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

SELECT FOR UPDATE obtains an intent exclusive lock on the table prior to obtaining the exclusive lock on the record.

SELECT FOR UPDATE obtains an intent exclusive lock on the table prior to obtaining the exclusive lock on the record. Therefore, in this scenario: 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, since both transactions are holding an IX lock on the table and waiting for an X lock on the records. Actually, this very scenario is described in the MySQL manual on locking.To work around this, you need to get rid of all indexes except the one you are searching on, that is lock_name.

Just drop the primary key on id.

So if I use UPDATE instead of INSERT, primary key won't hurt. Also the problem is not on one table. There are other tables that I can't remove the primary key.

Are there any other workarounds? There should be a way to savely insert a row when you make X lock on a row – NickSoft Mar 28 at 8:39 I guess the hard way is to have table locks with only one unique index and use it to lock before X lock and insert in more complex tables. But that has to be done before every insert that can deadlock and I hope there is other way.So is there another way to insert safely on tables with more than one (unique?

) index. – NickSoft Mar 28 at 8:44.

I typically prefer not to use transactions if I can avoid it; your problem could be solved by creating a single database query along the lines of insert into locks select ('lockname', $pid) from locks where name not in (select name from locks) By accessing the rows affected, you can see if the process is already running...

There is no way to check if the pid selected from the table is still running before do the update. What if 'lockname' is already in table, but it has crashed (not running)? The new process will never take the lock.

I need to select&lcok->check if it's running->if not running update with new pid – NickSoft Mar 28 at 8:30.

Just figured it out thanks to 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 }.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.