怎樣解決MySQL死鎖問題，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

10多年的裕民網(wǎng)站建設經(jīng)驗，針對設計、前端、開發(fā)、售后、文案、推廣等六對一服務，響應快，48小時及時工作處理。成都營銷網(wǎng)站建設的優(yōu)勢是能夠根據(jù)用戶設備顯示端的尺寸不同，自動調整裕民建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設計，從而大程度地提升瀏覽體驗。創(chuàng)新互聯(lián)從事“裕民網(wǎng)站設計”,“裕民網(wǎng)站推廣”以來，每個客戶項目都認真落實執(zhí)行。

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執(zhí)行計劃：   點擊(此處)折疊或打開

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，執(zhí)行計劃如下：   點擊(此處)折疊或打開

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死鎖問題問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關知識。