A concurrent insert deadlock brought by MySql lock

MySql locks caused by a concurrent insert deadlock

Recently encountered a unique index, resulting in concurrent insert deadlock scene, in the analysis of the cause of deadlock, found this part is quite interesting, involving a lot of knowledge in MySql, hereby summarize and record

I. Common MySql locks

SQL > alter table lock (); SQL > alter table lock (); SQL > alter table lock (); SQL > alter table lock ()

0. Lock classification

The most commonly said lock can be divided into shared lock (S) and exclusive lock (X). In mysql innoDB engine, gap lock and next key lock are introduced to solve the illusory problem. In addition, there is an intent lock, such as inserting an intent lock

This article focuses on the following types of locks

• Record lock: Note that this is a lock on an index (so if there is no index, eventually the entire table will be locked)
• Shared Lock (S Lock): Also called read Lock, shared locks do not block each other (as the name implies)
• X Lock: Also called write Lock, exclusive Lock can only have one session at a time. hold
• Gap lock: For gaps between indexes
• Next-key lock: can be simply understood as a row lock + gap lock

Although the basic definitions of several locks have been introduced above, when is a row lock, how to obtain a shared lock, and in what scenarios are exclusive locks produced? How does gap Lock/Next Key Lock solve illusion?

All of the following are explained based on the MYSql5.7.22 InnoDB engine, rr isolation level

1 Shared locks and exclusive locks

The following table describes whether locks will be used in our actual SQL, and what locks will be generated

Shared locks are distinguished from exclusive locks

sql	The sample	instructions
select ... where	select * from table limit 1	Mvcc-based, snapshot read, unlocked
select ... for update	select * from table where id=1 for update	Exclusive lock
select ... lock in share mode	select * from table where id=1 lock in share mode	A Shared lock
update ... where	update table set xx=xx where id=1	Exclusive lock
delete ... where	delete table where id=1	Exclusive lock

2. Differentiate row locks, table locks, gap locks, and next-key locks

The difference between these several is mainly to see the effect of our final lock, such as

• Without an index, adding an S/X lock will end up locking the entire table. Because locks are for indexes)
• Records determined by primary/unique key locking: row locking
• Normal index or range query: Gap lock/Next key lock

The biggest differences between row and gap locks are:

• Row locks are for identified records
• Gap locking is the range between two defined records; The next Key Lock includes a certain record in addition to the gap

3. Example demonstration

Look at the above two instructions, naturally want to analyze in the actual case, different SQL will produce what kind of lock effect

• Lock X on a certain record in a table.
• What if an X lock is applied to multiple identified records in the table?
• Does an X lock occur on a table that does not exist? If it’s gap lock, what’s the interval?
• For the range plus X lock, gap lock range generated how to determine?

Before analyzing the above cases, it is very, very important to remember that locks are indexed

Second, we need to test different indexes separately (i.e. unique and normal indexes).

Table 3.1 to prepare

Next, we design our test cases for the above four scenarios. First, we prepare three tables

• None Index TN
• Unique index table TU
• Normal index table TI

The corresponding table structure and initialization data are as follows

CREATE TABLE `tn` (
  `id` int(11) unsigned NOT NULL,
  `uid` int(11) unsigned NOT NULL
) ENGINE=InnoDB;

CREATE TABLE `tu` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL.PRIMARY KEY (`id`),
  UNIQUE KEY `u_uid` (`uid`)
) ENGINE=InnoDB;

CREATE TABLE `ti` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL.PRIMARY KEY (`id`),
  KEY `u_uid` (`uid`)
) ENGINE=InnoDB;


INSERT INTO `tn` (`id`, `uid`) VALUES (1.10), (5.20), (10.30);
INSERT INTO `tu` (`id`, `uid`) VALUES (1.10), (5.20), (10.30);
INSERT INTO `ti` (`id`, `uid`) VALUES (1.10), (5.20), (10.30);
Copy the code

3.2 Exact Matching

That is, when our SQL can accurately hit a record, the lock situation is as follows

