Mysql如何解決死鎖問題

145次閱讀

共計(jì) 3323 個(gè)字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。

這篇文章主要為大家展示了“Mysql 如何解決死鎖問題”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓丸趣 TV 小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“Mysql 如何解決死鎖問題”這篇文章吧。

TRANSACTION 8631FDC, ACTIVE 0 sec inserting

mysql tables in use 2, locked 2

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

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

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

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

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

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

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

*** (2) TRANSACTION:

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

mysql tables in use 2, locked 2

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

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

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

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

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

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

……………………………

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

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

*** WE ROLL BACK TRANSACTION (2)

分析：

1）insert …. select … 語句，在 MYSQL 里，會(huì)對(duì) select … 涉及的所有記錄進(jìn)行鎖定，這是特殊的 select 加 X 鎖的情況，原因是為保證數(shù)據(jù)的一致性（M- S 環(huán)境），假設(shè)不鎖定，在執(zhí)行過程中，select 包含的表一直在做插入操作，那么 M 端 select 出來的記錄數(shù)就會(huì)比 S 端少，數(shù)據(jù)就會(huì)不一致。

2）查看 select 的執(zhí)行計(jì)劃

點(diǎn)擊 (此處) 折疊或打開

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;

+—-+————-+———————+——+—————+——+———+——+——+————-+

+—-+————-+———————+——+—————+——+———+——+——+————-+

+—-+————-+———————+——+—————+——+———+——+——+————-+

1 row in set (0.00 sec)

通過索引 1）可以更快的定位數(shù)據(jù) 2）鎖定更少的行。

以上是“Mysql 如何解決死鎖問題”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

正文完