mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
110410 9:40:54 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 83736 1_second, 83735 sleeps, 8367 10_second, 75 background, 75 flush
srv_master_thread log flush and writes: 84278
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 23851, signal count 35580
Mutex spin waits 160416, rounds 575732, OS waits 10558
RW-shared spins 11180, OS waits 8971; RW-excl spins 1457, OS waits 4185
Spin rounds per wait: 3.59 mutex, 25.40 RW-shared, 101.31 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
110409 4:31:17
*** (1) TRANSACTION:
TRANSACTION 1C8E7AFE, ACTIVE 16 sec, process no 7485, OS thread id 1093081408 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 186, query id 13658737 ****** Updating
******
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 386 page no 53459 n bits 432 index `PRIMARY` of table ****** trx id 1C8E7AFE lock_mode X locks rec but not gap waiting
Record lock, heap no 186 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
0: len 4; hex 80a2df32; asc 2;;
1: len 6; hex 00001a52cae9; asc R ;;
2: len 7; hex 800000002d3d98; asc -= ;;
3: len 4; hex 002d95cb; asc - ;;
4: len 1; hex 80; asc ;;
5: SQL NULL;
6: len 1; hex 80; asc ;;
7: len 1; hex 80; asc ;;
8: SQL NULL;
9: SQL NULL;
10: len 4; hex 4d92f610; asc M ;;
11: len 4; hex 80341500; asc 4 ;;
12: len 4; hex 80000000; asc ;;
*** (2) TRANSACTION:
TRANSACTION 1C8E795A, ACTIVE 65 sec, process no 7485, OS thread id 1527142720 fetching rows, thread declared inside InnoDB 317
mysql tables in use 2, locked 2
85123 lock struct(s), heap size 13466040, 39604860 row lock(s), undo log entries 48520
MySQL thread id 21674, query id 13658762 ****** Sending data
******
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 386 page no 53459 n bits 432 index `PRIMARY` of table ****** trx id 1C8E795A lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
0: len 4; hex 80a2de74; asc t;;
1: len 6; hex 00001a52cae9; asc R ;;
2: len 7; hex 800000002d3388; asc -3 ;;
3: len 4; hex 002d2996; asc -) ;;
4: len 1; hex 80; asc ;;
5: SQL NULL;
6: len 1; hex 80; asc ;;
7: len 1; hex 80; asc ;;
8: SQL NULL;
9: SQL NULL;
10: len 4; hex 4d92f5e9; asc M ;;
11: len 4; hex 803414ff; asc 4 ;;
12: len 4; hex 80000000; asc ;;作者: cenalulu 时间: 2011-04-10 16:46
没有办法消除~ 是不是因为dump信息太长导致下面的status显示不全了？
可以考虑自己造个小型死锁把这个信息冲掉。
具体见： http://www.xaprb.com/blog/2006/0 ... -deadlock-in-mysql/作者: LMUser 时间: 2011-04-10 22:45 回复 2#cenalulu
是的，信息太长了，SHOW ENGINE InnoDB STATUS 显示不全，怎么也消除不掉，现在看了你给的链接就知道怎么办了；