Shulou(Shulou.com)05/31 Report--

This article mainly shows you "Mysql how to solve the deadlock problem", the content is easy to understand, clear, hope to help you solve the doubt, the following let the editor lead you to study and learn "Mysql how to solve the deadlock problem" this article.

Show engine innodb status\ G

* * (1) TRANSACTION:

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, '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 [appointment date]\ 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 [BusinessEmail]\ n [BusinessWebsite]\ n\ n -\ nThis email provided by Demandforce, Inc. To unsubscribe Please click this link:\ n\ n [UnsubscribeLink]\ n\ nsubscription, 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)

Analysis:

1) insert.... Select... Statement, in MYSQL, will be used for select. All the records involved are locked, which is a special case of select plus X lock, because in order to ensure the consistency of the data (Mmurs environment), assuming that it is not locked, during the execution, the table contained in select has been inserting, so the number of records from the select on the M side will be less than that on the S side, and the data will be inconsistent. 2) to view the execution plan of select, click (here) to collapse or open

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)

Key=null,rows=6108 full table scan, no indexes available. Solution: create a composite index in the BusinessId,Type field. By index 1) you can locate data faster and 2) lock fewer rows.

The above is all the contents of the article "how to solve the deadlock problem in Mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.