Insert row if not exists without deadlock

Question

I have a simple table

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

and a 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 running concurrently result in no duplicate rows and no deadlocks. I also don't want to incur the cost of generating data for col = 4 more than once.

I have tried:

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

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

2. SELECT .. LOCK IN SHARE MODE

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

3. SELECT .. FOR UPDATE

   I would expect that one transaction's SELECT will succeed and acquire an exclusive lock on col = 4 and the other transaction's SELECT will block waiting for the first.

   Instead, both SELECT .. FOR UPDATE queries succeed and the transactions proceed to deadlock just like with SELECT .. LOCK IN SHARE MODE. The exclusive lock on col = 4 just doesn't seem to work.

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

Answer 1

Maybe this...

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;

Caution: If the PRIMARY KEY is an AUTO_INCREMENT, this may 'burn' an id.