Sample Header Ad - 728x90

Does ACID guarantee locking (semaphore) until the transaction is commited?

0 votes
2 answers
139 views
I have following interaction (pseudocode):
start transaction
  select delivery for update                     # semaphore
  select delivery_items where processed = false  # get unprocessed data and process it
  update delivery_items SET processed = true
commit                                           # release semaphore
I was expecting that this will be enough to lock the delivery so that no two people will be able to perform actions on its items at the same time, but for some reason I am getting race conditions where two people do manage to modify delivery_items (I am getting the same items processed twice). Am I not understanding ACID correctly? Should the first select not block until the other user finishes transaction with a commit preventing race condition between second select and update? All MySQL settings are set to default (except some memory pool sizes). I am using Doctrine if that makes any difference. I am also using nested transactions (doctrine implements them using SAVEPOINTs).
Asked by HubertNNN (103 rep)
Apr 11, 2023, 01:42 PM
Last activity: Apr 12, 2023, 04:26 AM