CODEWITHSUNDEEP
mysqltransactionsinnodb
Jacobian
Jacobian

Reputation: 10872

How to prevent this deadlock with an INSERT and UPDATE statements inside a transaction

I have two tables table_a and table_b. In my application, I have a business method which does the following:

// it is one transaction

INSERT INTO table_b (join_col) VALUES (some_value);

UPDATE table_a a 
JOIN table_b b ON a.join_col = b.join_col AND a.column_a = b.column_b
SET a.column_c = a.column_c + 1;

I'm using InnoDB engine and the problem is, when I run my method in parallel or almost in parallel, I often get this error message:

Deadlock found when trying to get lock; try restarting transaction

It seems, like the first transaction started to update table_a and put a lock on table_b, while the second parallel transaction can not insert into table_b, because it is locked.

If I comment my UPDATE statement, it starts working. Both transactions insert into table_b without problems. So, how can I fix it? If it matters, I'm using MySQL 5.7.

Upvotes: 2

Views: 876

Answers (2)

Rick James
Rick James

Reputation: 142540

That seems like strange code.

  1. Insert a bunch of values into B.
  2. Use all those values to update another table.

Well, If you are playing with the same B in another thread, what do you expect? You don't want the list of things in B to be changing.

Consider using a TEMPORARY TABLE for B??

Upvotes: 0

O. Jones
O. Jones

Reputation: 108839

It looks like you're locking table b, then table a. Probably the a lock is an index lock.

Try this, to always grab an appropriate lock on table a first.

BEGIN TRANSACTION;
  SELECT COUNT(join_col) INTO @counter 
    FROM table_a a
   WHERE a.join_col = some_value
     FOR UPDATE;
  INSERT INTO table_b (join_col) VALUES (some_value);
  UPDATE table_a a 
    JOIN table_b b ON a.join_col = b.join_col 
                  AND a.column_a = b.column_b
     SET a.column_c = a.column_c + 1;
COMMIT;

The INTO @counter stuff prevents the first SELECT from returning a result set to your program.

If that doesn't work, try using LOCK TABLES. It's a big hammer, but effective.

LOCK TABLES table_a, table_b WRITE;
 your queries
UNLOCK TABLES;

Upvotes: 1

Related Questions