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

This article introduces the knowledge of "how to troubleshoot MySQL deadlock warning". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Fault background

During the National Day, I received an alarm that I had never seen before, and then reported a similar occasional alarm intermittently. I was suddenly interested, picked one of them, and explored the story.

* (1) TRANSACTION: TRANSACTION 6286508066, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 9 lock struct (s), heap size 1136, 14 row lock (s), undo log entries 1 MySQL thread id 189619143, OS thread handle 140619931252480, query id 1148803196 10.200.18.103 ke_information updating update `user_feed_ 26`set `notification` = 1, `mtime` = '2020-10-03 09ACTIVE 11' where `user_ id` = 20000126212250 and `action` in ('resblock_weekly',' bizcircle_weekly') 'district_weekly') and `notification` = 0 * * (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_ feed`.`user _ feed_ 26` trx id 6286508066 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 93 PHYSICAL RECORD: n_fields 5 Compact format; info bits 00: len 8; hex 80071afd5112d89a; asc Q; 1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;; 2: len 1; hex 81; asc; 3: len 12; hex 3130343130373333363737; asc 104103743677; 4: len 4; hex 95f12ab5; asc *

From the literal meaning of the log, it is obvious that the MySQL database discovered the deadlock during the execution of the transaction, so how did this deadlock occur, what hidden dangers are hidden behind it, and how to solve it? let's check it out together.

Investigation process

have a blurred vision

At the beginning of receiving this alarm, the first reaction was that different transactions locked each other, resulting in a deadlock. Nine times out of ten, there is something wrong with the logic written in some code snippet. However, after a whole circle of investigation, the code involving this sql has neither opened transactions nor multiple transactions, so the bug of the code can basically be excluded.

So how did these affairs come from? As we all know, MySQL's transaction support is related to the storage engine, MyISAM does not support transactions, INNODB supports transactions, and row-level locks are used in updates. Since our database uses the INNODB engine, it means that the update statement is treated as a transaction. Is that a conflict when updating the same piece of data? So I asked DBA for the deadlock log (database version: 5.7.24 transaction isolation level is RR).

Transaction 1 log:

* (1) TRANSACTION: TRANSACTION 6286508066, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 9 lock struct (s), heap size 1136, 14 row lock (s), undo log entries 1 MySQL thread id 189619143, OS thread handle 140619931252480, query id 1148803196 10.200.18.103 ke_information updating update `user_feed_ 26`set `notification` = 1, `mtime` = '2020-10-03 09ACTIVE 11' where `user_ id` = 20000126212250 and `action` in ('resblock_weekly',' bizcircle_weekly') 'district_weekly') and `notification` = 0 * * (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_ feed`.`user _ feed_ 26` trx id 6286508066 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 93 PHYSICAL RECORD: n_fields 5 Compact format; info bits 00: len 8; hex 80071afd5112d89a; asc Q; 1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;; 2: len 1; hex 81; asc; 3: len 12; hex 3130343130373333363737; asc 104103743677; 4: len 4; hex 95f12ab5; asc *

As you can see from the log, the sql statement executed by transaction 1 is:

Update `user_feed_ 26` set `notification` = 1, `mtime` = '2020-10-03 09vis11' where `notificationid` = 2000000126212250 and `action`in ('resblock_weekly',' bizcircle_weekly', 'district_weekly') and `notification` = 0

The waiting lock is:

* * (1) WAITING FOR THIS LOCK TO BE GRANTED: ECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_ feed`.`user _ feed_ 26` trx id 6286508066 lock_mode X locks gap before rec insert intention waiting

What is shown here is what lock the transaction is waiting for. RECORD LOCKS represents a record lock, and you can see that the index to be locked is idx_user_id,space id 2229 page no 263938 locklock mode X marks the record lock as an exclusive lock, and insert intention waiting indicates that the lock to be added is an insert intention lock and is in a lock waiting state.

Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 8; hex 80071afd512d89a; asc Q; 1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;; 2: len 1; hex 81; asc; 3: len 12; hex 3130343130333333363737; asc 104103743677; 4: len 4; hex 95f12ab5; asc *

Combined with the second line on_shelf_again of index information, we can know that the action field of this row lock is on_shelf_again.

Transaction 2 log:

* (2) TRANSACTION: TRANSACTION 6286508067, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4980 mysql tables in use 1, locked 1 12 lock struct (s), heap size 1136, 22 row lock (s), undo log entries 3 MySQL thread id 189619144, OS thread handle 140620050204416, query id 1148803197 10.200.17.37 pt_user updating UPDATE `user_feed_ 26`SET `notification` ='1', `mtime` = '2010-10-03 009 ACTIVE 11' WHERE `user_ id` =' 20000126212250' AND `action` in ('deal','price_changed' 'ting_shou','house_new_picture','house_new_vr','price_changed_rise','on_shelf_again') AND `notification` =' 0' * * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_ feed`.`user _ feed_ 26` trx id 6286508067 lock_mode X locks gap before rec Record lock, heap no 83 PHYSICAL RECORD: n_fields 5 Compact format; info bits 00: len 8; hex 80071afd512d89a; asc Q; 1: len 4; hex 6465616c; asc deal;; 2: len 1; hex 81; asc; 3: len 12; hex 313034313032363731333238; asc 104102671328; len 4; hex 95e14632; asc F2; Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 8; hex 80071afd5112d89a; asc Q; 1; len 14f6e5f768656c665f61676e; asc on_shelf_again 2: len 1; hex 81; asc; 3: len 12; hex 313034313033333336373737; asc 104103743677; 4: len 4; hex 95f12ab5; asc *; * * omitted. * * (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_ feed`.`user _ feed_ 26` trx id 6286508067 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 87 PHYSICAL RECORD: n_fields 5; compact format; info bits 320: len 8; hex 80071afd512d89a; asc Q;; 1: len 15; hex 646973747263745f77656b6c79; asc district_weekly;; 2: len 1; hex 80; asc; 3: len 8 Hex 3233303038373831; asc 23008781; 4: len 4; hex 95f63035; asc 05

The log of transaction two has more information about holding locks than transaction one:

* * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_ feed`.`user _ feed_ 26` trx id 6286508067 lock_mode X locks gap before rec Record lock, heap no 83 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 8; hex 80071afd512d89a; asc Q;; 1: len 4; hex 6465616c; asc deal;; 2: len 1; hex 81; asc; 3: len 12; hex 313034313033333731333238; asc 104102671328 4: len 4; hex 95e14632; asc F2; Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 8; hex 80071afd5112d89a; asc Q; 1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;; 2: len 1; hex 81; asc; 3: len 12; hex 31303431303333363737; asc 104103743677; 4: len 4; hex 95f12ab5; asc *; * * omitted.

From the log, transaction 2 holds a record lock, RECORD LOCKS, which is a record lock, and space id is 2229. Page no is 263938. As can be seen from the index information, transaction 2 holds exactly the row record lock needed by transaction 1, that is:

Record lock, heap no 93 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 8; hex 80071afd512d89a; asc Q; 1: len 14; hex 6f6e5f7368656c665f616761696e; asc on_shelf_again;; 2: len 1; hex 81; asc; 3: len 12; hex 3130343130333333363737; asc 104103743677; 4: len 4; hex 95f12ab5; asc * Lock_mode X locks gap before rec indicates that this is an exclusive lock and a gap lock * * (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2229 page no 263938 n bits 264 index idx_user_id of table `lianjia_user_ feed`.`user _ feed_ 26` trx id 6286508067 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 87 PHYSICAL RECORD: n_fields 5; compact format; info bits 320: len 8; hex 80071afd5112d89a; asc Q;; 1: len 15 Hex 64697374726963745f77656b6c79; asc district_weekly;; 2: len 1; hex 80; asc; 3: len 8; hex 3233303038373831; asc 23008781; 4: len 4; hex 95f63035; asc 05

Again, what is shown here is what lock transaction two is waiting for. RECORD LOCKS represents a record lock, and you can see that the index to be locked is idx_user_id,space id, page no is 2229, page no is 263938 lock_mode X indicates that the record lock is an exclusive lock, and insert intention waiting indicates that the lock to be added is an insert intention lock and is in a lock waiting state. Although the log of transaction one does not indicate which locks it holds, combined with the district_weekly field in the lock structure of transaction two waiting, transaction one holds the lock. Therefore, the two transactions form a scenario of waiting for each other for lock release, thus forming a deadlock.

So here comes the question, two sql:

# sql1: update `user_feed_ 26` set `notification` = 1, `mtime` = '2020-10-03 09V 11V 11' where `user_ id` = 2000000126212250 and `action`in (' resblock_weekly', 'bizcircle_weekly',' district_weekly') and `notification` = 0 # sql2: UPDATE `user_feed_ 26`notification` ='1', `mtime` = '2020-10-03 09Fr1111' WHERE `user_ id` = '20000126212250' AND `action` in 'house_new_picture','house_new_vr','price_changed_rise','on_shelf_again') AND `notification` =' 0'

It is clear that the where conditions of the two statements are different and do not intersect, so why do they occupy each other's locks?

The recovery of mountains and rivers

In order to verify this case, we try to reproduce it offline. The table structure is as follows:

# CREATE TABLE `user_feed_ 26` (`user_ id` int (10) NOT NULL AUTO_INCREMENT, `user_ id` bigint (20) NOT NULL, … PRIMARY KEY (`user_ id`), KEY `idx_user_ id` (`user_ id`, `action`, `notification`, `feed_ target`), … ) ENGINE=InnoDB AUTO_INCREMENT=371826027 DEFAULT CHARSET=utf8 COMMENT=' user push table'

But in any case, it is a lock waiting, not a deadlock. What's going on?

With skepticism, we looked at the execution plan of the statement:

Through the execution plan, we found that there is not the idx_user_id index that appears in the deadlock log, but the primary key index, so there is no deadlock.

Bold guess: because the amount of simulated data is too small, it does not take the composite index.

So we poured about 100w of random data into the offline simulation library and looked at the execution plan again:

Sure enough, when the amount of data becomes larger, it will go to the corresponding composite index. After another attempt, the online deadlock scene was reproduced, but the question is, why did this happen?

Dense willow trees and bright flowers

In order to understand the real principle behind it, we re-read the information about MySQL locks and learned the truth of the matter.

First of all, let's briefly talk about the basic principles of MySQL locking:

Principle 1: the basic unit of locking is next-key lock. Next-key lock is a front open and back closed interval.

Principle 2: only objects accessed during the lookup process will be locked.

Optimization 1: when an equivalent query on a unique index is locked, the next-key lock is reduced to a row lock.

Optimization 2: when the equivalent query on the non-unique index is locked, the interval of the value in the where condition is traversed to the right (back), and when the right boundary of the interval does not satisfy the equivalent condition, the next-key lock is reduced to a gap lock. This is difficult to understand, for example:

If there is a non-unique index on the column an of the table ta: index_a, the value that exists in the index is as follows: 1 next-key lock 1, 3, 3, 3, 7, 7, 9: when you execute select a from ta where ta.a=5, you will traverse from 3 to the right (back), corresponding to (3) 7], but because the last value 7 of the interval does not meet the condition of = 5, the next-key lock will be reduced to gap lock (3).

Thus, when we execute the update statement, when querying, we add a gap lock to the corresponding index idx_user_id, resulting in a deadlock with each other. Let me give you a simple example:

Transaction 2 executes a update with a where condition of 3, so it acquires the Gap lock of (1)

Transaction 1 also executes a update,where condition of 5, so it gets a (5meme + ∞) while waiting for (1mem7) to insert an intention lock.

Transaction 2 executes another update,where condition of 8, then he will wait (5meme + ∞).

As a result, a deadlock is created.

So how to prevent this kind of deadlock from happening again?

Update through a unique index (usually the primary key is), first find out the unique index of the eligible record through the select statement, and then update it with the unique index.

Select id from table where axioms? And baked goods; update table set column=xxx where idid= id

Avoid running multiple scripts that read and write the same table at the same time, and pay special attention to locked statements that operate on a large amount of data; we often have some scheduled scripts to prevent them from running at the same time. As shown in this event, Gap locks are often the real culprits in programs that cause deadlocks. Since the isolation level of MySQL is RR by default, if you can determine that phantom and non-repeatable reads have little impact on the application, you can consider changing the isolation level to RC to avoid deadlocks caused by Gap locks.

This is the end of "how to troubleshoot MySQL deadlock warnings". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.