The --single-transaction option of mysqldump does not do FLUSH TABLES WITH READ LOCK;. It causes mysqldump to setup a repeatable read transaction for all tables being dumped.

From your question, you stated that the mysqldump's SELECT for the db_external_notification table is holding up hundreds of INSERT command to that same table. Why is this happening ?

The most likely thing a lock on the gen_clust_index (better known as the Clustered Index). This paradigm causes data and index pages for a table to coexist. Those index pages are based on either the PRIMARY KEY or and auto-generated RowID index (in the event there is no PRIMARY KEY).

You should be able to spot this by running SHOW ENGINE INNODB STATUS\G and look for any page from the gen_clust_index that has an exclusive lock. Doing INSERTs into a table with a Clustered Index requires an exclusive lock for handling the PRIMARY KEY's BTREE, as well the serialization of the auto_increment.

I have discussed this phenomenon before

• Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
• Dec 22, 2011 : MySQL deadlock - cannot restart normally?
• Dec 13, 2012 : MySQL InnoDB locks primary key on delete even in READ COMMITTED

UPDATE 2014-07-21 15:03 EDT

Please look at lines 614-617 of your PastBin

mysql tables in use 1, locked 0
MySQL thread id 6155315, OS thread handle 0x85f11b70, query id 367774810 localhost root Sending data
SELECT /*!40001 SQL_NO_CACHE */ * FROM `db_external_notification`
Trx read view will not see trx with id >= 1252538405, sees < 1252538391

Note that line 617 says

Trx read view will not see trx with id >= 1252538405, sees < 1252538391

What does this tell me? You have some PRIMARY KEY with an auto_increment on id.

Your max id for the table db_external_notification was less than 1252538391 when the mysqldump was launched. When you subtract 1252538391 from 1252538405, this means that 14 or more INSERT commands have been attempted. Internally, this would need to move the auto_increment of this table at least 14 times. Yet, nothing can be committed or even pushed into the Log Buffer because of managing this id gap.

Now, look at the processlist from your PasteBin. Unless I miscounted, I saw 38 DB Connections doing an INSERT (19 Before the mysqldump process (process id 6155315), 19 After). I am sure 14 or more of those connections are frozen because of managing the auto_increment gap.

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