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 SAVEPOINT
s).
Asked by HubertNNN
(103 rep)
Apr 11, 2023, 01:42 PM
Last activity: Apr 12, 2023, 04:26 AM
Last activity: Apr 12, 2023, 04:26 AM