| Bug #43700 | dead lock after insert inside transaction | ||
|---|---|---|---|
| Submitted: | 17 Mar 2009 13:27 | Modified: | 18 Mar 2009 8:56 |
| Reporter: | Alexandr Nikulin | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S3 (Non-critical) |
| Version: | Ver 14.12 Distrib 5.0.54 | OS: | Any (ubuntu) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | dead lock, inert, transaction | ||
[17 Mar 2009 13:27]
Alexandr Nikulin
[17 Mar 2009 13:37]
Valeriy Kravchuk
Thank you for the problem report. Please, send the results of SHOW INNODB STATUS\G immediately after getting the deadlock. I hope the output will make it more clear what happened. By the way, why do you do SELECT ... FOR UPDATE (blocking reads) before INSERTs?
[18 Mar 2009 8:14]
Alexandr Nikulin
mysql> SHOW INNODB STATUS\G *************************** 1. row *************************** Status: ===================================== 090318 11:13:04 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 13 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 67, signal count 67 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 104, OS waits 52; RW-excl spins 19, OS waits 15 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 090318 11:12:46 *** (1) TRANSACTION: TRANSACTION 0 383854, ACTIVE 33 sec, process no 5397, OS thread id 3035831184 inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id 24, query id 873 localhost root update insert into table1()values() *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 861 n bits 72 index `GEN_CLUST_INDEX` of table `bug_test/table1` trx id 0 383854 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION 0 383855, ACTIVE 20 sec, process no 5397, OS thread id 3036101520 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL thread id 17, query id 874 localhost root update insert into table1()values() *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 861 n bits 72 index `GEN_CLUST_INDEX` of table `bug_test/table1` trx id 0 383855 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 861 n bits 72 index `GEN_CLUST_INDEX` of table `bug_test/table1` trx id 0 383855 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 0 383856 Purge done for trx's n:o < 0 383854 undo n:o < 0 0 History list length 3 Total number of lock structs in row lock hash table 3 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 383855, not started, process no 5397, OS thread id 3036101520 MySQL thread id 17, query id 875 localhost root SHOW INNODB STATUS ---TRANSACTION 0 383854, ACTIVE 51 sec, process no 5397, OS thread id 3035831184 4 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 24, query id 873 localhost root -------- 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 488 OS file reads, 521 OS file writes, 287 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.31 writes/s, 0.23 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 779, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 34050512 Log flushed up to 0 34050512 Last checkpoint at 0 34050512 0 pending log writes, 0 pending chkp writes 172 log i/o's done, 0.08 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 18896158; in additional pool allocated 1048448 Buffer pool size 512 Free buffers 69 Database pages 441 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 441, created 0, written 331 0.00 reads/s, 0.00 creates/s, 0.15 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 process no. 5397, id 3008211856, state: waiting for server activity Number of rows inserted 19, updated 0, deleted 1, read 12052 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set, 1 warning (0.04 sec)
[18 Mar 2009 8:56]
Sveta Smirnova
Thank you for the feedback. I assume you didn't change default transaction isolation level. In this case this is not a bug. See bug #43211 for explanation.
