Hi MySQL, Deadlock

Preface

In everyday life, I believe you have experienced this kind of experience more or less: "Every rush hour, the originally wide traffic arteries become blocked for a while, the drivers and passengers are restless, the trumpet sounds loud and when the car is stuck in the middle of the intersection, it is embarrassing to find it, no matter what you think.Wherever you go...... ".For such an experience, everyone is very afraid of contact and experience, and the transportation departments are constantly working to solve traffic congestion problems.

In fact, the congestion in the above life case is similar to a high concurrency scenario;

A traffic jam in all directions in the middle of an intersection is similar to a database deadlock scenario.

This chapter focuses on some concepts related to the InnoDB storage engine deadlock, the causes of the deadlock, deadlock scenarios, and deadlock handling strategies.

Related concepts

To better understand deadlocks, let's first learn some basic concepts related to deadlocks in MySQL.

concurrency control

Concurrency control refers to various technologies used to protect the integrity of a database when multiple users update the runtime simultaneously.

Read-write lock

To ensure concurrency control of the database, MySQL has two locks:

  • Shared Lock: Also known as Read Lock, allows multiple connections to read the same resource concurrently at the same time without interrupting each other
  • Exclusive Lock: Also known as Write Lock, which blocks requests for other write locks or books and guarantees that only one connection at a time can manipulate data, including reading

Lock Policy

A lock strategy is a balance between the cost of locking and the security of the data, which can affect performance.Currently, the InnoDB storage engine has two lock policies:

  • Table Lock policy: the most basic lock policy, with the least overhead, fast locking and no deadlock, but with high probability of lock conflicts, large granularity and low concurrency
  • Row Lock policy: smallest granularity, low lock conflict attitude and high concurrency, but expensive, slow lock, deadlock

affair

A transaction is a sequence of operations that either execute or not. It is an inseparable unit of work.A transaction needs to pass rigorous ACID tests:

  • Atomicity (ATOMICITY): The entire operation of a transaction either commits successfully or fails to roll back and cannot perform a part of it
  • CONSISTENCY: A database always transitions from one consistent state to another consistent state
  • Isolation: Modifications made by one thing that are not visible to other transactions until they are committed
  • DuRABILITY: Once a transaction commits, its modifications are persisted in the database

Isolation Level

The SQL Standard establishes four isolation levels that specify whether modifications to transactions are visible to other transactions

  • READ UNCOMMITED: Uncommitted or visible, also known as dirty read
  • READ COMMITED (Committed Read): This is the default isolation level for most DBMS s and is not, or is not, repeatable read by MySQL
  • REPEATABLE READ (REPEATABLE READ). Repeated reading results are consistent. MySQL defaults to this level to solve dirty reading problems, but there is a magic reading problem (when one transaction reads a record, another transaction inserts a new record, and the original transaction reads the record with a magic line).
  • SERIALIZABLE (serializable), the highest isolation level, enforces serial transaction execution, avoids the previously mentioned magic read problem, and has poor concurrency performance
Isolation Level Dirty reading possibilities Non-repeatable reading possibility Illusional reading possibilities Locked Read
READ UNCOMMITED Yes Yes Yes No
READ COMMITED No Yes Yes No
REPEATABLE READ No No Yes No
SERIALIZABLE No No No Yes

Definition of deadlock

Deadlock refers to the phenomenon that two or more transactions occupy the same resource and request to lock the other party's occupied resource (I wait for your resource, but you wait for my resource, we all wait for each other, and no one releases his own occupied resource). This leads to a vicious cycle:

  • Deadlocks can occur when multiple transactions attempt to lock resources in different order
  • Deadlock also occurs when multiple transactions lock the same resource at the same time

Deadlock hazard

Deadlock and deadlock are not the same thing. If you encounter a deadlock, you can rest assured that it is not. If a deadlock occurs, you can rest assured that it will never die.

This is because MySQL has a set of deadlock detection mechanisms that roll back one transaction and allow another to execute once a deadlock occurs.The error message for this deadlock rollback is also sent to the client.Deadlocks occur from time to time even in normal business, so don't be afraid to encounter them, because they are also a protection for data security. However, if deadlocks are too frequent, they can cause many problems:

  1. The process is not getting the right results: a deadlocked process cannot get the resources it needs, cannot move forward, and cannot get results
  2. Decrease resource utilization: Deadlocked processes do not release occupied resources so that they cannot be utilized by other processes, thereby reducing system resource utilization
  3. Causes a new deadlock: Deadlocks also occur because other processes cannot move forward without requesting the resources already occupied by the deadlocked process

Reason for deadlock

Deadlock has four necessary conditions:

  1. Mutually exclusive: A resource can only be used by one process at a time
  2. Keep exclusive resources and make new resource requests: When a process is blocked by requesting resources, keep the resources it has already acquired
  3. Indeprivable: Resources cannot be preempted, that is, resources can only be automatically released after the process has completed its task
  4. Loops: There is a set of waiting processes P 0, P 1, P 2. The resources that P 0 waits for are occupied by P 1, the resources that P 1 waits for are occupied by P 2, and the resources that P 2 waits for are occupied by P 0, forming a waiting cycle

Deadlock scenario

All scenarios below are based on the InnoDB storage engine and have REPEATABLE-READ isolation level (repeatable read)

Query the current isolation level:

select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+

Modify isolation level:

set global transaction isolation level read committed; ## Global

set session transaction isolation level read committed; ## Current session

Create Data Table

