Why is your insert deadlocked

One, foreword

I thought I only needed to learn a complete logic, but actually involved in a lot of InnoDB lock related knowledge and lock way. I don’t seem to have the ability to enumerate and analyze the locking process of various scenarios. There is not much effort to verify the accuracy of what is said online.

Have to according to the current understanding of the content, reference official documents, say their current understanding. This article is for reference only, if there is misleading, we are not responsible.

Ii. Site status

Different mysql versions and different parameter Settings may affect the locking process. The locking mechanism should be analyzed by enumerating as many key parameters as possible, such as the current mysql version, transaction isolation level, etc. Below, only a few of the more important parameters are listed.

1. Database version

``mysql> select version(); + -- -- -- -- -- -- -- -- -- -- -- + | version () | + -- -- -- -- -- -- -- -- -- -- -- + | 5.6.27 | + -- -- -- -- -- -- -- -- -- -- - +Copy the code

2. Database engine

``mysql> show variables like '%engine%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine | InnoDB |
+----------------------------+--------+
Copy the code

Note: InnoDB supports transactions, Myisam does not support transactions; InnoDB supports row and table locks; Myisam does not support row locking.

3. Transaction isolation level

``mysql> select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;
+-----------------------+------------------------+-----------------+
| @@global.tx_isolation | @@session.tx_isolation | @@tx_isolation |
+-----------------------+------------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+------------------------+-----------------+
Copy the code

Note: several transaction isolation level, READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

4. Check the gap lock status

``mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF |
+--------------------------------+-------+
Copy the code

Innodb_locks_unsafe_for_binlog: The default is 0, that is, gap lock is enabled. The main function is to control whether InnoDB locks gap. However, this setting change does not affect the need for gaps to be locked for foreign keys and unique indexes (including primary keys). Turn on REPEATABLE-READ transaction isolation level of Innodb_locks_unSAFE_for_binlog, which is largely READ-COMMITTED.

See official document 1:

By default, the value of innodb_locks_unsafe_for_binlog is 0 (disabled), which means that gap locking is enabled: InnoDB uses next-key locks for searches and index scans. To enable the variable, set it to 1. This causes gap locking to be disabled: InnoDB uses only index-record locks for searches and index scans.

Enabling innodb_locks_unsafe_for_binlog does not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.

The effect of enabling innodb_locks_unsafe_for_binlog is similar to but not identical to setting the transaction isolation level to READ COMMITTED.

5. Check the auto-add lock mode

``mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1 |
+--------------------------+-------+
Copy the code

Innodb_autoinc_lock_mode has 3 configuration modes: 0, 1, 2, corresponding to “traditional mode”, “continuous mode”, “interleaved mode” respectively. 8 Traditional mode: A table-level auto-Inc lock is added to an insert statement involving an auto-increment column. The lock is released only after the insert is complete. This is a strategy that is compatible with MySQL versions prior to 5.1. Continuous mode: a statement in which the number of inserted rows (including single-row and multi-row inserts) can be determined in advance and the value of auto-increment is allocated consecutively. For insert statements with an indeterminate number of insert rows, still add a table lock. In this mode, the transaction is rolled back and the auto-increment value is not rolled back. In other words, the auto-increment column is discontinuous. Interlaced mode: Multiple SQL statements at the same time generate interlaced auto-increment values.

Because INSERT statements often involve locking increments of columns, auto-Inc Locks are involved. In order to understand the insert locking process step by step, this article does not discuss any locking logic involving auto-increment columns. This parameter setting will probably come up in my next article.

InnoDB lock type²

1. The basic lock

Basic Locks: Shared Locks and Exclusive Locks

Mysql allows transactions with an S lock to read a row, and transactions with an X lock to update or delete a row. Records with S locks are allowed to be locked by other transactions, but not by other transactions. Records with X locks are not allowed to have S or X locks added to other transactions.

Mysql > select lock (S) from… Lock in share mode select… for update

2. Intention Locks

InnoDB introduces intentional locks to support multi-granularity (table and row locks). Intention lock IS table level lock, which can be divided into intention shared lock (IS lock) and intention exclusive lock (IX lock).

InnoDB supports multiple granularity locking which permits coexistence of row-level locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table-level locks in InnoDB that indicate which type of lock (shared or exclusive) a transaction will require later for a row in that table. There are two types of intention locks used in InnoDB (assume that transaction T has requested a lock of the indicated type on table t): Intention shared (IS): Transaction T intends to set S locks on individual rows in table t. Intention exclusive (IX): Transaction T intends to set X locks on those rows.

