InnoDB lock set by different SQL statements and unique index on line cause deadlock check

Lock Read, update, or delete typically sets a record lock on each index record scanned while processing SQL statements. It does not matter if any rows are excluded that do not meet the WHERE condition in the statement. InnoDB does not remember the exact WHERE conditions, but only which index ranges were scanned. These locks are usually next-key locks that also prevent insertions before the record (gap), that is, do not allow insertions in the front gap of the record. However, gap locking can be shown to be disabled, which will result in next-key locking not being used. InnoDB Locking can be seen in detail in my other article juejin.cn/post/684490… The transaction isolation level can also affect the locks that are set. For more details, see my other article juejin.cn/post/684490… .

If a secondary index is used in a search and the index Record Lock is an X Record Lock, InnoDB retrieves the corresponding clustered index Record (primary key Record) and sets the Lock on it.

If there is no index that fits the statement (equivalent to a field that is not indexed in an SQL statement) and MySQL must scan the entire table to process the statement, each row of the table will be locked, preventing all inserts to the table by other users. It is important to create good indexes so that the query does not scan many rows unnecessarily.

InnoDB sets specific types of locks as follows.

SELECT … FROM is read consistently, a snapshot of the database is read and locks are not set unless the transaction isolation level is set to SERIALIZABLE. At the SERIALIZABLE level, a search sets a shared next-key lock (s Next-key lock) on index records encountered. However, for statements that use a unique index lock to search a unique row, only an index record lock is required (a unique index equivalent is retrieved without phantom reading and can be degraded directly from a next-key lock to a record lock).
SELECT … FOR UPDATE and SELECT… FOR SHARE FOR statements that use a unique index, acquire locks FOR scanned rows and release locks FOR rows that do not meet the inclusion criteria in the result set (FOR example, if they do not meet the criteria given in the WHERE clause). However, in some cases, rows may not be immediately unlocked because the relationship between the result row and its original source is lost during query execution. For example, in a UNION UNION query, scanned (and locked) rows in a table might be inserted into a temporary table before evaluating whether they fit the result set. In this case, the relationship between the rows in the temporary table and the rows in the original table is lost, and the latter row is not unlocked until the query execution is complete.
FOR lock reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE and DELETE statements, the locking applied depends on whether the statement uses a unique index with a unique search condition or a scope-type search condition.

For unique indexes with unique search criteria, InnoDB locks only the index records found, not the gaps in front of them.
For other search criteria and non-unique indexes, InnoDB locks the index range of the scan, using gap locks or next key locks to prevent other sessions from being inserted into the gap in the range coverage.

For index records encountered by the search, SELECT… FOR UPDATE prevents other sessions from performing SELECT… FOR SHARE or read from some transaction isolation level. Consistent read ignores any locks that are set on records that exist in the read view.
UPDATE … WHERE … Set an exclusive next-key lock (at the RR level) on each record encountered by the search. However, for searching a unique row using a unique index, only the index record lock is required.
Update When a clustered index (primary key index) record is modified, the affected secondary index record is implicitly locked. The update operation also uses a shared lock on the secondary index record, affecting the repeat check scan before the new secondary index record is inserted and when the new secondary index record is inserted.
DELETE FROM … WHERE … Set an exclusive next-key lock (at the RR level) on each record encountered by the search. However, for searching a unique row using a unique index, only the index record lock is required. And the UPDATE… WHERE … The same.
INSERT sets an exclusive lock on the inserted row. This lock is an index record lock, not a next-key lock (i.e., no gap lock), and does not prevent other sessions from inserting new data in the gap before the row is inserted. Before inserting rows, a gap lock called an insert intention gap lock is set (see my other article on juejin.cn/post/684490…). . This lock represents the intent to insert so that if multiple transactions are inserted into the same location in the same index gap, they do not have to wait for each other. Assume that there are index records with values 4 and 7. Individual transactions attempting to insert values of 5 and 6 lock the gap between 4 and 7 using an insert intent lock before acquiring an exclusive lock on the inserted row, and do not block each other because rows are non-conflicting. If a duplicate build error occurs, a shared lock is set on the duplicate index record. Using a shared lock can cause a deadlock if multiple sessions attempt to insert the same row (if another session already has an exclusive lock). This can happen if another session deletes rows. Assume that InnoDB table T1 has the following structure:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;    Copy the code

Now assume that three sessions perform the following operations in order:
Session 1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);    
Copy the code

Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);Copy the code

Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1); Copy the code

Session 1:

```
ROLLBACK;    
Copy the code
```

The first operation of session 1 acquires the exclusive lock for the row. Sessions 2 and 3 both result in duplicate-key errors, and they both request a shared lock for rows. When session 1 rolls back, it releases the exclusive lock on the row and grants queued shared lock requests for sessions 2 and 3. At this point, sessions 2 and 3 are deadlocked: neither session can acquire the exclusive lock on the row because the other session holds the shared lock.
A similar situation occurs if the table already contains rows with a key value of 1 and the three sessions perform the following operations in order:
Session 1:

