patternsqlMinor
MySQL concurrent UPDATES
Viewed 0 times
mysqlconcurrentupdates
Problem
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 :
The
In a multi - threaded enviroment where a thread A and a thread B are past the
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
COMMITThe
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.Solution
is it possible that both A and B execute the
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):
Application
Database
Acquire lock
Do something else
...
Release lock
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:
Application
Database
Do something else
...
Acquire lock
Release lock
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):
Application
Database
SELECT ... FOR UPDATEAcquire lock
Do something else
...
UPDATE ...COMMITRelease lock
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:
Application
Database
SELECT ...Do something else
...
UPDATE ...Acquire lock
COMMITRelease lock
Context
StackExchange Database Administrators Q#311242, answer score: 2
Revisions (0)
No revisions yet.