Do Sql Update Statements run at the same time if requested at the same time?

Question

If two independent scripts call a database with update requests to the same field, but with different values, would they execute at the same time and one overwrite the other?

as an example to help ensure clarity, imagine both of these statements being requested to run at the same time, each by a different script, where Status = 2 is called microseconds after Status = 1 by coincidence.

Update My_Table SET Status = 1 WHERE Status= 0;
Update My_Table SET Status = 2 WHERE Status= 0;

What would my results be and why? if other factors play a roll, expand on them as much as you please, this is meant to be a general idea.

Side Note: Because i know people will still ask, my situation is using MySql with Google App Engine, but i don't want to limit this question to just me should it be useful to others. I am using Status as an identifier for what script is doing stuff to the field. if status is not 0, no other script is allowed to touch it.

Answer 1

This is what locking is for. All major SQL implementations lock DML statements by default so that one query won't overwrite another before the first is complete.

There are different levels of locking. If you've got row locking then your second update will run in parallel with the first, so at some point you'll have 1s and 2s in your table.

Table locking would force the second query to wait for the first query to completely finish to release it's table lock.

You can usually turn off locking right in your SQL, but it's only ever done if you need a performance boost and you know you won't encounter race conditions like in your example.

Edits based on the new MySQL tag

If you're updating a table that used the InnoDB engine, then you're working with row locking, and your query could yield a table with both 1s and 2s.

If you're working with a table that uses the MyISAM engine, then you're working with table locking, and your update statements would end up with a table that would either have all 1s or all 2s.

Answer 2

from https://dev.mysql.com/doc/refman/5.0/en/lock-tables-restrictions.html (MySql)

Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other session can interfere with any other currently executing SQL statement. However, there are a few cases when locking tables may provide an advantage:

from https://msdn.microsoft.com/en-us/library/ms177523.aspx (sql server)

An UPDATE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive lock, no other transactions can modify data.

If you were having two separate connections executing the two posted update statements, whichever statement was started first, would be the one that completed. THe other statement would not update the data as there would no longer be records with a status of 0