Search code examples
sqldatabaserdbms

Several same time requested queries execution sequence

For example one user executes query like this:

UPDATE table SET column = 100;

And second user:

UPDATE table SET column = 200;

And lets say, these two queries are requested exactly same time, same seconds, same nanoseconds (or minimal time measurement unit, which is for this DB), then which query will be executed first and which one second?

Will database in this case choose queries sequence just randomly?

p.s. I don't tag some concrete database, I think this mechanism for all major RDBMS are similar. Or may be not?

Solution

  • RDBMS's implement a set of properties abbreviated (and called) ACID. Wikipedia explains the concept.

    Basically, ACID-compliant databases lock the data at some level (table, page, and row locks are typical). In principle, only one write lock can be acquired for the same object at the same time. So, the database will arbitrarily lock the row for one of the transactions.

    What happens to the other transaction? That depends, but one of two things should happen:

    • The transaction waits until the lock is available. So "in the end", it will assign the value (lose the lock, win the war ;).
    • The transaction will "timeout" because the appropriate row(s) are not available.

    Your case is rather more complicated, because all rows in a table are affected. In the end, though, all rows should have the same value in an ACID-compliant database.

    I should note that major databases are (usually) ACID-compliant. However, even though they have locks and transactions and similar mechanisms, the details can and do vary among databases.