Before a transaction can request S and X locks, it needs to obtain the corresponding IS and IX locks.

Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t. Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t.

The main purpose of intent locks is to handle conflicts between row locks and table locks and to indicate that “a transaction is holding a lock on a row or is about to hold a lock.”

The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.

The compatibility matrix of shared lock, exclusive lock and intention lock is as follows:

	X	IX	S	IS
X	conflict	conflict	conflict	conflict
IX	conflict	Compatible with	conflict	Compatible with
S	conflict	conflict	Compatible with	Compatible with
IS	conflict	Compatible with	Compatible with	Compatible with

thinking

Intention lock is a table lock according to the literal meaning of the official document, but Daniu does not think “Intention lock is a table lock” 5? In addition, since intentional lock is mainly used to solve the conflict between row lock and table lock, given that there are very few table-level operations at ordinary times, can we not consider the problem of intentional lock too much in the analysis of locking process?

3. The row locks

Record Locks

A record lock that locks only one row of an index record. Innodb creates a hidden aggregate primary key index in the background, even if there is no index on the table. So when an SQL query does not run any indexes, an X lock will be placed after each clustered index. This is similar to a table lock, but the principle should be completely different from a table lock.

See official document 3:

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Gap Locks

Interval locking, which locks only one index interval (open interval). Locking in gaps between index records, or before or after an index record, does not include the index record itself.

Next – key lock (next – key Locks)

Record Lock + Gap lock, left open right close interval.

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system Variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows.

By default, InnoDB uses next-key locks to lock records. However, when the index of the query has a unique attribute, next-key Lock optimizes and demotes it to a Record Lock, which locks only the index itself, not the range.

Insert Intention Locks

Gap Locks have an Insert Intention Lock that occurs during an Insert operation. When multiple transactions simultaneously write different data to the same index gap, there is no need to wait for other transactions to complete and no lock wait occurs. Suppose there is a record index with key values 4 and 7, and different transactions insert 5 and 6, respectively. Each transaction generates an insert intent lock between 4 and 7, acquiring an exclusive lock on the insert row, but not locking each other, because rows do not conflict.

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

Note: Insert intention lock is not intention lock, but a special gap lock.

4. Compatibility matrix for row locks⁴

	Gap	Insert Intention	Record	Next-Key
Gap	Compatible with	Compatible with	Compatible with	Compatible with
Insert Intention	conflict	Compatible with	Compatible with	conflict
Record	Compatible with	Compatible with	conflict	conflict
Next-Key	Compatible with	Compatible with	conflict	conflict

Note: Horizontal is the lock that has been held, vertical is the lock that is being requested.

Since S locks and S locks are fully compatible, only the three combinations of held locks and requested locks are considered when determining compatibility: X, S and S, X and X, X. In addition, it is important to note that compatibility judgments are made only when the rows involved in the lock have an intersection.

The following conclusions can be drawn from the analysis of compatibility matrix:

• There will be no conflicts between INSERT operations.
• GAP, next-key prevents inserts.
• GAP and Record, next-key do not conflict
• Record conflicts with Record and next-key.
• The existing Insert lock does not prevent any locks from being added.

5. Auto-inc Locks

The auto-Inc lock is a special table-level lock that is generated when a transactional insert involving an AUTO_INCREMENT column occurs.

The official explanation is as follows 3:

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

Insert lock process

Official document 6 describes insert locking as follows:

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

A simple insert will place an exclusive lock on the index record corresponding to the insert row. This is a record lock, and there is no gap, so it will not block other sessions from inserting records in the gap.

Insertion intention Gap Lock Insertion intention Gap lock The purpose of this intentional gap lock is to indicate that when multiple transactions are simultaneously inserted into the same gap gap, as long as the inserted records are not at the same location in the gap gap, there is no need to wait for other sessions to complete, thus eliminating the need for a true gap lock for insert operations. Suppose there is a record index with key values 4 and 7, and different transactions insert 5 and 6, respectively. Each transaction generates an insert intent lock between 4 and 7, acquiring an exclusive lock on the insert row, but not locking each other, because rows do not conflict.

If a unique key conflict error occurs, read locks will be placed on duplicate index records. When multiple sessions insert the same row at the same time, a deadlock occurs if another session has acquired an exclusive lock for that row.

