I have a "Delay pending pending pending pending" error from MySQL that I cannot reproduce or diagnose. I'm sure this is a dead end (unlike a transaction that grabs the lock and then bends her thumbs), because my logs show that another process started at the same time, also hung and then continued when the first half -out. But, as a rule, InnoDB detects deadlocks without a timeout. Therefore, I am trying to understand why this deadlock was not detected.
Both transactions use a serializable isolation level. (I have a fair understanding of InnoDB locking at this isolation level.) The transaction uses a single non-InnoDB table (MyISAM), which I insert and update. However, I don’t understand how this can be involved in a dead end, because I believe that MyISAM just locks the table during insertions and updates (then immediately releases it, since MyISAM is not transactional), so no other lock is taken until this is a table lock.
So, I am convinced that the deadlock only includes InnoDB tables, which brings me back to the question of why it was not detected. The MySQL documentation (http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html) implies that locking detection almost always works. Problems that I discovered while searching include things like the explicit “lock table”, “modify table”, and “insert delay”. I am not doing any of this, just inserting, updating and selecting (some of my settings are “for updating”).
I tried to reproduce by creating one MyISAM table and a couple of InnoDB tables and following different insert and update sequences in MyISAM and "select to update" in InnoDB. But every time I created a dead end, InnoDB reported it immediately. I could not play the timeout.
Any other tips for diagnosing this? I am using mysql 5.1.49.
source
share