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.

MySql > alter database lock

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 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

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
  • A gap lock is a 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 nonexistent record in the table? 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:

[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 situation is as follows:

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 text description

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:

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);

  • The gap lock is (10, 30)
  • Next key lock will add a row lock to the right, that is, uid=30 and X lock
  • If uid=30 and uid=28 and uid= 29, lock will not block.

Note: if a range is locked by x, records that are not in the range may be locked

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 MODE: S LOCK
  • Update/delete: X 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

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

Description:

  • 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

  • A gap lock will only conflict with an insert intent lock
  • X row locks conflict with row locks
  • Next key lock next key lock next key lock next key lock X lock conflict for row lock

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); -- block -- 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 an X + Next Lock Lock
  • Session2/3: insert (id=2), insert intent Lock blocked, hold S + Next Lock instead

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); -- block -- 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

Third, summary

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.