Mysql如何解决死锁问题

57次阅读
没有评论

共计 3323 个字符,预计需要花费 9 分钟才能阅读完成。

这篇文章主要为大家展示了“Mysql 如何解决死锁问题”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让丸趣 TV 小编带领大家一起研究并学习一下“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 里,会对 select … 涉及的所有记录进行锁定,这是特殊的 select 加 X 锁的情况,原因是为保证数据的一致性(M- S 环境),假设不锁定,在执行过程中,select 包含的表一直在做插入操作,那么 M 端 select 出来的记录数就会比 S 端少,数据就会不一致。
 
2)查看 select 的执行计划
 

点击 (此处) 折叠或打开

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;

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

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

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

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

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

1 row in set (0.00 sec)


通过索引 1)可以更快的定位数据 2)锁定更少的行。

以上是“Mysql 如何解决死锁问题”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注丸趣 TV 行业资讯频道!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-15发表,共计3323字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)