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.



  • 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 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):

    Application Database
    SELECT ... FOR UPDATE Acquire lock
    Do something else
    ...
    UPDATE ...
    COMMIT 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
    SELECT ...
    Do something else
    ...
    UPDATE ... Acquire lock
    COMMIT Release lock



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2