mysql innodb死锁问题

Question

  表1
CREATE TABLE tbl_AA(
    aa    INT UNSIGNED NOT NULL,   
    bb    INT UNSIGNED NOT NULL,    
    cc    INT UNSIGNED NOT NULL,    
    dd    INT UNSIGNED NOT NULL,  
    ee    INT UNSIGNED NOT NULL,   
    ff    INT UNSIGNED NOT NULL,    
    PRIMARY KEY(aa)
) ENGINE = InnoDB DEFAULT CHARSET=gb2312 COLLATE=gb2312_bin;表2
CREATE TABLE tbl_BB(
    gg    varchar(100) NOT NULL,    
    aa   varchar(100) NOT NULL,    
    bb    TINYINT UNSIGNED NOT NULL,    
    hh    TINYINT UNSIGNED NOT NULL,    
    PRIMARY KEY  USING BTREE (gg,bb)
) ENGINE = InnoDB DEFAULT CHARSET=gb2312 COLLATE=gb2312_bin;
涉及的sql语句
update tbl_AA set bb=5,cc =6,dd = 7, ee = 8,ff = 9 where aa = 1;
delete from tbl_BB where hh = 0 or (gg IN (10,11) and ((aa,bb) NOT IN (SELECT aa, bb FROM tbl_AA)))

Answer 1

出现死锁的日志信息
LATEST DETECTED DEADLOCK
------------------------
130726 12:07:03
*** (1) TRANSACTION:
TRANSACTION 0 2360846446, ACTIVE 2 sec, process no 61546, OS thread id 3749423984 starting index read
mysql tables in use 9, locked 9
LOCK WAIT 36 lock struct(s), heap size 5504
MySQL thread id 26063, query id 460433877 localhost root Sending data
delete from tbl_BB where hh = 0 or (gg IN (10,11) and ((aa,bb) NOT IN (SELECT aa, bb FROM tbl_AA)))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 6203 n bits 176 index `PRIMARY` of table `test/tbl_AA` trx id 0 2360846446 lock_mode X locks rec but not gap waitingRecord lock, heap no 14 PHYSICAL RECORD: n_fields 6; compact format; info bits 0*** (2) TRANSACTION:
TRANSACTION 0 2360846426, ACTIVE 4 sec, process no 61546, OS thread id 3786451824 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
222 lock struct(s), heap size 27968, undo log entries 942
MySQL thread id 26032, query id 460442655 localhost root statistics
update tbl_AA set bb=5,cc =6,dd = 7, ee = 8,ff = 9 where aa = 1;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 6203 n bits 176 index `PRIMARY` of table `test/tbl_AA` trx id 0 2360846426 lock_mode X locks rec but not gapRecord lock, heap no 14 PHYSICAL RECORD: n_fields 6; compact format; info bits 0*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 6175 n bits 176 index `PRIMARY` of table `test/tbl_AA` trx id 0 2360846426 lock mode S locks rec but not gap waitingRecord lock, heap no 38 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

Answer 2

出现死锁日志如上所述：分析出现死锁的sql语句delete from tbl_BB where hh = 0 or (gg IN (10,11) and ((aa,bb) NOT IN (SELECT aa, bb FROM tbl_AA))) 黄色部分，申请的锁为排他锁X锁，由于是select语句没有查询条件，申请的是表锁定，一条记录锁定，再锁定下一条记录。即先申请一条记录的X锁，再申请下一条记录的X锁。   update tbl_AA set bb=5,cc =6,dd = 7, ee = 8,ff = 9 where aa = 1;
该sql语句是不是先申请的主键X锁，然后再根据所修改的字段，再将相应的字段修改前所对应的记录锁定。
该语句申请的锁为主键锁，即（X锁）。但是最后出现死锁的时候，先获取的是主键锁，再获取主键的共享锁。  请问，上述分析sql语句获取的锁是都正确。还有为什么update tbl_AA set bb=5,cc =6,dd = 7, ee = 8,ff = 9 where aa = 1;最后要获取的是主键的共享锁。   还有，如果要避免死锁，请问delete from tbl_BB where hh = 0 or (gg IN (10,11) and ((aa,bb) NOT IN (SELECT aa, bb FROM tbl_AA)))这个sql语句怎么修改，不会出现死锁，又不影响性能。

Answer 3

tbl_AA上建立AA、BB复合索引
将NOT　IN－＞LEFT　JOIN

Answer 4

请问，为什么要给tbl_AA上（aa,bb）建立复合索引，还有aa字段本来就是tbl_AA的主键。还有一个问题，为什么update tbl_AA set bb=5,cc =6,dd = 7, ee = 8,ff = 9 where aa = 1;最后还要获取的是主键的共享锁