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/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
