The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.

I wrote about pros and cons of using MyISAM vs InnoDB

• InnoDB vs MyISAM with many indexes (Jul 05, 2012)
• Which is faster, InnoDB or MyISAM? (May 03, 2012)
• Any gotchas at all with converting from MyISAM to InnoDB? (Jan 09, 2012)
• Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks? (Oct 07, 2011)
• Benefits of table level locking (Sep 14, 2011)
• What are the main differences between InnoDB and MyISAM? (Apr 14, 2011)

With regard to your current question, here is a possible scenario:

• article and article_comments are both MyISAM tables
• article_comments has one or more indexes with status as a column
• Index page updates for article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
• You have SELECT queries that perform JOINs between article and article_comments

In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)

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.