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.
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
seenPricevariable and the
buyer IS NULL AND price = seenPricechecks 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
SELECTstatement at the same time and say thread A executes the
UPDATEstatement first then concurrency is not a concern but is it possible that both A and B execute the
UPDATEstatement at the same exact time? For context the application is being developed using Spring Boot and Spring Data JPA.
is it possible that both A and B execute the
UPDATEstatement 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 https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html 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):
SELECT ... FOR UPDATE
Acquire lock Do something else ...
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:
Do something else ...