怎样解决Mysql死锁问题

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

10多年的裕民网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。成都营销网站建设的优势是能够根据用户设备显示端的尺寸不同，自动调整裕民建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联从事“裕民网站设计”,“裕民网站推广”以来，每个客户项目都认真落实执行。

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死锁问题问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注创新互联行业资讯频道了解更多相关知识。