I have just upgraded to percona 5.5.27-28 and hit what appears to be this deadlock. I do have drop table going on right around this issue. The trace is sightly different though. InnoDB: Warning: a long semaphore wait: --Thread 139645203990272 has waited at row0ins.c line 1557 for 241.00 seconds the semaphore: S-lock on RW-latch at 0x1089700 '&dict_operation_lock' a writer (thread id 139645204256512) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0undo.c line 315 Last time write locked in file /home/jenkins/workspace/percona-server-5.5-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.5.27-rel28.0/Percona-Server-5.5.27-rel28.0/storage/innobase/row/row0mysql.c line 3270 InnoDB: Warning: a long semaphore wait: --Thread 139645203724032 has waited at row0undo.c line 315 for 241.00 seconds the semaphore: S-lock on RW-latch at 0x1089700 '&dict_operation_lock' a writer (thread id 139645204256512) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0undo.c line 315 Last time write locked in file /home/jenkins/workspace/percona-server-5.5-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.5.27-rel28.0/Percona-Server-5.5.27-rel28.0/storage/innobase/row/row0mysql.c line 3270 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 120826 2:58:09 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 66 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 39222 1_second, 39129 sleeps, 3907 10_second, 150 background, 150 flush srv_master_thread log flush and writes: 40048 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 29054, signal count 45202 --Thread 139645203990272 has waited at row0ins.c line 1557 for 254.00 seconds the semaphore: S-lock on RW-latch at 0x1089700 '&dict_operation_lock' a writer (thread id 139645204256512) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0undo.c line 315 Last time write locked in file /home/jenkins/workspace/percona-server-5.5-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.5.27-rel28.0/Percona-Server-5.5.27-rel28.0/storage/innobase/row/row0mysql.c line 3270 --Thread 139645203724032 has waited at row0undo.c line 315 for 254.00 seconds the semaphore: S-lock on RW-latch at 0x1089700 '&dict_operation_lock' a writer (thread id 139645204256512) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0undo.c line 315 Last time write locked in file /home/jenkins/workspace/percona-server-5.5-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.5.27-rel28.0/Percona-Server-5.5.27-rel28.0/storage/innobase/row/row0mysql.c line 3270 --Thread 139645204256512 has waited at buf0buf.c line 3667 for 253.00 seconds the semaphore: Mutex at 0x2dd3048 '&buf_pool->mutex', lock var 1 waiters flag 1 --Thread 139645191735040 has waited at buf0buf.c line 3667 for 253.00 seconds the semaphore: Mutex at 0x2dd3048 '&buf_pool->mutex', lock var 1 waiters flag 1 Mutex spin waits 37264, rounds 210101, OS waits 5168 RW-shared spins 33728, rounds 504219, OS waits 10811 RW-excl spins 7569, rounds 438254, OS waits 12461 Spin rounds per wait: 5.64 mutex, 14.95 RW-shared, 57.90 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 120826 2:33:59 Transaction: TRANSACTION 10D4151, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id 1438, OS thread handle 0x7f01aec74700, query id 6441224 173-9-34-221-NewEngland.hfc.comcastbusiness.net 173.9.34.221 edm executing /* /opt/prod/bin/edm_nightly_cloud.py */ /* edm_nightly */ insert ignore into edm.user_visit (global_user_id, arrival_id, lead_id, publisher, received_at, make, model, trim, answer1, answer2, timeframe, tid, traffic_type) select 'smartautooffers.com:115913691', 115913691, 1082428071, 'smartautooffers.com', '2012-08-24 16:24:57', 'GMC', 'Canyon 2WD', 'Extended Cab SLE', 'Ready to buy', 'New Cars', '1 Week', 81, 'Tier 2 Search' Foreign key constraint fails for table `edm`.`user_visit`: , CONSTRAINT `user_visit_ibfk_1` FOREIGN KEY (`global_user_id`) REFERENCES `user` (`global_user_id`) Trying to add in child table, in index `unique_user` tuple: DATA TUPLE: 3 fields; 0: len 29; hex 736d6172746175746f6f66666572732e636f6d3a313135393133363931; asc smartautooffers.com:115913691;; 1: len 4; hex c0848aa7; asc ;; 2: len 4; hex 84cf4190; asc A ;; But in parent table `edm`.`user`, in index `unique_user`, the closest match we can find is record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 29; hex 736d6172746175746f6f66666572732e636f6d3a313135393133373033; asc smartautooffers.com:115913703;; 1: len 4; hex 8550f210; asc P ;; -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1491852 OS file reads, 747223 OS file writes, 88234 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 4806, free list len 78819, seg size 83626, 94662 merges merged operations: insert 247136, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 4425293, node heap has 3721 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 252112650982 Log flushed up to 252112650982 Last checkpoint at 252106684804 Max checkpoint age 7782360 Checkpoint age target 7539162 Modified age 5927893 Checkpoint age 5966178 0 pending log writes, 0 pending chkp writes 31111 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 2206203904; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 96371424 (35402344 + 60969080) Page hash 2213656 (buffer pool 0 only) Dictionary cache 9265295 (8851984 + 413311) File system 106432 (82672 + 23760) Lock system 5318032 (5313416 + 4616) Recovery system 0 (0 + 0) Dictionary memory allocated 413311 InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 139645203990272 has waited at row0ins.c line 1557 for 272.00 seconds the semaphore: S-lock on RW-latch at 0x1089700 '&dict_operation_lock' a writer (thread id 139645204256512) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0undo.c line 315 Last time write locked in file /home/jenkins/workspace/percona-server-5.5-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.5.27-rel28.0/Percona-Server-5.5.27-rel28.0/storage/innobase/row/row0mysql.c line 3270 InnoDB: Warning: a long semaphore wait: --Thread 139645203724032 has waited at row0undo.c line 315 for 272.00 seconds the semaphore: S-lock on RW-latch at 0x1089700 '&dict_operation_lock' a writer (thread id 139645204256512) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0undo.c line 315 Last time write locked in file /home/jenkins/workspace/percona-server-5.5-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.5.27-rel28.0/Percona-Server-5.5.27-rel28.0/storage/innobase/row/row0mysql.c line 3270 InnoDB: Warning: a long semaphore wait: --Thread 139645204256512 has waited at buf0buf.c line 3667 for 271.00 seconds the semaphore: Mutex at 0x2dd3048 '&buf_pool->mutex', lock var 1 waiters flag 1 InnoDB: Warning: a long semaphore wait: --Thread 139645191735040 has waited at buf0buf.c line 3667 for 271.00 seconds the semaphore: Mutex at 0x2dd3048 '&buf_pool->mutex', lock var 1 waiters flag 1 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 139645203990272 has waited at row0ins.c line 1557 for 303.00 seconds the semaphore: S-lock on RW-latch at 0x1089700 '&dict_operation_lock' a writer (thread id 139645204256512) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0undo.c line 315 Last time write locked in file /home/jenkins/workspace/percona-server-5.5-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.5.27-rel28.0/Percona-Server-5.5.27-rel28.0/storage/innobase/row/row0mysql.c line 3270 InnoDB: Warning: a long semaphore wait: --Thread 139645203724032 has waited at row0undo.c line 315 for 303.00 seconds the semaphore: S-lock on RW-latch at 0x1089700 '&dict_operation_lock' a writer (thread id 139645204256512) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0undo.c line 315 Last time write locked in file /home/jenkins/workspace/percona-server-5.5-rpms/label_exp/centos6-64/target/BUILD/Percona-Server-5.5.27-rel28.0/Percona-Server-5.5.27-rel28.0/storage/innobase/row/row0mysql.c line 3270 InnoDB: Warning: a long semaphore wait: --Thread 139645204256512 has waited at buf0buf.c line 3667 for 302.00 seconds the semaphore: Mutex at 0x2dd3048 '&buf_pool->mutex', lock var 1 waiters flag 1 InnoDB: Warning: a long semaphore wait: --Thread 139645191735040 has waited at buf0buf.c line 3667 for 302.00 seconds the semaphore: Mutex at 0x2dd3048 '&buf_pool->mutex', lock var 1 waiters flag 1 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 InnoDB: ###### Diagnostic info printed to the standard error stream