Insert row if it does not exist without deadlock

Question

I have a simple table

CREATE TABLE test ( col INT, data TEXT, KEY (col) ); 

and simple transaction

 START TRANSACTION; SELECT * FROM test WHERE col = 4 FOR UPDATE; -- If no results, generate data and insert INSERT INTO test SET col = 4, data = 'data'; COMMIT; 

I am trying to ensure that two copies of this transaction are executed simultaneously without duplicate rows and without deadlocks. I also do not want to take the cost of creating data for col = 4 more than once.

I tried:

• SELECT .. (without FOR UPDATE or LOCK IN SHARE MODE ):

  Both transactions see that there are no rows with col = 4 (without getting a lock), and both generate data and insert two copies of the row with col = 4 .

• SELECT .. LOCK IN SHARE MODE

  Both transactions receive a common lock on col = 4 , generate data and try to insert a row with col = 4 . Both transactions expect the other to release their shared lock so that it can INSERT , resulting in ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction .

• SELECT .. FOR UPDATE

  I would expect one SELECT transaction to succeed and acquire an exclusive lock on col = 4 , and another SELECT transaction to block waiting for the first.

  Instead, both SELECT .. FOR UPDATE are executed, and the transaction is stalled, as when using SELECT .. LOCK IN SHARE MODE . The exclusive lock on col = 4 just doesn't work.

How can I write this transaction without causing duplicate rows and without deadlock?

Answer 1

Adjust your circuit a bit:

 CREATE TABLE test ( col INT NOT NULL PRIMARY KEY, data TEXT ); 

If col is the primary key, it cannot be duplicated.

Then use ON DUPLICATE KEY :

 INSERT INTO test (col, data) VALUES (4, ...) ON DUPLICATE KEY UPDATE data=VALUES(data) 

Answer 2

Perhaps it...

 START TRANSACTION; INSERT IGNORE INTO test (col, data) VALUES (4, NULL); -- or '' -- if Rows_affected() == 0, generate data and replace `data` UPDATE test SET data = 'data' WHERE col = 4; COMMIT; 

Note: if PRIMARY KEY is AUTO_INCREMENT , this may "write" the identifier.

Answer 3

Please note that InnoDB has 2 types of exclusive locks: one for updating and deleting, and the other for inserting. Thus, in order to execute a SELECT FOR UPDATE transaction, InnoDB must first take a lock for updating in one transaction, then the second transaction will try to make the same lock and block waiting for the first transaction (it could not succeed, as you stated in the question), then when the first transaction tries to execute INSERT, it will have to change its lock from lock to update to lock to insert. The only way InnoDB can do this is to first lower the lock to general, and then update it to lock for insertion. And he cannot lower the lock when there is another transaction awaiting the acquisition of an exclusive lock. Therefore, in this situation, you get a deadlock error.

The only way to do this correctly is to have a unique index in col, try to insert a row with col = 4 (you can put dummy data if you do not want to generate it before INSERT), then in case of duplicate rollback of a key error, and if INSERT is successful You can UPDATE a row with the correct data. Please note that if you do not want to incur the cost of creating data unnecessarily, this probably means that the generation takes a lot of time, and all this time you will keep an open transaction that inserted a row with col = 4, which will hold all other processes trying to insert the same row. I'm not sure if this will be much better than generating data first and then pasting it.