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

A preface

Deadlock, in fact, is a very interesting and challenging technical problem, probably every DBA and some developers will encounter in the course of work. I will continue to write a series of case studies on deadlocks, hoping to be helpful to friends who want to know about deadlocks.

Case study 2.1 Environmental description

MySQL 5.6.24 transaction isolation level is RR

Create table tx (id int not null primary key auto_increment, c1 int not null default 0, c2 int not null default 0, key idx_c1 (C1)) engine=innodb; insert into tx values (24 int not null default 3, 4), (25, 3, 4), (26, 3, 4), (30, 5, 8); 2.2 test cases

Sess1

Sess2

Begin

Begin

T1

Select * from tx where id=30 for update

T2

Update tx set c2 # 8 where c1 # 5

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

T3

Delete from tx where id=30

2.3deadlock log-LATEST DETECTED DEADLOCK----2018-03-27 15:40:40 0x7f75cafce700log * (1) TRANSACTION:TRANSACTION 1850, ACTIVE 20 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct (s), heap size 1136, 2 row lock (s) MySQL thread id 379040, OS thread handle 140143994337024 Query id 1521958 localhost root updatingupdate tx set c2 "8 where C1" 5 bits * (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table `test`.`tx` trx id 1850 lock_mode X locks rec but not gap waiting*** (2) TRANSACTION:TRANSACTION 1849, ACTIVE 32 sec updating or deleting, thread declared inside InnoDB 4999mysql tables in use 1, locked 13 lock struct (s), heap size 1136, 2 row lock (s), undo log entries 1MySQL thread id 379016, OS thread handle 140143893473024 Query id 1521976 localhost root updatingdelete from tx where id=30*** (2) HOLDS THE LOCK (S): RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table `test`.`tx` trx id 1849 lock_mode X locks rec but not gap*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 27 page no 5 n bits 72 index idx_c1 of table `test`.`tx` trx id 1849 lock_mode X locks rec but not gap waiting*** WE ROLL BACK TRANSACTION (1) 2.4 analyze deadlock logs

The first thing to understand is that you need to queue up to apply for a lock on the same field.

Secondly, the index idx_c1 in the table ty is a non-unique ordinary index, which is explained according to the time order in which the transaction is executed, which is easier to understand.

T1: sess2 performs a select for update operation to hold the primary key row lock of the record id=30: PRIMARY of table test.tx lock_mode X locks rec but not gap

T2: the sess1 statement update updates c2 through the normal index idx_c1, first acquires the X lock lock _ mode X locks rec but not gap of idx_c1 c1lock5, and then applies for the row lock of the corresponding primary key id=30, but the sess2 already holds the row lock of the primary key, so sess1 waits.

T3: sess2 performs the deletion of records according to the primary key id=30, and you need to apply for a row lock for id=30 and an index row lock for C1. But sess1 and hold the lock, so there will be index idx_c1 of table test.tx trx id 1849 lock_mode X locks rec but not gap waiting

Sess2 (delete) waits for sess1 (update), sess1 (update) waits for sess2 (select for update) loop to wait, resulting in deadlock.

The root cause of deadlock in RDBMS system can be summarized as follows: different transactions add locks in different order, which leads to loop waiting, which leads to deadlock.

2.5 solution

Modify the update of sess1 to update according to the primary key, that is, update tx set c2roomx where id=30, change the locking mode to sequential locking, apply for the lock of the primary key id, and avoid applying for locks held by each other through cross-locking.

Three summaries

The deadlock in the above case occurs because different sessions compete with each other for the normal index idx_c1 and the primary key, resulting in a loop waiting. When you encounter high concurrency updating the same row during production, you can consider avoiding updates through different indexes, thus avoiding deadlocks.

Recommended reading

How to read deadlock logs

Talking about deadlock

One of the deadlock cases

Deadlock case 2

Deadlock case 3

Deadlock case 4

Deadlock case 5

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.