這篇文章主要為大家展示了“MySQL如何解決死鎖問題”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“Mysql如何解決死鎖問題”這篇文章吧。

西山ssl適用于網站、小程序/APP、API接口等需要進行數據傳輸應用場景，ssl證書未來市場廣闊！成為創新互聯建站的ssl證書銷售渠道，可以享受市場價格4-6折優惠！如果有意向歡迎電話聯系或者加微信：028-86922220（備注：SSL證書合作）期待與您的合作！

show engine innodb status\G    

1. *** (1) TRANSACTION:

2. TRANSACTION 8631FDC, ACTIVE 0 sec inserting

3. mysql tables in use 2, locked 2

4. LOCK WAIT 7 lock struct(s), heap size 1248, 107 row lock(s)

5. MySQL thread id 28162, OS thread handle 0x7f4ab3073700, query id 210340529 tc-demo-dev3.dfengg.com 172.16.2.173 root

6. INSERT INTO Text****(BusinessId,Type,Template,CreatedUserId,LastModifiedUserId,Delivery,DeliveryWindow,BusinessCalendar) SELECT 124001698, 205, '[CustomerFirstName], your next appointment with [Business] is [AppointmentDateTime]. We look forward to seeing you at your upcoming appointment.', 0, 0, IFNULL(96, Delivery),DeliveryWindow,BusinessCalendar FROM Text**** WHERE BusinessId=0 AND Type=205

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

8. RECORD LOCKS space id 794567 page no 6 n bits 88 index `PRIMARY` of table `df1`.`Text****` trx id 8631FDC lock_mode X insert intention waiting

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

10.  0: len 8; hex 73757072656d756d; asc supremum;;

11.  

12. *** (2) TRANSACTION:

13. TRANSACTION 8631FDB, ACTIVE 0 sec setting auto-inc lock

14. mysql tables in use 2, locked 2

15. 5 lock struct(s), heap size 1248, 106 row lock(s)

16. MySQL thread id 28158, OS thread handle 0x7f4a48cd0700, query id 210340528 tc-demo-dev3.dfengg.com 172.16.2.173 root

17. INSERT INTO Text****(BusinessId,Type,Template,CreatedUserId,LastModifiedUserId,Delivery,DeliveryWindow,BusinessCalendar) SELECT 124001699, 406, 'Dear [CustomerFirstName],\n\nYou have an upcoming appointment with [BusinessName]. Please take a minute to review the details of your appointment scheduled for:\n\n[AppointmentDateTime]\n\nIf you are unable to make this appointment, it is important that you call us at [BusinessPhone] as soon as possible so we can make other arrangements.\n\nWe look forward to seeing you at your upcoming appointment.\n\nSincerely,\n\n[BusinessName]\n[BusinessPhone]\n[BusinessEmail]\n[BusinessWebsite]\n\n-----\nThis email provided by Demandforce, Inc. To unsubscribe, please click this link:\n\n[UnsubscribeLink]\n\n', 0, 0, IFNULL(504, Delivery),DeliveryWindow,BusinessCalendar FROM Text**** WHERE BusinessId=0 AND Type=406

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

19. RECORD LOCKS space id 794567 page no 6 n bits 88 index `PRIMARY` of table `df1`.`Text****` trx id 8631FDB lock mode S

20. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

21.  

22. .................................

23.  

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

25. TABLE LOCK table `df1`.`Text****` trx id 8631FDB lock mode AUTO-INC waiting

26. *** WE ROLL BACK TRANSACTION (2)

分析： 

 1）insert .... select ...語句，在MYSQL里，會對select ...涉及的所有記錄進行鎖定，這是特殊的select加X鎖的情況，原因是為保證數據的一致性（M-S環境），假設不鎖定，在執行過程中，select 包含的表一直在做插入操作，那么M端select出來的記錄數就會比S端少，數據就會不一致。   2）查看select的執行計劃   點擊(此處)折疊或打開

1. mysql> explain SELECT 124001698, 205, '[CustomerFirstName], your next appointment with [Business] is [AppointmentDateTime]. We look forward to seeing you at your upcoming appointment.', 0, 0, IFNULL(96, Delivery),DeliveryWindow,BusinessCalendar FROM Text**** WHERE BusinessId=0 AND Type=205;

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

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

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

5. | 1 | SIMPLE | Text**** | ALL | NULL | NULL | NULL | NULL | 6108 | Using where |

6. +----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+

7. 1 row in set (0.00 sec)

key=null,rows=6108 全表掃描，沒有可用的索引。   解決辦法： 在BusinessId,Type字段建復合索引。   通過索引1）可以更快的定位數據2）鎖定更少的行。

以上是“Mysql如何解決死鎖問題”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注創新互聯行業資訊頻道！