Mysql – InnoDB deadlock with INSERT .. SELECT

deadlockinnodbMySQL

I am trying to understand why a deadlock occurs when two workers in parallel are running INSERT .. SELECT queries on a table which has both 1) auto incremented primary key, 2) a unique index on multiple columns first of which is a date column. Workers normally work with rows from different dates. The unique index column value ranges should not overlap (they were overlapping before, but I changed the index to have date column as the first one).

SHOW ENGINE INNODB STATUS\G shows that 1st transaction (active for 20secs) is waiting for unique_rows lock (mode S), while the 2nd transaction (active for 11secs) holds unique_rows lock (mode X) and is waiting for auto-inc table lock.

How far I understand this: when 1st transaction starts, it acquires auto-inc table lock (I use the default auto-inc lock mode = 1), then it acquires some gap locks on the unique_rows index. The 2nd transaction could be acquiring gap locks on the unique_rows index, but they should not conflict with the locks acquired by 1st transaction.

Having written the question down, I suspect my assumptions about gap locks are wrong.

Another thing is I always have a DELETE FROM WHERE query in the same transaction just before INSERT .. SELECT, but that DELETE query has never been mentioned in deadlock logs.

The whole process works like this: 1) delete a date range from the destination table, 2) insert a date range in the destination table by selecting data from some other table.

Best Answer

the deadlock message sometimes will not show the full picture. It could be some other query is holding the lock which is not any of those two showed in the log. As you said, you have delete query in the beginning, that properly would be the case.

Here is a way I normally use to troubleshoot the deadlocks when you can not get all the info from the log.

mysql> set global log_output = 'table';
mysql> set global general_log=on;

Then you reproduce the problem, when the deadlock happens, turn off the general log. Find those two queries in the mysql.general_log, then use thread_id to find the start and end of the transaction. You will have all the queries in the transaction, then you can go through the logic, to find out which is the query or logic caused the problem.

OBSERVATION #1

The locking of the PRIMARY KEY is much more involved that you think. It has nothing to do with the default values because the three columns define the uniqueness of the key.

OBSERVATION #2

Your PRIMARY KEY has three columns. This makes great demands on the gen_clust_index, the Clustered Index. The entire row is basically stored in the index. The locking has to serialize the creation of rowids.

OBSERVATION #3

With the large column (realm) as part of the PRIMARY KEY, this makes for a lot of BTREE operations. I say this because the max row size is 263 bytes. Assuming every row is 263 bytes, and the size of a data page is 16K, this makes for each BTREE node holding about 255 keys before splitting. I recommend storing the MD5 or SHA1 of the realm as follows:

ALTER TABLE domain_access ADD realmkey CHAR(40);
CREATE TABLE domain_access_new
(
    nid int(10) unsigned,
    gid int(10) unsigned,
    realmkey char(40),
    realm varchar(255),
    PRIMARY KEY (nid,gid,realmkey)
    KEY (realm)
) ENGINE=InnoDB;
INSERT INTO domain_access_new
SELECT nid,gid,SHA1(realm),realm FROM domain_access;
ALTER TABLE domain_access RENAME domain_access_old;
ALTER TABLE domain_access_new RENAME domain_access;
DROP TABLE domain_access_old;

With this, your PRIMARY KEY would be 48 bytes instead of 263. You should look further into make the PRIMARY KEY even smaller.

EPILOGUE

Locking of the PRIMARY KEY must occur for INSERTs, UPDATEs, and DELETEs. I remember a hefty deadlocking exercise I went through with RedBlueThing

Jun 14, 2011 : Reasons for occasionally slow queries?
Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?
Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

Mysql – Clarification on thesql deadlock situation

Your assumption is correct. Nevertheless, you need to pay attention to what is being locked.

Both transactions are locking

space id 10787
page no 139
n bits 176
index PERMISSION_ID of table IDENTITY.ROLE

The DB Connections 12287 and 12279 ran one INSERT each

Here is what I notice: In each INSERT, you are running SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='...' to fill in DOMAIN_ID. This would cause additional locks because a query's result is needed to fill DOMAIN_ID while other indexes are made to wait for the materialization of ROLE's primary key. This deadlock lasts a little longer that normal because of this intermittent need to run a SELECT.

SUGGESTION

Instead of doing

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone',
(SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'));

you should retrieve the DOMAIN_ID separately into a variable and then use it in the INSERT

SELECT DOMAIN_ID INTO @domain_id FROM DOMAIN
WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL';
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188,'everyone',@domain_id);

This might reduce the need to pile up locks