The instance	TN	TU	TI
select * from tx where uid=20 for update	A full table lock	Row locking uid = 20	Uid =20, uid=[10, 30]

[10, 30] [gap] [10, 30] [gap] [10, 30] [gap]

So let’s verify that

The basic process is as follows

As can be seen from the above test, adding x lock under normal index will actually add a gap lock, and the gap interval is the previous record (including it), to the next record

For example, if uid = 20, the two records are (1, 10), (10, 30).

• Gap Lock: range is [10, 30]
• Insert uid=[10,30]
• Note that there is a gap lock on uid=10 that cannot insert records, but there is no problem with adding X locks.

3.3 Exact Query Is Not Matched

When the record of our lock does not exist, the lock condition is as follows

The instance	TN	TU	TI
select * from tx where uid=25 for update	A full table lock	Gap lock uid = (20, 30)	gap lock uid=(20, 30)

The measured case is as follows (TN omitted, there is no test necessity for locking the whole table)

The basic process is not drawn, the above picture has been described in words

Select * from uid=(20, 30); select * from uid=(20, 30)

Unique indexes behave like normal indexes and block the insert intent lock for inserts.

3.4 Range Query

When we lock an interval, the locking situation is as follows

The instance	TN	TU	TI
select * from tx where uid>15 and uid<25 for update	A full table lock	The next key lock uid = (10, 30]	next key lock uid=(10, 30]

In simple terms, when querying a range, add a Next key lock to find the leftmost and rightmost record ranges based on our query criteria

Select * from (1, 10), (10, 30);

• Gap for the lock(10, 30)
• next key lockThe row lock is added to the right, i.euid=30Add X lock
• So foruid=30(uid=28,29 (x))

Note: range plus X lock, may lock no longer this range of records, accidentally may cause deadlock oh

3.5 summary

At the RR isolation level, we generally consider the following statements to generate locks:

• SELECT ... FOR UPDATE: X lock
• SELECT ... LOCK IN SHARE MODELock: S
• update/delete: X lock

The index	scenario	The lock range
There is no index	S/X lock	A full table lock
The only index	An exact match and a hit	Row locks
The only index	It’s a perfect match. No hit	gap lock
The only index	Range queries	Next key lock Next record row lock

| | ordinary index an exact match, and a line | + gap lock lock (on a record and the next interval, left closed right away, the left records adb lock) | | common index an exact match, Not hit the lock | | gap | | common index range queries | next key lock |

4. Lock conflicts

Insert (X/S) LOCK (X/S) LOCK (X/S) LOCK (X/S) LOCK (X/S)

4.1 Inserting an intent lock

Inserting an intent lock is actually a special gap lock, but it does not block other locks. Assuming that there are index records with values 4 and 7, two transactions with values 5 and 6 attempt to insert a gap lock before acquiring the exclusive lock on the inserted row, that is, add a gap lock on (4, 7), but these two transactions do not conflict with each other and wait; But if the interval has a gap lock, it will be blocked; If multiple transactions insert the same data resulting in a unique conflict, a read lock is placed on duplicate index records

In simple terms, its properties are:

• It does not block any other locks;
• It itself will only be blocked by a gap lock

The second important point is:

• Insert statements usually add row locks, exclusive locks
• Insert intent lock (only blocked by gap lock)
• When a unique conflict is inserted, a read lock is added to the duplicate index
  • Here’s why:
  • Transaction 1 May eventually be rolled back, so other repeat insert transactions should not fail directly. In this case, they should apply for read locks instead.

4.2 Lock Conflict matrix

Simplified matrix

	Shared lock (S)	Exclusive lock (X)
Shared lock (S)	Compatible with	conflict
Exclusive lock (X)	conflict	conflict

When we add gap lock, next key lock, and Insert Intention lock to the matrix, it becomes more complicated

