mysql deadlocks with concurrent inserts

mysql deadlocks with concurrent inserts

It is possible to cause deadlocks in mysql (Innodb) on concurrent insert statements, without there being any transactions in progress. Deadlocks are possible even when the inserts don't collide on any key.

The deadlocks occur due to gap locking done by mysql. There are several reasons for gap locking, and in this particular case, it has to do with preserving a unique key constraint on an index. The situation presents itself to us this way: There is a unique key constraint on a column and we are doing an insert. Mysql has to make sure that the lock it takes is sufficient to prevent another concurrent insert from adding a record with the same key, thus breaking the unique key constraint.

Mysql innodb engine performs row locking on inserts. If column A has a unique key constraint, and we are adding the value "bbb" for column A in an insert statement, mysql needs to lock any gap in the index between the two current records where "bbb" will be inserted at.

To illustrate the deadlock, let us start with a table schema:

TABLE vegetable (
   id bigint(10) NOT NULL auto_increment,
   name varchar(255) NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY uk_name (name)
) ENGINE=InnoDB

Let us assume the existence of these records in the table, and look at them in 'name' index order:

id name
10 ggg
05 jjj

Now, imagine two concurrent connections executing the following inserts in the following order:

Connection 1:

insert ignore into vegetable values(null, "ppp");

For this insert to proceed, connection 1 will lock the gap between "jjj" and "ppp" in the name index.

Connection 2:

insert ignore into vegetable values (null,"iii");

This will require locking the gap after "ggg", upto "iii". Since the lock from connection 1 does not span this, it will succeed.

insert ignore into vegetable values (null, "mmm");

This needs to lock the gap after "jjj" upto "mmm". Since connection 1 has a lock between "jjj" and "ppp", effectively spanning the lock connection 2 is attempting to take, this will block.

Connection 1:

insert ignore into vegetable values (null, "hhh");

This requires the gap lock between "ggg" and "hhh". This will block as it spans the the lock ["ggg" to "iii"] held by connection 2.


Thus we have both connections blocked on each other. This is the deadlock.

Here is a diagram. Transactions to the left are done by Connection 2. Transactions to the right are done by Connection 1. The sequence of transactions is donated by numbers 1) through 4).

Connection 2                         Connection1

---------------------------  ggg
G                                           G
A                                           AP
P                                            <------------------- 4) hhh
Lock                                                                  blocks (deadlock)
2) iii -------------------->

---------------------------  jjj 
G                                           G
A
P                                            A
Lock
3) mmm --------------->            P  
blocks
                                              L
                                              o
                                              c
                                              k
                                             <--------------------- 1) ppp

You can avoid this if you can order the inserts on each connection on the same direction. The deadlock happens as connection 2 inserts in ascending order of the index, while connection 1 inserts on descending order.

If you can't do this for practical reasons, you could retry the operation. Unless there is a high level of concurrency with a high load on the db where each transaction takes a heavy hit, a simple retry should work.