MySQL concurrent UPDATES

Say we have the following situation in an application using MySQL where a User can buy an Item and each Item has a single buyer but the Item’s price may change.

Pseudocode :

BEGIN TRANSACTION

seenPrice = SELECT price FROM Item
    WHERE id = ABC AND buyer IS NULL

UPDATE Item SET buyer = X
    WHERE id = ABC AND buyer IS NULL AND price = seenPrice

COMMIT

The seenPrice variable and the buyer IS NULL AND price = seenPrice checks in the UPDATE statement serve as an Optimistic Lock mean to make sure that any concurrency issues do not come up.

In a multi – threaded enviroment where a thread A and a thread B are past the SELECT statement at the same time and say thread A executes the UPDATE statement first then concurrency is not a concern but is it possible that both A and B execute the UPDATE statement at the same exact time? For context the application is being developed using Spring Boot and Spring Data JPA.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

is it possible that both A and B execute the UPDATE statement at the same exact time?

The statements can potentially start executing at the same time, but they cannot update the same row simultaneously. An update statement must acquire an exclusive lock on the row before it can be modified, and lock acquisition is an atomic operation that is strictly serialized, meaning that one of the concurrent sessions will always acquire the lock first, and the other sessions will be blocked.

Lock acquisition is always atomic, regardless of the DBMS or storage engine — if it weren’t, it would be completely useless as a concurrency control mechanism.

The locking mechanism does not care if your application is written using optimistic or pessimistic locking approach — what changes is the time when the lock is taken. With pessimistic locking you expect there to be a lot of concurrent activity and you want to ensure that your transaction succeeds (at the cost of preventing concurrent updates):

With optimistic locking you don’t expect much concurrent activity, so the probability of your application failing to complete the update is low, and you don’t keep the lock for long:

Method 2

No, it is not possible for both A and B to execute the UPDATE at the exact same time. One or the other will acquire the lock on the row first, and then the other will be blocked until the winner commits.

Once the winner commits, the condition buyer IS NULL will no longer be true, so the UPDATE of the thread that waited will not affect it.

We can do an experiment to test this:

In window 1:

mysql> begin;

mysql> select * from item where id=1 and buyer is null;
+-------+----+-------+
| price | id | buyer |
+-------+----+-------+
| 19.95 |  1 |  NULL |
+-------+----+-------+

In window 2, do the same, start a transaction and view the price.

In window 1:

mysql> update item set buyer=42 where id=1 and buyer is null and price=19.95;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

In window 2:

mysql> update item set buyer=84 where id=1 and buyer is null and price=19.95;
(hangs, waiting)

In window 1:

mysql> commit;

In window 2:

(finishes waiting)
Query OK, 0 rows affected (4.53 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Notice zero rows matched or changed! Because the buyer was no longer null in the most recent committed version of the row.

Method 3

Since you are testing buyer IS NULL in both statements, the processing will “work”. However, a different connection could sneak in between the SELECT and UPDATE and set buyer.

So, you must check to see whether the UPDATE actually succeeded. If it did not, then what? Tell the user “Sorry you went through all the UI only to have someone else sneak in and buy the thing you wanted.” Ugh.

Instead, do FOR UPDATE on the SELECT. That way, you can catch the issue sooner.

And do not write code that hangs on to a transaction for more than a few seconds. That could lead to all sorts of nasty melt-downs in your application.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0