Do Sql write update reports at the same time if they are requested at the same time?

Question

If two independent scripts call the database with update requests in the same field, but with different values, will they be executed simultaneously, and one will be executed the other?

As an example, to help ensure clarity, imagine that both of these requests are requested to be run simultaneously, each with 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 be my results and why? if other factors play a roll, expand them as much as possible, this will be a common idea.

Side note: Because I know that people will ask anyway, my situation uses MySql with the Google App Engine, but I do not want to limit this question only to me if it will be useful to others. I use Status as an identifier for what the script does stuff in the field. if the status is not 0, no other script can touch it.

Answer 1

This is a delay for. In all major SQL implementations, DML statements are blocked by default, so one query will not overwrite another until the first is complete.

There are different levels of blocking. If you have row locking, your second update will work in parallel with the first, so at some point you will have 1 s and 2 in your table.

A table lock will cause the second query to wait for the completion of the first query to release the table lock.

Usually you can disable the lock directly in your SQL, but this is only ever done if you need to improve performance and you know that you will not encounter race conditions, as in your example.

Editing based on the new MySQL tag

If you are updating a table that uses the InnoDB , then you are working with row locking, and your query could give a table with 1 and 2.

If you are working with a table using the MyISAM mechanism, then you are working with table locking, and your update instructions will have a table in which there will be either all 1 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 individual UPDATE statements are atomic; no other session can interfere with the execution of another executable SQL query. However, there are several cases where table tables can provide an advantage:

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

The UPDATE statement always gets an exclusive (X) lock in the table being modified and holds that lock until the transaction completes. With an exclusive lock, no other transactions can modify the data.

If you had two separate connections executing two published update statements, depending on which one was started first, this will be the one that was completed. The other operator will not update the data, since there will no longer be records with status 0

Answer 3

The short answer is: it depends on which statement is made in the first place. Just because one process launched an update statement before another does not mean that it will end before another. At first it may not be planned, it may be blocked by another process, etc.

Ultimately, this is a condition of the race: the operation that completes (and fixes) the latter wins.

Answer 4

Since you have two scripts that do the same thing and use different values ​​for UPDATE, they will not run at the same time, one of the scripts will be run before even if you think you are invoking them at the same time. You need to indicate WHEN each script should be run, otherwise the program will not know what should be 1 and what should be 2.