java.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2851) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1534) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1625) at com.mysql.jdbc.Connection.execSQL(Connection.java:2291) at com.mysql.jdbc.Connection.execSQL(Connection.java:2226) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1262) at opennemo.DatabaseDeadlockTest.run(DatabaseDeadlockTest.java:73) at java.lang.Thread.run(Thread.java:595) [Worker 4]: ===================================== 051122 15:47:12 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 36 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 4, signal count 4 Mutex spin waits 10, rounds 160, OS waits 2 RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 0 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 051122 15:47:11 *** (1) TRANSACTION: TRANSACTION 0 1812, ACTIVE 0 sec, process no 9139, OS thread id 57359 inserting mysql tables in use 1, locked 1 LOCK WAIT 8 lock struct(s), heap size 1024, undo log entries 2 MySQL thread id 5, query id 154 localhost 127.0.0.1 root update INSERT INTO test1 (value, test_id_ref) VALUES (6, 3) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 55 n bits 160 index `test_id_ref` of table `deadlock/test1` trx id 0 1812 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 8; hex 8000000000000004; asc ;; 1: len 8; hex 8000000000000004; asc ;; *** (2) TRANSACTION: TRANSACTION 0 1813, ACTIVE 0 sec, process no 9141, OS thread id 61456 setting auto-inc lock mysql tables in use 1, locked 1 5 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 6, query id 155 localhost 127.0.0.1 root update INSERT INTO test1 (value, test_id_ref) VALUES (2, 4) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 55 n bits 160 index `test_id_ref` of table `deadlock/test1` trx id 0 1813 lock_mode X Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 8; hex 8000000000000004; asc ;; 1: len 8; hex 8000000000000004; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `deadlock/test1` trx id 0 1813 lock mode AUTO-INC waiting *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 0 1826 Purge done for trx's n:o < 0 1328 undo n:o < 0 0 History list length 14 Total number of lock structs in row lock hash table 7 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 1813, not started, process no 9141, OS thread id 61456 MySQL thread id 6, query id 313 localhost 127.0.0.1 root SHOW ENGINE INNODB STATUS ---TRANSACTION 0 1809, not started, process no 9137, OS thread id 53262 MySQL thread id 4, query id 90 localhost 127.0.0.1 root ---TRANSACTION 0 1805, not started, process no 9101, OS thread id 45068 MySQL thread id 2, query id 20 localhost root ---TRANSACTION 0 1825, ACTIVE 0 sec, process no 9135, OS thread id 49165 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 3, query id 298 localhost 127.0.0.1 root updating DELETE FROM test1 WHERE test_id_ref = 1 Trx read view will not see trx with id >= 0 1826, sees < 0 1821 ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 54 n bits 288 index `PRIMARY` of table `deadlock/test1` trx id 0 1825 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000001; asc ;; 1: len 6; hex 00000000070e; asc ;; 2: len 7; hex 00000000c115b7; asc ;; 3: len 8; hex 8000000000000001; asc ;; 4: len 8; hex 8000000000000001; asc ;; ------------------ ---TRANSACTION 0 1823, ACTIVE 0 sec, process no 9143, OS thread id 65553 6 lock struct(s), heap size 1024, undo log entries 49 MySQL thread id 7, query id 312 localhost 127.0.0.1 root Trx read view will not see trx with id >= 0 1824, sees < 0 1817 ---TRANSACTION 0 1821, ACTIVE 1 sec, process no 9139, OS thread id 57359 fetching rows mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 29 MySQL thread id 5, query id 230 localhost 127.0.0.1 root updating DELETE FROM test1 WHERE test_id_ref = 3 Trx read view will not see trx with id >= 0 1822, sees < 0 1817 ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 54 n bits 280 index `PRIMARY` of table `deadlock/test1` trx id 0 1821 lock_mode X waiting Record lock, heap no 152 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000097; asc ;; 1: len 6; hex 00000000071f; asc ;; 2: len 7; hex 00000000381028; asc 8 (;; 3: len 8; hex 8000000000000008; asc ;; 4: len 8; hex 8000000000000005; asc ;; ------------------ -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 28 OS file reads, 23 OS file writes, 23 OS fsyncs 0.78 reads/s, 91428 avg bytes/read, 0.64 writes/s, 0.64 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 0, seg size 2, is empty Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 5, node heap has 1 buffer(s) 3.14 hash searches/s, 28.25 non-hash searches/s --- LOG --- Log sequence number 0 163475 Log flushed up to 0 161738 Last checkpoint at 0 116989 0 pending log writes, 0 pending chkp writes 28 log i/o's done, 0.78 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 16900634; in additional pool allocated 916352 Buffer pool size 512 Free buffers 483 Database pages 28 Modified db pages 23 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 22, created 6, written 0 0.61 reads/s, 0.17 creates/s, 0.00 writes/s Buffer pool hit rate 995 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 4 read views open inside InnoDB Main thread process no. 9097, id 32777, state: sleeping Number of rows inserted 233, updated 0, deleted 178, read 292 6.47 inserts/s, 0.00 updates/s, 4.94 deletes/s, 8.11 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================