START TRANSACTION;
DELETE FROM t1 WHERE i = 1; Copy the code

Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1); Copy the code

Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1); Copy the code

Session 1:

```
COMMIT; Copy the code
```

The first operation of session 1 acquires the exclusive lock for the row. Sessions 2 and 3 both result in duplicate-key errors, and they both request a shared lock for rows. When session 1 commits, it releases the exclusive lock on the row and grants queued shared lock requests for sessions 2 and 3. At this point, sessions 2 and 3 are deadlocked: neither session can acquire the exclusive lock on the row because the other session holds the shared lock.
The first operation of session 1 acquires the exclusive lock for the row. Sessions 2 and 3 both result in duplicate-key errors, and sessions 2 and 3 both request a shared lock for rows. When session 1 commits, it releases the exclusive lock on the row and grants queued shared lock requests for sessions 2 and 3. At this point, sessions 2 and 3 are deadlocked: neither session can acquire the exclusive lock on the row because the other session holds the shared lock.

INSERT … ON DUPLICATE KEY UPDATE differs from simple insert in that when a duplicate-key error occurs, an exclusive lock instead of a shared lock is placed ON the row to be updated. Use an exclusive index record lock for duplicate primary key values. Use an exclusive next-key lock for duplicate unique key values.
If there are no conflicts on the unique key, the REPLACE operation is similar to the INSERT operation. Otherwise, an exclusive next-key lock is placed on the row to be replaced.
INSERT INTO T SELECT … FROM S WHERE … Set an exclusive index record lock (without a gap lock) on each row that inserts T. If the transaction isolation level is READ COMMITTED, InnoDB searches for S as a consistent READ (without locking). Otherwise, InnoDB sets a shared next keylock on the ROWS of S. InnoDB must set the lock in the latter case: each SQL statement must be executed in exactly the same way as originally during roll-forward recovery using statement-based binary logs. CREATE TABLE … SELECT … The selection is performed using the shared next-key lock, or as a consistent read. And INSERT… SELECT the same. When in the construct SELECT is replaced with REPLACE INTO t SELECT… FROM s WHERE … or UPDATE t … WHERE col IN (SELECT … FROM s …) InnoDB sets a shared next-key lock on rows of table S.
InnoDB initializes a previously specified AUTO_INCREMENT column on a table by setting an exclusive lock at the end of the index associated with the AUTO_INCREMENT column. In the case of innodb_AUTO INC_lock_mode=0, InnoDB uses a special auto-Inc table lock mode to acquire the lock and hold it to the end of the current SQL statement (rather than the end of the entire transaction) when accessing the auto-increment counter. When an Auto-INC table lock is held, other clients cannot insert the auto-INC table. The same behavior occurs for “bulk inserts” with Innodb_autoinc_lock_mode =1. Table level auto-inc locking is not used with innodb_autoINC_LOCK_mode =2. For more information. InnoDB retrieves the value of a previously initialized AUTO_INCREMENT column without setting any locks.
If a FOREIGN KEY constraint is defined on the table, any insert, update, or delete operation that needs to check the constraint sets a shared record level lock on the record checked for that constraint. InnoDB also sets these locks if constraints fail.
LOCK TABLES sets table locks, but those locks are set in the MySQL layer higher than InnoDB layer. If InnoDB_table_locks=1 (the default) and AUTOCOMMIT =0, InnoDB knows about table locks and the MySQL layer above InnoDB knows about row-level locks. Otherwise, InnoDB’s automatic deadlock detection cannot detect deadlocks involving such table locks. Also, because the higher MySQL layer is unaware of row-level locks in this case, the table lock can be acquired on the table that currently has row-level locks in another session. This does not compromise transaction integrity.
If innodb_TABLE_LOCKS =1 (the default), the lock table acquires two locks on each table. In addition to table locks on the MySQL layer, it also acquires InnoDB table locks. MySQL prior to mysql4.1.2 does not acquire InnoDB table locks; You can select the old behavior by setting InnoDB_table_locks=0. If InnoDB table lock is not obtained, the table lock will be completed even if some records of the table are locked by other transactions. Innodb_table_locks =0 innodb_table_locks=0 innodb_table_locks=0 innodb_table_locks=0 The WRITE. It does affect TABLES that are locked for read or write. LOCK TABLES… WRITE implicitly (for example, through triggers) or through LOCK TABLES… The READ.
When a transaction is committed or aborted, all InnoDB locks held by the transaction are released. Therefore, calling the lock table on an InnoDB table in autoCOMMIT =1 mode does not make much sense, as the acquired InnoDB table lock will be released immediately.
Other tables cannot be locked in the middle of a transaction because locked tables implicitly commit and unlock tables.

Let’s look at two examples of deadlock due to unique indexes. In the first example, the deadlock actually happened online

