show engine innodb status \G   点击(此处)折叠或打开

1. *** (1) TRANSACTION:

2. TRANSACTION 9CC3, ACTIVE 0 sec, OS thread id 2384 fetching rows

3. mysql tables in use 2, locked 2

4. LOCK WAIT 6 lock struct(s), heap size 1024, 12 row lock(s), undo log entries 2

5. MySQL thread id 20, query id 138891 localhost 127.0.0.1 root Sending data

6. DELETE FROM User WHERE ID IN (SELECT UserID FROM BusinessUser WHERE BusinessID=124001692)

7. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

8. RECORD LOCKS space id 0 page no 92015 n bits 88 index `PRIMARY` of table `df_demo`.`user` trx id 9CC3 lock_mode X waiting

9. Record lock, heap no 14 PHYSICAL RECORD: n_fields 48; compact format; info bits 0

10.  0: len 4; hex 80000788; asc ;;

11.  

12.  .......................................................................

13.  

14. *** (2) TRANSACTION:

15. TRANSACTION 9CC0, ACTIVE 0 sec, OS thread id 1696 starting index read, thread declared inside InnoDB 370

16. mysql tables in use 3, locked 3

17. 9 lock struct(s), heap size 1024, 59 row lock(s), undo log entries 10

18. MySQL thread id 21, query id 138887 localhost 127.0.0.1 root Sending data

19. DELETE FROM UserOptions WHERE UserID IN (SELECT u.ID FROM User u JOIN BusinessUser bu ON u.ID=bu.UserID WHERE bu.BusinessID=124001691)

20. *** (2) HOLDS THE LOCK(S):

21. RECORD LOCKS space id 0 page no 92015 n bits 88 index `PRIMARY` of table `df_demo`.`user` trx id 9CC0 lock mode S locks rec but not gap

22. Record lock, heap no 14 PHYSICAL RECORD: n_fields 48; compact format; info bits 0

23.  0: len 4; hex 80000788; asc ;;

24. ..........................................................................

25.  

26. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

27. RECORD LOCKS space id 0 page no 92015 n bits 88 index `PRIMARY` of table `df_demo`.`user` trx id 9CC0 lock mode S locks rec but not gap waiting

28. Record lock, heap no 11 PHYSICAL RECORD: n_fields 48; compact format; info bits 0

29.  0: len 4; hex 80000786; asc ;;

30.  

31. ...........................................................................

32.  

33.  

34. *** WE ROLL BACK TRANSACTION (1)

1：分析： 看看引起死锁的SQL执行计划：   点击(此处)折叠或打开

1. mysql> explain select * FROM User WHERE ID IN (SELECT UserID FROM BusinessUser WHERE BusinessID=124001692);

2. +----+--------------------+--------------+----------------+--------------------------------+-----------------+---------+-

3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

4. +----+--------------------+--------------+----------------+--------------------------------+-----------------+---------+-

5. | 1 | PRIMARY | User | ALL | NULL | NULL | NULL | NULL | 6095 | Using where |

6. | 2 | DEPENDENT SUBQUERY | BusinessUser | index_subquery | idx_UserID_Type,idx_BusinessID | idx_UserID_Type | 5 | func | 1 | Using where |

7. +----+--------------------+--------------+----------------+--------------------------------+-----------------+---------+-

8. 2 rows in set (0.00 sec)

User表全表扫描，会引起死锁。   2：解决 重写SQL，把IN改成JOIN，执行计划如下：   点击(此处)折叠或打开

1. mysql> explain select a.* FROM User a inner join BusinessUser b on a.id=b.userid WHERE b.BusinessID=124001692;

2. +----+-------------+-------+--------+--------------------------------+----------------+---------+--------------+------+

3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

4. +----+-------------+-------+--------+--------------------------------+----------------+---------+--------------+------+

5. | 1 | SIMPLE | b | ref | idx_UserID_Type,idx_BusinessID | idx_BusinessID | 5 | const | 1 | Using where |

6. | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | ng1.b.UserID | 1 | |

7. +----+-------------+-------+--------+--------------------------------+----------------+---------+--------------+------+

8. 2 rows in set (0.00 sec)

两表都用到索引了。  

问题得到解决。