maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Jesse!

On Mar 17, Jesse Schalken wrote:
> 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.

The statement SELECT ... FOR UPDATE works for you exactly as it says. It
*selects* and locks it *for update*. Because the row doesn't exist,
SELECT actually selects nothing. So it locks nothing for update.

When the row exists, the first transaction returns a row and the second
waits on SELECT.

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

Your goal is to "check if a row exists with a particular value
and if not, generate the data for that row and insert it". Normally
uniqueness checks are done inside a database. Make your KEY (col) a
UNIQUE key, then when you insert the database will check whether a row
exists and will insert the value if not.

If you insist on doing it in the application, you can try user-level
locks, GET_LOCK/RELEASE_LOCK functions. Like

  SELECT GET_LOCK(4, 10);
  -- If the result is 1
  SELECT * FROM test WHERE col = 4;
  -- If no results, generate data and insert
  INSERT INTO test SET col = 4, data = 'generated data goes here';
  SELECT RELEASE_LOCK(4);

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx