MySQL InnoDB lock on SELECT with exclusive lock (FOR UPDATE)

Question

I do this to ensure that only one instance of this process is running (pseudocode php / mysql innodb):

START TRANSACTION $rpid = SELECT `value` FROM locks WHERE name = "lock_name" FOR UPDATE $pid = posix_getpid(); if($rpid > 0){ $isRunning = posix_kill($rpid, 0); if(!$isRunning){ // isRunning INSERT INTO locks values('lock_name', $pid) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`) }else{ ROLLBACK echo "Allready running...\n"; exit(); } }else{ // if rpid == 0 - INSERT INTO locks values('lock_name', $pid) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`) } COMMIT ............... //free the pid INSERT INTO locks values('lock_name', 0) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`) 

Table locks contain the following fields:

 id - primary, autoinc name - varchar(64) unique key description - text value - text 

I believe that the time from START TRANSACTIN to COMMIT / ROLLBACK is really a millisecond - there is not enough time even for the wait time. How can I get a dead end with this code? I do not use other tables in this transaction. It seems like a dead end is impossible. If 2 processes are started at the same time, the first that gets the lock on this line will continue, and the other will wait for the lock that needs to be released. If the lock is not released within 1 minute, the error is a timeout and not a deadlock.

Answer 1

Just made it up because of Quassnoi's answer ...

I can do:

 $myPid = posix_getpid(); $gotIt = false; while(true){ START TRANSACTION; $pid = SELECT ... FOR UPDATE; // read pid and get lock on it if(mysql_num_rows($result) == 0){ ROLLBACK;// release lock to avoid deadlock INSERT IGNORE INTO locks VALUES('lockname', $myPid); }else{ //pid existed, no insert is needed break; } } if($pid != $myPid){ //we did not insert that if($pid>0 && isRunning($pid)){ ROLLBACK; echo 'another process is running'; exit; }{ // no other process is running - write $myPid in db UPDATE locks SET value = $myPid WHERE name = 'lockname'; // update is safe COMMIT; } }else{ ROLLBACK; // release lock } 

Answer 2

SELECT FOR UPDATE receives an intentional exclusive lock on the table until an exclusive write lock is received.

Therefore, in this case:

 X1: SELECT FOR UPDATE -- holds IX, holds X on 'lock_name' X2: SELECT FOR UPDATE -- holds IX, waits for X on 'lock_name' X1: INSERT -- holds IX, waits for X for the gap on `id` 

a deadlock occurs because both transactions hold IX lock in the table and wait for X lock in the records.

Actually, this script is described in the MySQL locking guide .

To get around this, you need to get rid of all the indexes except the ones you are looking for, i.e. lock_name .

Just drop the primary key on id .

Answer 3

Without seeing the actual PHP code, it's hard to be sure - but is it possible that you are not actually using the same database connection between running SELECT and INSERT?

I usually prefer not to use transactions if I can avoid this; your problem can be solved by creating a single database query row by row

 insert into locks select ('lockname', $pid) from locks where name not in (select name from locks) 

Having access to the affected lines, you will see that the process is already running ...