怎样解决Mysql死锁问题

怎样解决Mysql死锁问题，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

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)

两表都用到索引了。  

问题得到解决。

关于怎样解决Mysql死锁问题问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注编程网数据库频道了解更多相关知识。