MySQL return Deadlock with insert row and FK is locked 'for update'

Question

Hello developers!

I get deadlock error in my mysql transaction.

The simple example of my situation:

Thread1 > BEGIN;
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 > BEGIN;
Query OK, 0 rows affected (0.00 sec)

Thread2 > INSERT INTO B (AID, NAME) VALUES (1000, 'Hello world');
[Hangs]

Thread1 > INSERT INTO B (AID, NAME) VALUES (1000, 'Hello world2');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Thread2 >
Query OK, 1 row affected (10.00 sec)

B.AID is a FOREIGN KEY referring to A.ID

I see three solutions:

1. catch deadlock error in code and retry query.
2. use innodb_locks_unsafe_for_binlog in my.cnf
3. lock (for update) table A in Thread2 before insert

Is there any other solutions ?

Answer 1

I don't know what code surounds this examples, but it might be worth using LOCK IN SHARE MODE for both Threads, since you're not actually updating the row itself. If you must use LOCK FOR UPDATE, I would think that locking the other thread would be the only logical path.

Also if you open to moving away from MySQL, I've found that PostgreSQL has much better resolution of deadlocks. In some cases, I was finding MySQL deadlocked every time when running the same script on >1 thread. Where the same script in PostgreSQL could handle it just fine for any number of parallel threads.

Answer 2

Based on a function from the mysql high performance blog.

I was able to implement the following deadlock handling code in PHP:

/* maximum number of attempts for deadlock */
$MAX_ATTEMPS = 20;

 /* query */
 $sql = "INSERT INTO B (AID, NAME) VALUES (1000, 'Hello world')"; 

 /* current attempt counter */
 $current = 0;

 /* try to query */
 while ($current++ <$MAX_ATTEMPS) 
 {
     $result = mysql_query($sql);
     if(!$result && ( mysql_errno== '1205' || mysql_errno == '1213'  ) )
         continue;
     else
         break;
 }  
 }

Hopefully this might give you some good ideas.