The deadlock occurs in our member project’s wxhC_DISTRIBUTOR table, where we have a unique key cuser_id.

transactionTemplate.execute(new TransactionCallback<Result>() {
            @Override
            public Result doInTransaction(TransactionStatus status) {//1. Insert a data distributor into the wxhC_DISTRIBUTOR table distributorManager.increaseDistributor(distributorDTO); CreateUserAccount (userAccountDTO); createUserAccountDto (userAccountDTO); createUserAccountDto (userAccountDTO); // insert, update, query;})Copy the code

Check aliyun’s log, the phenomenon is that cuser_id equals 48929303 records already exist Duplicate error

Create a yangzai table

CREATE TABLE `yangzai`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(255) NULL DEFAULT NULL.`b` int(255) NULL DEFAULT NULL.`c` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `un_a`(`a`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;Copy the code

Two records already exist in the table

Start three sessions, one transaction for each session, now assume that the three sessions perform the following operations in order:

Session1:

BEGIN;
INSERT into yangzai(a,b,c) VALUES (7.2.3);Copy the code

Session2:

BEGIN;
INSERT into yangzai(a,b,c) VALUES (7.2.3);Copy the code

Session3:

BEGIN;
INSERT into yangzai(a,b,c) VALUES (6,2,3);Copy the code

Session1:

Submit 7 rows of data
COMMIT;Copy the code

Session1 starts transaction and performs insert operation, first uses read to determine whether a (unique key a) conflicts, then adds exclusive insert intent lock, and finally adds exclusive record lock on row a=7.

Session2 also starts the transaction and inserts the same data as Session1, Session2 also determines whether the unique key a conflicts, because Session1 also inserts the data, there is a unique key conflict, but because Session1 has not committed, Session2 does not report Duplicate key error, but Session2 tries to add a shared record lock to the unique index in row A =7, but Session1 obtains the exclusive lock in row A =7, so Session2 tries to obtain the shared record lock in row A =7. Since Session2 is waiting for a shared record lock at a=7, it will not acquire an immediate acquisition-exclusive insert intent lock.

Session3 will attempt to insert a gap lock on a= (5, + infinity), but gap will wait because Session1 has already added an exclusive lock on a=7. Session2 waits to acquire the shared record lock with a=7.

Session1: COMMIT; Session2: lock a=7; Session2:1062 – Duplicate entry ‘7’ for key ‘un_A ‘, Time: 8.616000s, however, the transaction in Session2 will still hold the shared record lock a=7, Session3 will wait, Session3 gap lock (5, infinity) will not be successful, because the transaction in Session2 will still hold the shared record lock a=7. Transaction 1 failed to commit the exclusive record lock (48929303) due to dubbo API call. Transaction 2 tried to insert the same data repeatedly. Transaction 2 tried to obtain the shared record lock (48929303) due to Duplicate key. Transaction 1 holds 48929303 exclusive record lock, transaction 2 holds 48929303 shared record lock wait, resulting in transaction 3 gap wait. The mysql protection mechanism automatically checks for deadlocks

Next, let’s look at the locally-revived three transactions holding the lock before committing the transaction in Session1

Select * from test where a is the primary key and b and C are the joint unique indexes. Select * from test where a is the primary key and C is the joint unique index

CREATE TABLE `itemcenter`.`Untitled`  (
  `a` int(11) NOT NULL DEFAULT 0,
  `b` int(11) NULL DEFAULT NULL,
  `c` int(11) NULL DEFAULT NULL,
  `d` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`a`) USING BTREE,
  UNIQUE INDEX `uk_bc`(`b`, `c`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;Copy the code

Session1:

BEGIN;
insert into test values(100213.215.215.312);Copy the code

Session2:

BEGIN;
insert into test values(100214.215.215.312);Copy the code

Session3:

BEGIN;
insert into test values(100215.215.215.312);Copy the code

Session1:

ROLLBACK;Copy the code

Select * from Session1; insert (id, id, id); insert (id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id, id); It then holds the exclusive record lock for the record whose (b, C) data is 215,215.

Session2, Session3 insert data as Session1 Session2, Session3 requires inspection into the only index key only if there is a conflict, because the Session1 already open transactions into a data, Cause Session2, the transactions in the Session3 unique key conflict, Session2, Session3 will try to add (b, c) data sharing is the record of 215215 record locks, will be waiting, Since Session1 already holds an exclusive record lock for the record (b, C) data is 215,215.

Session1 rollback, Session2, Session3 and (b, C) data is 215,215 shared record lock. At this point, both session2 and session3 attempt to continue the insert, both with a write lock. Both sessions wait for each other’s rows to lock, so deadlocks occur.

Next, let’s look at the locally-revived three transactions holding the lock before committing the transaction in Session1

I hope you can help me correct my mistakes.

InnoDB lock set by different SQL statements and unique index on line cause deadlock check

Related Posts

C# common features and shortcuts

ACID property of Redis

MongoDB — access image (C#)