Shulou(Shulou.com)06/01 Report--

[author]

Liu Bo: senior database manager of Ctrip Technical support Center, mainly concerned with the operation and maintenance and fault handling of Sql server and Mysql.

[environment]

Version number: 5.6.21

Isolation level: REPEATABLE READ

[problem description]

After receiving the monitoring alarm, an online application DeadLock reports an error, which appears on time every 15 minutes. The error statistics are as follows:

Log in to the Mysql server to view the log:

Mysql > show engine innodb status\ gateway * (1) TRANSACTION:TRANSACTION 102973, ACTIVE 11 sec starting index readmysql tables in use 3, locked 3LOCK WAIT 4 lock struct (s), heap size 1136, 3 row lock (s) MySQL thread id 6, OS thread handle 140024996574976, query id 83 localhost us updatingUPDATE TestTableSET column1 = 1 OS thread handle column 2 = sysdate () Column3= '026'Column4 = 0AND column5 = 485AND column6 =' SEK'*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 417 page no 1493 n bits 1000 index idx_column6 of table test.TestTable trx id 102973 lock_mode X waitingRecord lock, heap no 859 PHYSICAL RECORD: n_fields 2 Compact format; info bits 00: len 3; hex 53454b; asc SEK;;1: len 8; hex 800000007e1452; asc ~ R * (2) TRANSACTION:TRANSACTION 102972, ACTIVE 26 sec starting index readmysql tables in use 3, locked 3219 lock struct (s), heap size 24784, 2906 row lock (s), undo log entries 7MySQL thread id 5, OS thread handle 140024996841216, query id 84 localhost us updatingUPDATE TestTableSET Column1 = 1 sysdate 2 = sysdate (), Column3 = '026'Column4 = 0AND Column5 = 485AND Column6 =' SEK'*** (2) HOLDS THE LOCK (S): RECORD LOCKS space id 417 page no 1493n bits 1000 index idx_Column6 of table test.TestTable trx id 102972 lock_mode XRecord lock Heap no 1 PHYSICAL RECORD: n_fields 1 Compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 3; hex 53454b; asc SEK;;1: len 8; hex 80000000007e1452; asc ~ R * (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 601 page no 89642 n bits 1000 index idx_column6 of table test.TestTable trx id 32231892482 lock_mode X locks rec but not gap waitingRecord lock, heap no 38 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 3 Hex 53454b; asc SEK;;1: len 8; hex 800000007eea14; asc ~

At a glance, updating the same row of the same index should be a Block, reporting the error of TimeOut is right, how can it be reported to DeadLock?

[preliminary analysis]

First analyze (2) TRANSACTION,TRANSACTION 32231892482.

The waiting lock information is:

0: len 3; hex 53454b; asc SEK;;1: len 8; hex 80000000007ee14; asc

The lock information held is:

0: len 3; hex 53454b; asc SEK;;1: len 8; hex 80000000007eeac4; asc

Then analyze (1) TRANSACTION,TRANSACTION 32231892617.

The waiting lock information is:

0: len 3; hex 53454b; asc SEK;;1: len 8; hex 80000000007eeac4; asc

So you can draw a deadlock table, and the two resources depend on each other, resulting in a deadlock:

TRANSACTIONHoldWait3223189261753454b\ 80000000007eea1453454b\ 80000000007eeac43223189248253454b\ 80000000007eeac453454b\ 80000000007eea14

Let's take another look at the explain results:

Mysql > desc UPDATE TestTable SET Column1=1, Column2 = sysdate (), Column3 = '025' Column4 = 0 AND Column5 = 477 AND Column6 =' SEK'\ G

* * 1. Row *

Id: 1

Select_type: UPDATE

Table: TestTable

Partitions: NULL

Type: index_merge

Possible_keys: column5_index,idx_column5_column6_Column1,idxColumn6

Key: column5_index,idxColumn6

Key_len: 8,9

Ref: NULL

Rows: 7

Filtered: 100.00

Extra: Using intersect (column5_index,idxColumn6); Using where

You can see the EXTRA column:

Using intersect (column5_index,idxColumn6)

Since 5. 1, index merge optimization technology has been introduced, and multiple indexes can be used for conditional scanning on the same table.

Related documentation: http://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.

[simulation and verification]

Based on the above preliminary analysis, it is speculated that it should be caused by intersect, so simulate and verify in the test environment, open 2 session simulation deadlocks:

Time series Session1Session21Begin;2UPDATE TestTable SET Column2 = sysdate () Column4 = 0 AND Column5 = 47 AND Column6 = 'SEK executed successfully, affecting 7 lines 3Beginath4UPDATE TestTable SET Column2 = sysdate (), Column4 = 0 AND Column5 = 485 AND Column6 =' SEK'; by Blocking5UPDATE TestTable SET Column2 = sysdate (), Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK'; successfully executed ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

According to the above information, it can be found that although Session2 is Block, it also acquires some X locks of the resources needed by Session1 in time series 5. You can open another query select count (Column5) from TestTable where Column5 = 485, set SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, query the rows with Column5 = 485, and observe the information of lock waiting:

Mysql > SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id

Mysql > SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\ G * * 1. Row * * waiting_trx_id: 103006waiting_thread: 36waiting_query: UPDATE TestTable SET Column1 = 1, Column2 = sysdate () Column3 = '026' Column4 = 0 AND Column5 = 485 AND Column6 =' SEK'blocking_trx_id: 103003blocking_thread: 37blocking_query: NULL** 2. Row * * waiting_trx_id: 421500433538672waiting_thread: 39waiting_query: select count (Column5) from TestTable where Column5 = 485blocking_trx_id: 103006blocking_thread: 36blocking_query: UPDATE TestTable SET Column1 = 1 Column2 = sysdate (), Column3 = '026' Column4 = 0 AND Column5 = 485 AND Column6 =' SEK'2 rows in set, 1 warning (0.00 sec) mysql > select * from information_schema.innodb_lock_waits\ G * * 1. Row * * requesting_trx_id: 103006requested_lock_id: 103006:417:1493:859blocking_trx_id: 103003blocking_lock_id: 103003 103006requested_lock_id: 859 * * 2. Row * * requesting_trx_id: 421500433538672requested_lock_id: 421500433538672:417:749:2blocking_trx_id: 103006blocking_lock_id: 103006 421500433538672requested_lock_id 749 22 rows in set 1 warning (0.00 sec) mysql > select * from INNODB_LOCKS\ G * * 1. Row * * lock_id: 103006:417:1493:859lock_trx_id: 103006lock_mode: Xlock_type: RECORDlock_table: test.TestTablelock_index: idxColumn6lock_space: 417lock_page: 1493lock_rec: 859lock_data: 'SEK' 8262738 row * * lock_id: 103003:417:1493:859lock_trx_id: 103003lock_mode: Xlock_type: RECORDlock_table:test.TestTablelock_index: idxColumn6lock_space: 417lock_page: 1493lock_rec: 859lock_data: 'SEK' 8262738 row * * lock_id: 421500433538672:417:749:2lock_trx_id: 421500433538672lock_mode: Slock_type: RECORDlock_table: test.TestTablelock_index: column5_indexlock_space: 417lock_page: 749lock_rec: 2lock_data: 485 8317620 rows in set * 4. Row * * lock_id: 103006:417:749:2lock_trx_id: 103006lock_mode: Xlock_type: RECORDlock_table: test.TestTablelock_index: column5_indexlock_space: 417lock_page: 749lock_rec: 2lock_data: 485,0.00 rows in set, 1 warning (83176204 sec)

You can see that Session2,trx_id 103006 blocks trx_id 421500433538672, and trx_id 421500433538672 requested_lock happens to be lock_data: 485,8317620. Thus it can be seen that although Session2 is not block, it still acquires the locks related to Index column5_index. Block is due to intersect, and the lock of idxColumn6 is also needed. Now that the idea is clear, simplify the information of the whole lock assignment, as shown in the following table (the requested lock is indicated in cyan, and the lock to be acquired but not acquired is indicated in red):

Point-in-time Session1Session21477 SEK2485 SEK3485 SEK deadlock occurs

You can see that the two resources of 485 SEK form a ring, resulting in deadlock.

[solution]

The best way is to add a joint index of column5 and Column6. The situation in our environment at that time found that the filtering of Column6 was very low, so we deleted the index of Column6.

After the index was deleted around 10:55, the error did not occur again:

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.