CREATE TABLE `deadlock` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uniq_stu_num` (`stu_num`),
  KEY `idx_score` (`score`)
) ENGINE=InnoDB;

insert into deadlock(id, stu_num, score) values (1, 11, 111);
insert into deadlock(id, stu_num, score) values (2, 22, 222);
insert into deadlock(id, stu_num, score) values (3, 33, 333);

id primary key index

stu_num is the unique index

score normal index

To simulate a real-world scenario, the following two commands need to be executed in each session:

set autocommit=0; ## Turn off automatic submission

START TRANSACTION; ## Start Transaction

Scenario 1: AB BA

# session A
select * from deadlock where id = 1 for update; 

# session B
select * from deadlock where id = 2 for update; 

# session A
select * from deadlock where id = 2 for update;
## Because session2 has assigned a write lock to id=2

# session B
select * from deadlock where id = 1 for update;
## 1213 - Deadlock found when trying to get lock; try restarting transaction

Scenario 2: S-lock upgraded to X-lock in the same transaction

# session A
SELECT * FROM deadlock WHERE id = 1 LOCK IN SHARE MODE;   
## Get S-Lock

# session B
DELETE FROM deadlock WHERE id = 1;   
## To get X-Lock, but stuck by S-lock of session A, currently in waiting lock phase

# session A
DELETE FROM deadlock WHERE id = 1;   
## Error : Deadlock found when trying to get lock; try restarting transaction
## To get X-Lock, sessionA itself has S-Lock
## But because sessionB Apply X-Lock Again##
## Therefore sessionA cannot be promoted from S-lock to X-lock
## Deadlock caused by waiting for sessionB to release before it can be acquired

Scenario 3: Deadlock for primary key and secondary index

CREATE TABLE `deadlock_A` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_score` (`score`),
  KEY `idx_stu_num` (`stu_num`) USING BTREE
) ENGINE=InnoDB;

# deadlock_A data
# select * from deadlock_A
| id   | stu_num | score |
| ---- | ------- | ----- |
| 1    | 11      | 111   |
| 2    | 33      | 222   |
| 3    | 22      | 333   |
| 4    | 44      | 444   |
# session A
delete from deadlock_A where stu_num > 11;
## Order of lock secondary index (stu_num): 22->33->44 Order of lock primary key (id) index: 3->2->4

# session B
delete from deadlock_A where score > 111;
## Order of lock secondary index (score): 222->333->444 Order of lock primary key (id) index: 2->3->4

## sessionA lock primary key 3, sessionB lock primary key 2
## sessionA lock primary key 2, sessionB lock primary key 3
## Deadlock Generation-"AB BA
## This may occur in a concurrent scenario.

Scenario 4: Gap Lock

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `v` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_v` (`v`) USING BTREE
) ENGINE=InnoDB;

# select * from t2
| id   | v       |
| ---- | ----- |
| 2    | 2     |
| 5    | 5     |
| 10   | 10    |

Gap lock case

# session A
delete from test where v=5;

# session B
insert into t2 (id,v) values (3,3);
## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

insert into t2 (id,v) values (9,9);
## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

insert into t2 (id,v) values (5,11);
## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

insert into t2 (id,v) values (1,1)
## Affected rows : 1, Time: 5.62sec

insert into t2(id,v) values (10, 10);
## Affected rows : 1, Time: 10.51sec

insert into t2 (id,v) values (9,11);
## Affected rows : 1, Time: 15.51sec

You can see that the lock is a record with id=5 & k=[3,10].

With an overview of the extent of gap locks in the above case, let's look at the deadlock scenario:

# session A
update t2 set v = 5 where v =5;
## Affected rows : 1, Time: 12.67sec

# session B
update t2 set v = 10 where v =10;
## Affected rows : 1, Time: 12.88sec

# session A
insert into t2 (id,v) values (7,7);
## waiting

# session B
insert into t2 (id,v) values (8,8);
## Error : Deadlock found when trying to get lock; try restarting transaction

Deadlock Handling Policy

Deadlock prevention

  1. Same order: Access tables and rows in a fixed order.For example, two transactions updating data, transaction A updates data in the order of 1->2; transaction B updates data in the order of 2->1.This is more likely to cause deadlocks
  • Keep transactions as short as possible: Large transactions tend to be deadlocked, and large transactions are broken down if the business allows them
  • One-time locks: In the same transaction, lock as many resources as possible at once, reducing deadlock probability
  • Lower isolation level: Lower isolation level is also a good option if the business allows, such as adjusting isolation level from RR to RC, to avoid many deadlocks caused by gap locks
  • Fine-grained locking (row lock): Adds a reasonable index to the table.You can see that if you don't leave the index, each row of records in the table will be locked, and the probability of deadlock will be greatly increased

deadlock detection

innodb_lock_wait_timeout Waiting for lock timeout Rollback Transaction:
An intuitive way is to roll back one of the two transactions when they are waiting for each other, and the other transaction can continue to execute when one wait time exceeds a set threshold.This method is simple and efficient, and in innodb, the parameter innodb_lock_wait_timeout is used to set the timeout time.

wait-for graph algorithm for active deadlock detection:
innodb also provides a wait-for-graph algorithm to actively detect deadlocks, which is triggered whenever a lock request cannot immediately satisfy a need and enter a wait.

Reference Article

High Performance MySQL 3rd Edition

http://hedengcheng.com/?p=771...

https://www.kancloud.cn/hangh...

https://blog.csdn.net/dqjyong...

Tags: MySQL Session Database REST

Posted on Sun, 18 Aug 2019 17:08:35 -0700 by adamlacombe