← Back to team overview

maria-discuss team mailing list archive

Exclusive lock not working

 

I have a transaction that checks if a row exists with a particular value
and if not, generates the data for that row and inserts it:

-- schema

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

-- 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 = 'generated data goes here';
COMMIT;


The problem I have is that if two copies of this transaction run
concurrently (with the queries interleaved), they both pass the SELECT and
deadlock on the INSERT. I would expect that the SELECT would acquire an
exclusive lock on "col = 4" so only one transaction will proceed to insert,
but the "FOR UPDATE" seems to behave as a shared lock instead.

What is the correct way to write this transaction so only one copy proceeds
to generate the "data" string and insert the row?

(I originally posted this on Stack Overflow here
<https://stackoverflow.com/questions/42547629/insert-row-if-not-exists-without-deadlock>
.)

Thanks

Follow ups