Line: to be locked; Column: Locks exist	S(not gap)	S(gap)	S(next key)	X(not gap)	X(gap)	X(next key)	Insert Intention
S(not gap)	–	–	–	conflict	–	conflict	–
S(gap)	–	–	–	–	–	–	conflict
S(next-key)	–	–	–	conflict	–	conflict	conflict
X(not gap)	conflict	–	conflict	conflict	–	conflict	–
X(gap)	–	–	–	–	–	–	conflict
X(next-key)	conflict	–	conflict	conflict	–	conflict	conflict
Insert Intention	–	conflict	conflict	–	conflict	conflict	–

instructions

• The not gap: row locks
• gap: gap lock
• Next-key: gap + row lock

summary

For the above matrix, understand the following principles to derive the above matrix

• gap lockIt will only conflict with the insert intent lock
• X row locks conflict with row locks
• next key lock: Row lock + gap lock
  • Lock interval, insert conflict;
  • X lock conflict for row lock

II. Concurrent insert deadlock analysis

Above the basics, let’s look at a case that actually causes a deadlock

• Simultaneous insertion of the same record causes a deadlock

0. Table

Create a simple, basic table for the demonstration

CREATE TABLE `t` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `t` (`id`) VALUES (1);
Copy the code

1. Deadlock problem of transaction rollback

Scene replay:

step1:

-- session1: 
begin; insert into t values (2);

-- session2:
begin; insert into t values (2);
Blocked -

-- session3:
begin; insert into t values (2);
Blocked -
Copy the code

step2:

-- session1:
rollback;
Copy the code

Cause analysis:

Deadlock log view

SHOW ENGINE INNODB STATUS;
Copy the code

• step1:
  • Session1: insert (id=2), add oneX + Next LockThe lock
  • Session2/3: Insert (id=2), insert intent lock blocked, changed to holdS + Next LockThe lock
• step2:
  • Session1: Rollback, release X lock
  • Session2/3: compete X lock, only the other party releases S lock, can compete successfully; Waiting for each other leads to deadlock

2. Delete causes deadlock problems

Basically the same as before, except that the first session deletes the record

step1:

-- session1: 
begin; delete from t where id=1;

-- session2:
begin; insert into t values (1);
Blocked -

-- session3:
begin; insert into t values (1);
Blocked -
Copy the code

step2:

-- session1:
commit;
Copy the code

Cause analysis is basically consistent with the previous

Insert lock logic

Lock logic for unique indexes in insert

1. If there is a target row, add S Next Key Lock to the target row. (This record will be deleted by other transactions during the waiting period, and this Lock will be deleted at the same time.)
2. If 1 succeeds, insert the intent lock on the corresponding row with X +
3. If 2 succeeds, insert record and add X + row lock to record (possibly implicit lock)

By the logic of the above, then there is an interesting deadlock scenario

step1:

-- session1
begin; delete from t where id = 1;


-- session2
begin; delete from t where id = 1;
Copy the code

step2:

-- session1
insert into t values(1)
Copy the code

The corresponding deadlock log

For details on this scenario, please refer to this blog post: Record a Mysql deadlock check

4. How to avoid deadlocks?

• Break big things down into small things
• Add proper indexes to avoid locking each row and reduce the probability of deadlocks
• Avoid circular waits on business (such as adding distributed locks)
• Lowering transaction isolation level (e.g. RR -> RC is certainly not recommended)
• Using replace/ ON Duplicate for concurrent inserts can also avoid deadlocks

II. The other

1. A gray Blog:liuyueyi.github.io/hexblog

A gray personal blog, recording all the study and work in the blog, welcome everyone to go to stroll

Related blog

• Mysql:innodb-next-key-locks
• [MySQL][Gap Lock][Next-key Lock
• Mysql record lock (record lock), gap lock (gap lock),Next-key lock (next-key lock) test
• Record the process of checking Mysql deadlocks
• Failure analysis | MySQL Insert lock and deadlock analysis
• MySQL deadlock log view and analysis

2. Statement

As far as the letter is not as good, the above content is purely one’s opinion, due to the limited personal ability, it is inevitable that there are omissions and mistakes, if you find bugs or have better suggestions, welcome criticism and correction, don’t hesitate to appreciate

• QQ: a gray /3302797840
• Wechat official account: One Grey Blog