MySQL return Deadlock with insert row and FK is locked 'for update'
- by constantin-slednev
Hello developers!
I get deadlock error in my mysql transaction.
The simple example of my situation:
Thread1 > set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
Thread1 > SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
Thread1 > SELECT * FROM A WHERE ID=1000 FOR UPDATE;
1 row in set (0.00 sec)
Thread2 > set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
Thread2 > SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
Thread2 > INSERT INTO B (AID, NAME) VALUES (1000, 'Hello world');
SLEEP
Query OK, 1 row affected (4.99 sec)
Thread1 > INSERT INTO B (AID, NAME) VALUES (1000, 'Hello world2');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
B.AID -> FK -> A.ID
I see three solutions:
catch deadlock error in code and retry query.
use innodb_locks_unsafe_for_binlog in my.cnf
lock (for update) table A in Thread2 before insert
Can you give me more solutions ? Current solutions do not fit me.
