List:General Discussion« Previous MessageNext Message »
From:Marcus Bointon Date:March 5 2007 12:24pm
Subject:Transaction/locking confusion
View as plain text  
Hi,

I have a simple PHP function that initialises a process definition.  
To prevent it happening more than once, I'm wrapping it in a  
transaction, however, it doesn't seem to be working and I get  
multiple initialisations. In pseudocode:

BEGIN;
UPDATE process SET status = 'ready' WHERE id = 123 AND status =  
'init' LIMIT 1;
...do other stuff including some INSERTs
if other stuff is OK:
COMMIT;
else
ROLLBACK;

If I have two simultaneous processes running this script, somehow  
they are both able to initialise. I guess that if the overlapping  
transactions are isolated, then both see the process in the 'init'  
status and that the 'other stuff' part takes long enough that it's  
reasonably likely to happen. I was under the impression that the  
UPDATE inside the transaction would lock the row and prevent the  
later query from succeeding, but it seems that's not how it works.

How can I prevent this situation? Do I need to lock the row  
explicitly? Why doesn't the transaction provide sufficient isolation?

Marcus
-- 
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
marcus@stripped | http://www.synchromedia.co.uk/


Thread
Transaction/locking confusionMarcus Bointon5 Mar
  • Dead lock detection?Raúl Andrés Duque12 Mar