请教死锁分析

signmem

各位好， 下面是我的锁信息 (mysql-5.5.34) (64bit)

SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 78453, signal count 49529
Mutex spin waits 984985, rounds 3237726, OS waits 77490
RW-shared spins 989, rounds 26950, OS waits 845
RW-excl spins 51, rounds 1861, OS waits 36
Spin rounds per wait: 3.29 mutex, 27.25 RW-shared, 36.49 RW-excl

问题：请教上面信息如何理解？

------------------------
LATEST DETECTED DEADLOCK
------------------------
131025 13:41:09
*** (1) TRANSACTION:
TRANSACTION 65FDB, ACTIVE 9 sec fetching rows (读取了行)
mysql tables in use 1, locked 1 (有一个表被锁定)
LOCK WAIT 7 lock struct(s), heap size 1248, 268 row lock(s), undo log entries 3 (锁定行数，3次 UNDO)
MySQL thread id 746, OS thread handle 0x7fda7568f700, query id 902061 10.1.1.25 mini189 Updating
update index_species_situaction s set s.new_version= '13102513410032000001' where s.new_version != 'DELETE' and s.species_id ='W6021601'   (当前 SQL )

问题: 请问 上述信息中， LOCK WAIT 7 lock struct(s), 怎么理解呢？

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1700 n bits 72 index `GEN_CLUST_INDEX` of table `189mini`.`index_species_situaction` trx id 65FDB lock_mode X waiting (排他锁)
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 6; hex 00000000e1b8; asc       ;;
1: len 6; hex 000000065fdc; asc     _ ;;
2: len 7; hex c10000067f0110; asc        ;;
3: len 14; hex 3230313331303235343530333134; asc 20131025450314;;
4: len 8; hex 5335463630383131; asc S5F60811;;
5: len 8; hex 5736303435464633; asc W6045FF3;;
6: len 5; hex 7a685f434e; asc zh_CN;;
7: len 8; hex 8000124f1e8adfdd; asc    O    ;;
8: len 3; hex 4e4557; asc NEW;;
9: len 3; hex 4e4557; asc NEW;;


*** (2) TRANSACTION:
TRANSACTION 65FDC, ACTIVE 9 sec starting index read ( 事务在读索引)
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1248, 40 row lock(s), undo log entries 2
MySQL thread id 804, OS thread handle 0x7fda7564e700, query id 904061 10.1.1.25 mini189 Updating
update INDEX_SPECIES_SITUACTION h set h.new_version='DELETE'
                                                        where h.specification_id =  NAME_CONST('_specification_id',_utf8'S5F60811' COLLATE 'utf8_general_ci') and h.species_id= NAME_CONST('_species_id',_utf8'W6045FF3' COLLATE 'utf8_general_ci')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1700 n bits 72 index `GEN_CLUST_INDEX` of table `189mini`.`index_species_situaction` trx id 65FDC lock_mode X locks rec but not gap (表索引被锁定，但不完整)
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 6; hex 00000000e1b8; asc       ;;
1: len 6; hex 000000065fdc; asc     _ ;;
2: len 7; hex c10000067f0110; asc        ;;
3: len 14; hex 3230313331303235343530333134; asc 20131025450314;;
4: len 8; hex 5335463630383131; asc S5F60811;;
5: len 8; hex 5736303435464633; asc W6045FF3;;
6: len 5; hex 7a685f434e; asc zh_CN;;
7: len 8; hex 8000124f1e8adfdd; asc    O    ;;
8: len 3; hex 4e4557; asc NEW;;
9: len 3; hex 4e4557; asc NEW;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 491 n bits 248 index `GEN_CLUST_INDEX` of table `189mini`.`index_species_situaction` trx id 65FDC lock_mode X waiting (排他锁)
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 6; hex 00000000069e; asc       ;;
1: len 6; hex 00000000054f; asc      O;;
2: len 7; hex d0000001ea0110; asc        ;;
3: len 14; hex 3230313330373035343134323030; asc 20130705414200;;
4: len 8; hex 5335463738454238; asc S5F78EB8;;
5: len 8; hex 5736303036383537; asc W6006857;;
6: len 5; hex 7a685f434e; asc zh_CN;;
7: len 8; hex 8000124f0b7883de; asc    O x  ;;
8: len 6; hex 555044415445; asc UPDATE;;
9: len 6; hex 555044415445; asc UPDATE;;

*** WE ROLL BACK TRANSACTION (2)
问题： <- 这里指事务 2 需要回滚吗？

幕南风

死锁 innodb会自动回滚

chinafenghao

@signmem
上面那个咋个理解，我也高球不懂啊。求高手

下面那个死锁，很明显是sql执行顺序的问题。让程序调吧。如果几率很低，可以不用管。