-- Session setting for each connection set session transaction isolation level read committed; set autocommit = 0; -- Setup the database tables CREATE TABLE `inow6`.`tmp_tbl_1` ( `TMP1_ID` varchar(23) NOT NULL, `ITEMS` int(11) DEFAULT NULL, PRIMARY KEY (`TMP1_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `inow6`.`tmp_tbl_2` ( `TMP2_ID` varchar(23) NOT NULL, `ITEMS` int(11) DEFAULT NULL, `TMP1_ID` varchar(23) DEFAULT NULL, PRIMARY KEY (`TMP2_ID`), CONSTRAINT `FK_TMP1_ID` FOREIGN KEY (`TMP1_ID`) REFERENCES `tmp_tbl_1` (`TMP1_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into tmp_tbl_1 (TMP1_ID, ITEMS) values ('2000000060_00003E3DY1BJ', 0); commit; -- Steps to produce the deadlock -- Transaction 1 START TRANSACTION; INSERT INTO tmp_tbl_2(TMP2_ID, ITEMS, TMP1_ID) VALUES('2000000007_0002BT4N2C17', 2, '2000000060_00003E3DY1BJ'); -- Transaction 2 START TRANSACTION; INSERT INTO tmp_tbl_2(TMP2_ID, ITEMS, TMP1_ID) VALUES('200000000F_0002BS4N2C17', 1, '2000000060_00003E3DY1BJ'); -- Transaction 1 - Blocks UPDATE tmp_tbl_1 SET ITEMS = 1 WHERE TMP1_ID = '2000000060_00003E3DY1BJ'; -- Transaction 2 - Triggers Deadlock UPDATE tmp_tbl_1 SET ITEMS = 1 WHERE TMP1_ID = '2000000060_00003E3DY1BJ'; ===================================== 091102 16:49:38 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 10 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 8, signal count 8 Mutex spin waits 0, rounds 20, OS waits 1 RW-shared spins 14, OS waits 7; RW-excl spins 0, OS waits 0 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 091102 16:49:29 *** (1) TRANSACTION: TRANSACTION 0 6995970, ACTIVE 16 sec, OS thread id 724 starting index read mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1024, 2 row lock(s), undo log entries 1 MySQL thread id 3, query id 52 pettyb.pvi.com 172.18.120.100 INUSER Updating UPDATE tmp_tbl_1 SET ITEMS = 1 WHERE TMP1_ID = '2000000060_00003E3DY1BJ' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 478408 n bits 72 index `PRIMARY` of table `inow6`.`tmp_tbl_1` trx id 0 6995970 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 23; hex 323030303030303036305f30303030334533445931424a; asc 2000000060_00003E3DY1BJ;; 1: len 6; hex 0000006abcea; asc j ;; 2: len 7; hex 0000074ccc221b; asc L " ;; 3: len 4; hex 80000001; asc ;; *** (2) TRANSACTION: TRANSACTION 0 6995971, ACTIVE 11 sec, OS thread id 5976 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 5 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1 MySQL thread id 5, query id 57 pettyb.pvi.com 172.18.120.100 INUSER Updating UPDATE tmp_tbl_1 SET ITEMS = 1 WHERE TMP1_ID = '2000000060_00003E3DY1BJ' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 478408 n bits 72 index `PRIMARY` of table `inow6`.`tmp_tbl_1` trx id 0 6995971 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 23; hex 323030303030303036305f30303030334533445931424a; asc 2000000060_00003E3DY1BJ;; 1: len 6; hex 0000006abcea; asc j ;; 2: len 7; hex 0000074ccc221b; asc L " ;; 3: len 4; hex 80000001; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 478408 n bits 72 index `PRIMARY` of table `inow6`.`tmp_tbl_1` trx id 0 6995971 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 23; hex 323030303030303036305f30303030334533445931424a; asc 2000000060_00003E3DY1BJ;; 1: len 6; hex 0000006abcea; asc j ;; 2: len 7; hex 0000074ccc221b; asc L " ;; 3: len 4; hex 80000001; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 0 6995972 Purge done for trx's n:o < 0 6995459 undo n:o < 0 0 History list length 15 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 6995971, not started, OS thread id 5976 MySQL thread id 5, query id 57 pettyb.pvi.com 172.18.120.100 INUSER ---TRANSACTION 0 0, not started, OS thread id 5144 MySQL thread id 4, query id 60 pettyb.pvi.com 172.18.120.100 INUSER SHOW ENGINE INNODB STATUS ---TRANSACTION 0 6995970, ACTIVE 25 sec, OS thread id 724 5 lock struct(s), heap size 1024, 2 row lock(s), undo log entries 2 MySQL thread id 3, query id 52 pettyb.pvi.com 172.18.120.100 INUSER -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (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 106 OS file reads, 45 OS file writes, 25 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.20 writes/s, 0.20 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 0 inserts, 0 merged recs, 0 merges Hash table size 2365829, node heap has 1 buffer(s) 0.00 hash searches/s, 0.30 non-hash searches/s --- LOG --- Log sequence number 8 2679485922 Log flushed up to 8 2679485922 Last checkpoint at 8 2679485749 0 pending log writes, 0 pending chkp writes 20 log i/o's done, 0.20 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 648317140; in additional pool allocated 1413504 Dictionary memory allocated 26592 Buffer pool size 36480 Free buffers 36382 Database pages 97 Modified db pages 5 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 96, created 1, written 25 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 3636, state: sleeping Number of rows inserted 3, updated 1, deleted 0, read 2 0.00 inserts/s, 0.10 updates/s, 0.00 deletes/s, 0.10 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================