- 论坛徽章:
- 0
|
本帖最后由 cenalulu 于 2012-08-01 15:29 编辑
发现一张表几乎每天都会出现死锁现象
show engine innodb status ,结果如下:- | InnoDB | |
- =====================================
- 120801 11:39:27 INNODB MONITOR OUTPUT
- =====================================
- Per second averages calculated from the last 57 seconds
- ----------
- SEMAPHORES
- ----------
- OS WAIT ARRAY INFO: reservation count 51384484, signal count 48846140
- Mutex spin waits 0, rounds 1018011722, OS waits 16297634
- RW-shared spins 50943146, OS waits 25603297; RW-excl spins 10433570, OS waits 5954627
- ------------------------
- LATEST DETECTED DEADLOCK
- ------------------------
- 120801 10:26:55
- *** (1) TRANSACTION:
- TRANSACTION 1 3307885574, ACTIVE 10 sec, process no 9567, OS thread id 139743974278928 fetching rows
- mysql tables in use 1, locked 1
- LOCK WAIT 5366 lock struct(s), heap size 620528, 36645 row lock(s)
- MySQL thread id 451507729, query id 6609691877 192.168.10.161 web Searching rows for update
- UPDATE user_notice_2 SET is_read = '1' WHERE type = '12' AND user_id = '269786'
- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 0 page no 3515164 n bits 320 index `PRIMARY` of table `xxxx`.`user_notice_2` trx id 1 3307885574 lock_mode X locks rec but not gap waiting
- Record lock, heap no 172 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
- 0: len 4; hex 8031acdc; asc 1 ;; 1: len 6; hex 0001c52a52c7; asc *R ;; 2: len 7; hex 00003f8024055b; asc ? $ [;; 3: len 4; hex 8000bfe2; asc ;; 4: len 4; hex 8000f95c; asc \;; 5: len 4; hex 80000000; asc ;; 6: len 4; hex 80000006; asc ;; 7: len 1; hex 81; asc ;; 8: len 4; hex 80041dda; asc ;; 9: len 4; hex 80000903; asc ;; 10: len 4; hex 80000001; asc ;; 11: len 4; hex 80000001; asc ;; 12: len 4; hex cf6f2a42; asc o*B;;
- *** (2) TRANSACTION:
- TRANSACTION 1 3307885255, ACTIVE 16 sec, process no 9567, OS thread id 139743724054288 fetching rows, thread declared inside InnoDB 222
- mysql tables in use 1, locked 1
- 5125 lock struct(s), heap size 587760, 33201 row lock(s), undo log entries 175
- MySQL thread id 451507525, query id 6609689171 192.168.1.58 web Updating
- UPDATE user_notice_2 SET is_read = '1' WHERE type = '6' AND user_id = '269786'
- *** (2) HOLDS THE LOCK(S):
- RECORD LOCKS space id 0 page no 3515164 n bits 320 index `PRIMARY` of table `xxxx`.`user_notice_2` trx id 1 3307885255 lock_mode X locks rec but not gap
- Record lock, heap no 172 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
- 0: len 4; hex 8031acdc; asc 1 ;; 1: len 6; hex 0001c52a52c7; asc *R ;; 2: len 7; hex 00003f8024055b; asc ? $ [;; 3: len 4; hex 8000bfe2; asc ;; 4: len 4; hex 8000f95c; asc \;; 5: len 4; hex 80000000; asc ;; 6: len 4; hex 80000006; asc ;; 7: len 1; hex 81; asc ;; 8: len 4; hex 80041dda; asc ;; 9: len 4; hex 80000903; asc ;; 10: len 4; hex 80000001; asc ;; 11: len 4; hex 80000001; asc ;; 12: len 4; hex cf6f2a42; asc o*B;;
- *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 0 page no 4282304 n bits 320 index `PRIMARY` of table `xxxx`.`user_notice_2` trx id 1 3307885255 lock_mode X locks rec but not gap waiting
- Record lock, heap no 103 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
- 0: len 4; hex 8094ba21; asc !;; 1: len 6; hex 0001c3d9f6be; asc ;; 2: len 7; hex 80001840120110; asc @ ;; 3: len 4; hex 80019c71; asc q;; 4: len 4; hex 800145d5; asc E ;; 5: len 4; hex 80000000; asc ;; 6: len 4; hex 80000006; asc ;; 7: len 1; hex 81; asc ;; 8: len 4; hex 80041dda; asc ;; 9: len 4; hex 8006c3f0; asc ;; 10: len 4; hex 80000001; asc ;; 11: len 4; hex 80000000; asc ;; 12: len 4; hex d0160d7c; asc |;;
- *** WE ROLL BACK TRANSACTION (2)
- ------------
- TRANSACTIONS
- ------------
- Trx id counter 1 3308366016
- Purge done for trx's n:o < 1 3308364543 undo n:o < 0 0
- History list length 36
- LIST OF TRANSACTIONS FOR EACH SESSION:
- ---TRANSACTION 0 0, not started, process no 9567, OS thread id 139743705683728
- MySQL thread id 451506124, query id 6612683330 localhost root
- show engine innodb status
- --------
- 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
- 484129428 OS file reads, 569326131 OS file writes, 14401634 OS fsyncs
- 83.58 reads/s, 19781 avg bytes/read, 24.96 writes/s, 1.75 fsyncs/s
- -------------------------------------
- INSERT BUFFER AND ADAPTIVE HASH INDEX
- -------------------------------------
- Ibuf: size 56, free list len 7387, seg size 7444,
- 32140121 inserts, 32131078 merged recs, 6226493 merges
- Hash table size 9239933, node heap has 17416 buffer(s)
- 1140.79 hash searches/s, 628.04 non-hash searches/s
- ---
- LOG
- ---
- Log sequence number 215 1052551632
- Log flushed up to 215 1052550237
- Last checkpoint at 215 1046136556
- 0 pending log writes, 0 pending chkp writes
- 494759215 log i/o's done, 12.96 log i/o's/second
- ----------------------
- BUFFER POOL AND MEMORY
- ----------------------
- Total memory allocated 4665606282; in additional pool allocated 1040896
- Dictionary memory allocated 1243248
- Buffer pool size 256000
- Free buffers 0
- Database pages 238584
- Modified db pages 7894
- Pending reads 0
- Pending writes: LRU 0, flush list 0, single page 0
- Pages read 662841221, created 1168882, written 117181538
- 100.91 reads/s, 0.02 creates/s, 21.02 writes/s
- Buffer pool hit rate 991 / 1000
- --------------
- ROW OPERATIONS
- --------------
- 0 queries inside InnoDB, 0 queries in queue
- 1 read views open inside InnoDB
- Main thread process no. 9567, id 139744041867024, state: sleeping
- Number of rows inserted 93903841, updated 390672007, deleted 13535688, read 1271485043994
- 1.77 inserts/s, 36.24 updates/s, 0.00 deletes/s, 45229.31 reads/s
- ----------------------------
- END OF INNODB MONITOR OUTPUT
- ============================
复制代码 |
|