Think: Insert Intention Locks

Insert Intention Locks were introduced, I understand, to improve the concurrency of data inserts. If no Insert Intention Locks are available, Gap Locks may be used instead.

5. Insert deadlock scenario analysis

Next, take a look at some insert related deadlock scenarios.

1. Deadlock caused by duplicate key error

This scenario occurs when two or more transactions simultaneously insert records with the same unique key value.

Table structure

” CREATE TABLE AA (id int(10) unsigned NOT NULL COMMENT ‘,name varchar(20) NOT NULL DEFAULT COMMENT ‘,age Int (11) NOT NULL DEFAULT ‘0’ COMMENT ‘age ‘,stage int(11) NOT NULL DEFAULT ‘0’ COMMENT’ PRIMARY KEY ‘, UNIQUE KEY udx_name (name),KEY idx_stage (stage)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table data

'' CREATE TABLE 'aa' (' id 'int(10) unsigned NOT NULL COMMENT '主键',' name 'varchar(20) NOT NULL DEFAULT' COMMENT 'name ',' age 'int(11) NOT NULL DEFAULT '0' COMMENT' age ', 'stage' int(11) NOT NULL DEFAULT '0' COMMENT 'PRIMARY KEY' (`id`),UNIQUE KEY `udx_name` (`name`),KEY `idx_stage` (`stage`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;Copy the code

The transaction execution sequence table

T1(36727)	T2(36728)	T3(36729)
begin;	begin;	begin;
Insert into AA values(6, ‘test’, 12, 3);
	Insert into AA values(6, ‘test’, 12, 3);
		Insert into AA values(6, ‘test’, 12, 3);
rollback;
		ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
	Query OK, 1 row affected (13.10 sec)

ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘PRIMARY’ ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘PRIMARY’

Transaction lock occupancy

Status of transaction locks before T1 rollback:

mysql> select * from information_schema.innodb_locks; +--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+--- --------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+--- --------+ | 36729:24:3:7 | 36729 | S | RECORD | `test`.`aa` | PRIMARY | 24 | 3 | 7 | 6 | | 36727:24:3:7 | 36727 | X | RECORD | `test`.`aa` | PRIMARY | 24 | 3 | 7 | 6 | | 36728:24:3:7 | 36728 | S | RECORD | `test`.`aa` | PRIMARY | 24 | 3 | 7 | | 6 +--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+--- --------+Copy the code

Note: mysql has its own set of rules to determine whether T2 or T3 should be rolled back, which is not discussed in this article.

Deadlock log

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-07-21 19:34:23 700000a3f000
*** (1) TRANSACTION:
TRANSACTION 36728, ACTIVE 199 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 13, OS thread handle 0x700000b0b000, query id 590 localhost root update
insert into aa values(6, 'test', 12, 3)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36728 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 36729, ACTIVE 196 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 14, OS thread handle 0x700000a3f000, query id 591 localhost root update
insert into aa values(6, 'test', 12, 3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36729 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36729 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)

Copy the code

Cause a deadlock

Transaction T1 successfully insert records and obtain index id = 6 on the exclusive record locks (LOCK_X | LOCK_REC_NOT_GAP). Followed by transaction T2 and T3 began to insert record, request exclusive insert intent locks (LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION); But as a result of repeated unique key conflict, each request of exclusive record locks (LOCK_X | LOCK_REC_NOT_GAP) into a Shared record locks (LOCK_S | LOCK_REC_NOT_GAP).

T1 rollback release the exclusive record locks on the index id = 6 (LOCK_X | LOCK_REC_NOT_GAP), T2 and T3 are exclusive record lock request index id = 6 (LOCK_X | LOCK_REC_NOT_GAP). Since the X lock is mutually exclusive with the S lock, both T2 and T3 wait for the other to release the S lock. Thus, deadlocks occur.

If there are only two transactions T1 and T2 or T1 and T3 in this scenario, the deadlock situation above will not occur.

thinking

• Why convert the X lock to S lock in a transaction request when duplicate primary key conflicts are found? Insert the intent lock type to improve the concurrency efficiency of the insert.
• What is the purpose of requesting an intent lock before inserting? Personally, as analyzed by compatibility matrix, Insert Intention Locks are designed to reduce lock conflicts during inserts.

Deadlock caused by conflict between GAP and Insert Intention

Table structure

` `CREATE TABLE `t` (`a` int(11) NOT NULL,`b` int(11) DEFAULT NULL.PRIMARY KEY (`a`),KEY `idx_b` (`b`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Table data

``mysql> select * from t;
+----+------+
| a | b |
+----+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 11 | 22 |
+----+------+
Copy the code

The transaction execution sequence table

T1(36831)	T2(36832)
begin;	begin;
select * from t where b = 6 for update;
	select * from t where b = 8 for update;
Insert into t values (4,5);
	Insert into t values (4,5);
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Query OK, 1 row affected (5.45 sec)

Transaction lock occupancy

Before T2 insert, lock occupation of each transaction:

mysql> select * from information_schema.innodb_locks; +--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+---- -------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec |  lock_data | +--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+---- -------+ | 36831:25:4:5 | 36831 | X,GAP | RECORD | `test`.`t` | idx_b | 25 | 4 | 5 | 22, 11 | | 36832:25:4:5 | 36832 | X,GAP | RECORD | `test`.`t` | idx_b | 25 | 4 | 5 | 22, 11 | +--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+---- -------+Copy the code

Deadlock log

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2016-07-28 12:28:34 700000a3f000 *** (1) TRANSACTION: TRANSACTION 36831, ACTIVE 17 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1 MySQL thread id 38, OS thread handle 0x700000b0b000, Query id 953 localhost root update insert into t values (4,5) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 25 page no 4 n bits 72 index `idx_b` of table `test`.`t` trx id 36831 lock_mode X locks gap before  rec insert intention waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000016; asc ;; 1: len 4; hex 8000000b; asc ;; *** (2) TRANSACTION: TRANSACTION 36832, ACTIVE 13 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 360, 2 row lock(s) MySQL thread id 39, OS thread handle 0x700000a3f000, Query id 954 localhost root update insert into t values (4,5) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 25 page no 4 n bits 72 index `idx_b` of table `test`.`t` trx id 36832 lock_mode X locks gap before  rec Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000016; asc ;; 1: len 4; hex 8000000b; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 25 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 36832 lock mode S locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000000008fdf; asc ;; 2: len 7; hex 8d000001d00110; asc ;; 3: len 4; hex 80000005; asc ;; *** WE ROLL BACK TRANSACTION (2)Copy the code

Cause a deadlock

Transaction T1 query execution in index b = 6 and exclusive Next – key lock (LOCK_X | LOCK_ORDINARY), will lock the idx_b index range (4, 22). Transaction T2 query execution in index b = 8 with exclusive Next – key lock (LOCK_X | LOCK_ORDINARY), will lock the idx_b index range (4, 22). Since the requested GAP is compatible with the held GAP, transaction T2 can be locked successfully in the IDX_B index range (4, 22).

Transaction T1 executes the Insert statement and puts an exclusive Insert Intention lock first. Because the requested Insert Intention lock is incompatible with the existing GAP lock, transaction T1 waits for T2 to release the GAP lock. Transaction T2 executes the insert statement and waits for T1 to release the GAP lock. Thus, deadlocks occur.

Note: LOCK_ORDINARY has some of the characteristics of LOCK_GAP.

Which index should be added?

Is this exclusive lock attached to PK or secondary index?

Think after class

1. Locking without primary keys Without PK, an implicit clustered index is created. What difference does locking in this implicit clustered index make?
2. Compound index locking process
3. Where condition locking process
4. Implicit locks vs. explicit locks. When does an implicit lock convert to an explicit lock
5. If inserting a intent lock does not block any locks, is the lock still necessary? The action seen so far is to wake up the waiting thread by locking it. But that doesn’t mean you can just insert when you wake up. You need to determine again if there is a lock conflict.

7. Supplement knowledge

1. View the transaction isolation level

SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;

2. Set the isolation level

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} For example, set session Transaction Isolation Level read uncommitted.

3. View the auto_increment mode

Show variables like ‘innodb_autoinc_lock_mode;

4. View the table status

Show table status like ‘plan_branch’ \G; Show table status from test like ‘plan_branch’ \G;

5. View THE SQL performance

show profiles show profile for query 1;

6. View the latest transaction ID

Each time a new transaction is started, the id of the latest transaction is recorded for subsequent deadlock analysis. show engine innodb status\G;

7. Check the waiting status of the transaction lock

select from information_schema.innodb_locks; select from information_schema.innodb_lock_waits; select * from information_schema.innodb_trx;

8. Check innoDB state (including recent deadlock logs)

show engine innodb status;

Viii. Reference documents