Marcus Bointon wrote: > Hi, > > (repeat posting) > > 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/ > [EMAIL PROTECTED] | http://www.synchromedia.co.uk/
With what you've provided us: You can not prevent this. You are running in a transaction which is isolated from any others. One way to prevent this may be to write a pid file in your script, then check for its existence. If it does exist then just die out (script was already running.) I have scripts that perform tasks on regular intervals. Some times the script runs long and may overlap into the next run time. I check for the file's existence, if it does exist just print a message and exit. If it does not exist, touch the file to create it and move on... deleting it later of course. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
