Today's in-depth understanding of MySQL lock types and locking principles

Related free learning recommendations: mysql tutorial

Preface

• MySQL index bottom layer Data Structures and Algorithms
• MySQL Performance Optimization Principles-Part 1
• MySQL Performance Optimization-Practice 1
• MySQL Performance Optimization-Practice 2
• MySQL Locks and transaction isolation levels

We talked about the underlying data structure and algorithm of the MySQL database and some content about MySQL performance optimization. And the previous article talked about MySQL’s row locks and transaction isolation levels. This article will focus on lock types and locking principles.

First divide the mysql lock:

1. Divide according to the granularity of the lock: row lock, table lock, page lock
2. Divide according to the way the lock is used: sharing Lock, exclusive lock (an implementation of pessimistic lock)
3. There are also two ideological locks: pessimistic lock and optimistic lock.
4. There are several row-level lock types in InnoDB: Record Lock, Gap Lock, Next-key Lock
5. Record Lock: Lock the index record
6. Gap Lock: Gap Lock
7. Next-key Lock：Record Lock Gap Lock

Table Lock

Table-level lock is the most granular lock among MySQL locks, indicating the current The operation locks the entire table. The resource overhead is less than row locking, and deadlock will not occur, but the probability of lock conflict is high. Supported by most mysql engines, both MyISAM and InnoDB support table-level locks, but InnoDB defaults to row-level locks.

Table lock is implemented by MySQL Server. Generally, the entire table is locked when executing DDL statements, such as ALTER TABLE and other operations. When executing a SQL statement, you can also explicitly specify a table to be locked.

Table lock uses one-time lock technology, that is to say, use the lock command at the beginning of the session to lock all tables that will be used later. Before the table is released, only these added tables can be accessed. Locked tables cannot access other tables until all table locks are finally released through unlock tables.

In addition to using unlock tables to display the release lock, executing the lock table statement when the session holds other table locks will release the locks previously held by the session; executing start transaction or begin to open the transaction when the session holds other table locks , the previously held lock will also be released.

Shared lock usage：

LOCK TABLE table_name [ AS alias_name ] READ复制代码

Exclusive lock usage：

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE复制代码

Unlock usage：

unlock tables;复制代码

Row lock

Row-level lock is the most granular lock in Mysql, which means that only the row of the current operation is locked. Row-level locks can greatly reduce conflicts in database operations. Its locking granularity is the smallest, but the locking overhead is also the largest. Deadlock situations may occur. Row-level locks are divided into shared locks and exclusive locks according to how they are used.

Different storage engines have different row lock implementations. If there is no special explanation later, the row lock specifically refers to the row lock implemented by InnoDB.

Before understanding the locking principle of InnoDB, you need to have a certain understanding of its storage structure. InnoDB is a clustered index, that is, the leaf nodes of the B-tree store both the primary key index and the data rows. The leaf nodes of InnoDB's secondary index store primary key values, so when querying data through the secondary index, you need to retrieve the corresponding primary key in the clustered index and query again. For detailed knowledge about MySQL indexes, you can view "MySQL Index Underlying Data Structure and Algorithm".

The following takes the execution of two SQLs as an example to explain the locking principle of InnoDB for single row data.

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';复制代码

The first SQL uses the primary key index to query, and you only need to add a write lock on the primary key index with id = 49;

The second SQL uses the secondary index to query , then first add a write lock on the index name = Tom, and then use the InnoDB secondary index to query based on the primary key index again, so you also need to add a write lock on the primary key index id = 49, as shown in the figure above.

That is to say, using the primary key index requires adding a lock, and using the secondary index requires adding a lock on the secondary index and the primary key index.

Now that we understand the locking principle of updating a single row of data based on the index, what if the update operation involves multiple rows, such as the following SQL execution scenario.

update user set age = 10 where id > 49;复制代码

The lock release in this scenario is more complicated. There are many optimization methods. I don’t know about this yet. Anyone who knows please leave a message below to explain.

Page lock

Page-level lock is a lock in MySQL whose locking granularity is between row-level lock and table-level lock. Table-level locks are fast but have many conflicts. Row-level locks have few conflicts but are slow. So a compromised page level was adopted, locking a group of adjacent records at a time. BDB supports page-level locks.

Shared lock/exclusive lock

Shared lock (Share Lock)

Shared lock, also known as read lock, is a lock created by a read operation. Other users can read the data concurrently, but no transaction can modify the data (acquire an exclusive lock on the data) until all shared locks have been released.

If transaction T adds a shared lock to data A, other transactions can only add shared locks to A and cannot add exclusive locks. Transactions that are granted shared locks can only read data and cannot modify data.

Usage

SELECT ... LOCK IN SHARE MODE;

Add LOCK IN SHARE MODE after the query statement, Mysql will add a shared lock to each row in the query result set. When no other thread uses an exclusive lock on any row in the query result set, it can successfully apply for a shared lock, otherwise it will be blocked. Other threads can also read tables using shared locks, and these threads read the same version of data.

Exclusive lock (eXclusive Lock)

Exclusive lock is also called write lock. If transaction T adds an exclusive lock to data A, other transactions cannot add any type of blockade to A. . Transactions granted exclusive locks can both read and modify data.

Usage

SELECT ... FOR UPDATE;

Add FOR UPDATE after the query statement, Mysql will An exclusive lock is added to each row in the result set. When no other thread uses an exclusive lock on any row in the query result set, it can successfully apply for an exclusive lock, otherwise it will be blocked.

Optimistic locking and pessimistic locking

As introduced in the locking mechanism of the database, the task of concurrency control in the database management system (DBMS) is to ensure that multiple transactions access the same database at the same time. Data processing does not destroy the isolation and unity of transactions and the unity of the database.

Optimistic concurrency control (optimistic locking) and pessimistic concurrency control (pessimistic locking) are the main technical means used for concurrency control.

Whether it is pessimistic locking or optimistic locking, they are concepts defined by people and can be considered as a kind of thought. In fact, not only the concepts of optimistic locking and pessimistic locking exist in relational database systems, but also memcache, hibernate, tair, etc. have similar concepts.

For different business scenarios, different concurrency control methods should be selected. Therefore, do not understand optimistic concurrency control and pessimistic concurrency control in a narrow sense as concepts in DBMS, and do not confuse them with the lock mechanisms (row locks, table locks, exclusive locks, and shared locks) provided in the data. In fact, in DBMS, pessimistic locking is implemented by using the locking mechanism provided by the database itself.

Pessimistic Lock

In relational database management systems, pessimistic concurrency control (also known as "pessimistic lock", Pessimistic Concurrency Control, abbreviation "PCC") is a kind of concurrency control method. It prevents a transaction from modifying data in a way that affects other users. If the operation performed by a transaction applies a lock to a certain row of data, only when the transaction releases the lock can other transactions perform operations that conflict with the lock. Pessimistic concurrency control is mainly used in environments with intense data contention, and in environments where the cost of using locks to protect data when a concurrency conflict occurs is lower than the cost of rolling back transactions.

Pessimistic lock, as its name suggests, refers to a conservative attitude (pessimistic) towards data being modified by the outside world (including other current transactions of this system and transaction processing from external systems), so , keeping the data in a locked state during the entire data processing process. The implementation of pessimistic locking often relies on the locking mechanism provided by the database (only the locking mechanism provided by the database layer can truly guarantee the exclusivity of data access. Otherwise, even if the locking mechanism is implemented in this system, there is no guarantee that the external system will not modify it. Data)

The specific process of pessimistic lock

• Before modifying any record, try to add exclusive locking to the record;
• If the lock fails, it means that the record is being modified, and the current query may have to wait or throw an exception. The specific response method is determined by the developer based on actual needs;
• If the lock is successfully locked, the record can be modified, and it will be unlocked after the transaction is completed.
• If there are other operations to modify the record or add an exclusive lock, they will wait for us to unlock or throw an exception directly.

Advantages and Disadvantages of Pessimistic Lock

Pessimistic lock actually adopts the strategy of "get the lock first before accessing", which provides guarantee for the security of data processing, but in terms of efficiency, , because the additional locking mechanism generates additional overhead and increases the chance of deadlock. And it reduces concurrency; when one thing obtains a row of data, other things must wait for the transaction to be submitted before they can operate on this row of data.

Optimistic Lock

In relational database management systems, optimistic concurrency control (also known as "optimistic lock", Optimistic Concurrency Control, abbreviation "OCC") is a method of concurrency control. It assumes that multi-user concurrent transactions will not affect each other during processing, and each transaction can process the part of the data it affects without generating locks. Before committing data updates, each transaction will first check whether other transactions have modified the data after the transaction read the data. If other transactions have updates, the transaction being submitted will be rolled back.

Optimistic Locking (Optimistic Locking) Compared with pessimistic locking, optimistic locking assumes that the data will not cause conflicts under normal circumstances, so the data will only be formally conflicted when the data is submitted for update. Whether to detect or not, if a conflict is found, return error information to the user and let the user decide what to do.

Compared with pessimistic locking, optimistic locking does not use the locking mechanism provided by the database when processing the database. The general way to implement optimistic locking is to record the data version.

Data version, a version identifier added to the data. When reading data, the value of the version identifier is read together. Every time the data is updated, the version identifier is updated at the same time. When we submit an update, we compare the current version information of the corresponding record in the database table with the version identification value taken out for the first time. If the current version number of the database table is equal to the version identification value taken out for the first time, update it. , otherwise it is considered to be expired data.

Advantages and Disadvantages of Optimistic Locking

Optimistic concurrency control believes that the probability of data race between transactions is relatively small, so do it as directly as possible until The lock is only locked when submitting, so no locks or deadlocks will occur. But if you do this simply, you may still encounter unexpected results. For example, if two transactions read a certain row of the database and then write it back to the database after modification, you will encounter a problem.

Intention Shared Lock/Intention Exclusive Lock

Since table locks and row locks have different locking scopes, they will conflict with each other. So when you want to add a table lock, you must first traverse all the records in the table to determine whether an exclusive lock is added. This traversal check method is obviously an inefficient way. MySQL introduces intention locks to detect conflicts between table locks and row locks.

Intention locks are also table-level locks and can also be divided into read intention locks (IS locks) and write intention locks (IX locks). When a transaction wants to add a read lock or write lock on a record, it must first add an intention lock on the table. In this way, it is very simple to judge whether there are records in the table that are locked. Just check whether there is an intention lock on the table.

Intention locks will not conflict with each other, nor will they conflict with AUTO_INC table locks. They will only block table-level read locks or table-level write locks. In addition, intention locks will not conflict with row locks. , row locks will only conflict with row locks.

Intention locks are automatically added by InnoDB and do not require user intervention.

For insert, update, delete, InnoDB will automatically add exclusive locks (X) to the data involved;

For general Select statements, InnoDB will not add any locks, transactions You can add shared locks or exclusive locks to the display through the following statements.

Intention Shared Lock (Intention Shared Lock)

Intention Shared Lock (IS): Indicates that the transaction is preparing to add a shared lock to the data row, which means that a data row must be obtained before adding a shared lock. IS lock of the table

Intention exclusive lock (Exclusive Lock)

Intention exclusive lock (IX): similar to the above, indicating that the transaction is preparing to add an exclusive lock to the data row, indicating that the transaction is in a data row Before adding an exclusive lock, you must first obtain the IX lock of the table.

Record Lock

Record lock is the simplest row lock, and there is nothing to say. The lock in the InnoDB locking principle described above is the record lock, which only locks the record with id = 49 or name = 'Tom'.

When the SQL statement cannot use the index, a full table scan will be performed. At this time, MySQL will add record locks to all data rows in the entire table, and then the MySQL Server layer will filter them. However, when filtering at the MySQL Server layer, if it is found that the WHERE condition is not met, the lock on the corresponding record will be released. This ensures that only locks on records that meet the conditions will be held in the end, but the locking operation of each record cannot be omitted.

So update operations must be performed based on the index. Without an index, it will not only consume a lot of lock resources and increase database overhead, but also greatly reduce the concurrency performance of the database.

Gap Lock

When we use range conditions instead of equality conditions to retrieve data and request shared or exclusive locks, InnoDB will give the index of the existing data record that meets the conditions The item is locked; for records whose key values ​​are within the condition range but do not exist, InnoDB will also lock the "gap". This locking mechanism is the so-called gap lock.

间隙锁是锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

间隙锁在 InnoDB 的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排他锁。

要禁止间隙锁，可以把隔离级别降为读已提交，或者开启参数 innodb_locks_unsafe_for_binlog

 show variables like 'innodb_locks_unsafe_for_binlog';复制代码

innodb_locks_unsafe_for_binlog：默认

值为OFF，即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改 my.cnf（windows是my.ini） 重新启动才行。

# 在 my.cnf 里面的[mysqld]添加
[mysqld]
innodb_locks_unsafe_for_binlog = 1复制代码

案例1：唯一索引的间隙锁

测试环境：

MySQL5.7，InnoDB，默认的隔离级别（RR）

示例表：

CREATE TABLE `my_gap` (  `id` int(1) NOT NULL AUTO_INCREMENT,  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `my_gap` VALUES ('1', '张三');INSERT INTO `my_gap` VALUES ('5', '李四');INSERT INTO `my_gap` VALUES ('7', '王五');INSERT INTO `my_gap` VALUES ('11', '赵六');复制代码

在进行测试之前，我们先看看 my_gap 表中存在的隐藏间隙：

1. (-infinity, 1]
2. (1, 5]
3. (5, 7]
4. (7, 11]
5. (11, +infinity]

只使用记录锁（行锁），不会产生间隙锁

/* 开启事务1 */BEGIN;/* 查询 id = 5 的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 5 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 name = '杰伦' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '杰伦'); # 正常执行/* 事务3插入一条 name = '学友' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '学友'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

上述案例，由于主键是唯一索引，而且只使用一个索引查询，并且只锁定了一条记录，所以只会对 id = 5 的数据加上记录锁（行锁），而不会产生间隙锁。

产生间隙锁

恢复初始化的4条记录，继续在 id 唯一索引列上做以下测试：

/* 开启事务1 */BEGIN;/* 查询 id 在 7 - 11 范围的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '思聪3' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (3, '思聪3'); # 正常执行/* 事务3插入一条 id = 4，name = '思聪4' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '思聪4'); # 正常执行/* 事务4插入一条 id = 6，name = '思聪6' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '思聪6'); # 阻塞/* 事务5插入一条 id = 8， name = '思聪8' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '思聪8'); # 阻塞/* 事务6插入一条 id = 9， name = '思聪9' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (9, '思聪9'); # 阻塞/* 事务7插入一条 id = 11， name = '思聪11' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (11, '思聪11'); # 阻塞/* 事务8插入一条 id = 12， name = '思聪12' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (12, '思聪12'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

从上面可以看到，(5,7]、（7,11] 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以可以得出结论：当我们给(5,7] 这个区间加锁的时候，会锁住(5,7]、(7,11] 这两个区间。

恢复初始化的4条记录，我们再来测试如果锁住不存在的数据时，会如何？

/* 开启事务1 */BEGIN;/* 查询 id = 3 这一条不存在的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '小张' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (2, '小张'); # 阻塞/* 事务3插入一条 id = 4，name = '小白' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '小白'); # 阻塞/* 事务4插入一条 id = 6，name = '小东' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '小东'); # 正常执行/* 事务5插入一条 id = 8， name = '大罗' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '大罗'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码

从上面可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。

结论

1. 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁（行锁）和间隙锁，如果记录存在，则只会产生记录锁（行锁）；
2. 对于查找某一范围内的查询语句，会产生间隙锁。

案例2：普通索引的间隙锁

示例表：id 是主键，在 number 上，建立了一个普通索引。

# 注意：number 不是唯一值CREATE TABLE `my_gap1` (  `id` int(1) NOT NULL AUTO_INCREMENT,  `number` int(1) NOT NULL COMMENT '数字',
  PRIMARY KEY (`id`),  KEY `number` (`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `my_gap1` VALUES (1, 1);INSERT INTO `my_gap1` VALUES (5, 3);INSERT INTO `my_gap1` VALUES (7, 8);INSERT INTO `my_gap1` VALUES (11, 12);复制代码

在进行测试之前，我们先来看看 my_gap1 表中 number 索引存在的隐藏间隙：

1. (-infinity, 1]
2. (1, 3]
3. (3, 8]
4. (8, 12]
5. (12, +infinity]

测试1

我们执行以下的事务（事务1最后提交），分别执行下面的语句：

/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 number = 0 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (0); # 正常执行/* 事务3插入一条 number = 1 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (1); # 被阻塞/* 事务4插入一条 number = 2 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (2); # 被阻塞/* 事务5插入一条 number = 4 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (4); # 被阻塞/* 事务6插入一条 number = 8 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (8); # 正常执行/* 事务7插入一条 number = 9 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (9); # 正常执行/* 事务8插入一条 number = 10 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (10); # 正常执行/* 提交事务1 */COMMIT;复制代码

我们会发现有些语句可以正常执行，有些语句被阻塞来。查看表中的数据：

这里可以看到，number(1,8) 的间隙中，插入语句都被阻塞来，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。

测试2

我们再进行以下测试，这里将数据还原成初始化那样

/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);/* 事务1插入一条 id = 2， number = 1 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (2, 1); # 阻塞/* 事务2插入一条 id = 3， number = 2 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (3, 2); # 阻塞/* 事务3插入一条 id = 6， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (6, 8); # 阻塞/* 事务4插入一条 id = 8， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (8, 8); # 正常执行/* 事务5插入一条 id = 9， number = 9 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (9, 9); # 正常执行/* 事务6插入一条 id = 10， number = 12 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (10, 12); # 正常执行/* 事务7修改 id = 11， number = 12 的数据 */UPDATE `my_gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞/* 提交事务1 */COMMIT;复制代码

查看表中的数据；

这里有一个奇怪的现象：

• 事务3 添加 id = 6，number = 8 的数据，阻塞了；
• 事务4 添加 id = 8，number = 8 的数据，正常执行了；
• 事务7 将 id = 11，number = 12 的数据修改为 id = 11， number = 5 的操作，给阻塞了。

这是为什么？我们来看看下面的图：

从图中库看出，当 number 相同时，会根据主键 id 来排序

1. 事务 3 添加的 id = 6，number = 8，这条数据是在 (3,8) 的区间里边，所以会阻塞；
2. 事务 4 添加的 id = 8，number = 8，这条数据实在 (8,12) 区间里边，所以不会阻塞；
3. 事务 7 的修改语句相当于 在 (3,8) 的区间里边插入一条数据，所以也被阻塞了。

结论

1. 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
2. 在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通普通索引排序，再根据唯一索引排序。

临键锁（Next-key Locks）

临键锁，是记录锁（行锁）与间隙锁的组合，它的锁范围，即包含索引记录，又包含索引区间。它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 15、18、20 ，30，49，50 这几个值，可能的 Next-key 锁如下：

(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)复制代码

通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。还是之前的例子，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下就会加如下的 Next-key 锁 (30, 49](49, 50)

此时如果插入一条 id = 31 的记录将会阻塞住。之所以要把 id = 49 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 49 可能会有多条记录，为了防止再插入一条 id = 49 的记录。

注意：临键锁的主要目的，也是为了避免幻读（Phantom Read）。如果把事务隔离级别降级为 RC，临键锁则也会失效。

插入意向锁（Insert Intention Locks）

插入意向锁是一种特殊的间隙锁（简称II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。

插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。

插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

插入意向锁的作用：

1. 为来唤起等待。由于该间隙已经有锁，插入时必须阻塞，插入意向锁的作用具有阻塞功能；
2. 插入意向锁是一种特殊的间隙锁，既然是一种间隙锁，为什么不直接使用间隙锁？间隙锁直接不相互排斥。不可以阻塞即唤起等待，会造成幻读。
3. 为什么不实用记录锁（行锁）或 临键锁？申请了记录锁或临键锁，临键锁之间可能相互排斥，即影响 insert 的并发性。

自增锁（Auto-inc Locks）

AUTO_INC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTO_INCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTO_INC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTO_INC 锁具有如下特点：

• AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
• 自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

自增操作

使用AUTO_INCREMENT 函数实现自增操作，自增幅度通过 auto_increment_offset和auto_increment_increment这2个参数进行控制：

• auto_increment_offset 表示起始数字
• auto_increment_increment 表示调动幅度（即每次增加n个数字，2就代表每次+2）

通过使用last_insert_id()函数可以获得最后一个插入的数字

select last_insert_id();复制代码

自增锁

首先insert大致上可以分成三类：

1. simple insert 如insert into t(name) values('test')
2. bulk insert 如load data | insert into ... select .... from ....
3. mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');

如果存在自增字段，MySQL 会维护一个自增锁，和自增锁相关的一个参数为（5.1.22 版本后加入） innodb_autoinc_lock_mode ，可以设定 3 值：

• 0 ：traditonal (每次都会产生表锁)
• 1 ：consecutive（会产生一个轻量锁，simple insert 会获得批量的锁，保证连续插入）
• 2 ：interleaved （不会锁表，来一个处理一个，并发最高）

  MyISam引擎均为 traditonal，每次均会进行表锁。但是InnoDB引擎会视参数不同产生不同的锁，默认为 1：consecutive。

 show variables like 'innodb_autoinc_lock_mode';复制代码

traditonal

innodb_autoinc_lock_mode 为 0 时，也就是 traditional 级别。该自增锁时表锁级别，且必须等待当前 SQL 执行完毕后或者回滚才会释放，在高并发的情况下可想而知自增锁竞争时比较大的。

• 它提供来一个向后兼容的能力
• 在这一模式下，所有的 insert 语句（“insert like”）都要在语句开始的时候得到一个表级的 auto_inc 锁，在语句结束的时候才释放这把锁。注意，这里说的是语句级而不是事务级的，一个事务可能包含有一个或多个语句；
• 它能保证值分配的可预见性、可连续性、可重复性，这个也就是保证了 insert 语句在复制到 slave 的时候还能生成和 master 那边一样的值（它保证了基于语句复制的安全）；
• 由于在这种模式下 auto_inc 锁一直要保持到语句的结束，所以这个就影响了并发的插入。

consecutive

innodb_autoinc_lock_mode 为 1 时，也就是 consecutive 级别。这是如果是单一的 insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其它事务中已经有 session 获取了自增锁）。另外当SQL是一些批量 insert SQL 时，比如 insert into ... select ... ， load data , replace ... select ... 时，这时还是表级锁，可以理解为退化为必须等待当前 SQL 执行完才释放。可以认为，该值为 1 时相对比较轻量级的锁，也不会对复制产生影响，唯一的缺陷是产生自增值不一定是完全连续的。

• 这一模式下对 simple insert 做了优化，由于 simple insert 一次性插入的值的个数可以立马得到确定，所以 MyQL 可以一次生成几个连续的值，用于这个 insert 语句。总得来说这个对复制也是安全的（它保证了基于语句复制的安全）；
• 这一模式也是MySQL的默认模式，这个模式的好处是 auto_inc 锁不要一直保持到语句的结束，只要语句得到了相应的值就可以提前释放锁。

interleaved

innodb_autoinc_lock_mode 为 2 时，也就是 interleaved 级别。所有 insert 种类的 SQL 都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当 binlog_format 为 statement 时，这是复制没法保证安全，因为批量的 insert，比如 insert ... select ... 语句在这个情况下，也可以立马获取到一大批的自增 id 值，不必锁整个表， slave 在回放这个 SQL 时必然会产生错乱。

• 由于这个模式下已经没有了 auto_inc 锁，所以这个模式下的性能是最好的，但是也有一个问题，就是对于同一个语句来说它所得到的 auto_incremant 值可能不是连续的。

如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。

由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可能知道更好的性能。

Summary

Characteristics of InnoDB locks

1. When querying without index conditions, InnoDB does indeed use table locks!
2. Since MySQL's row lock is a lock for the index, not for the record, although records of different rows are accessed, if the same index key is used, a lock conflict will occur.
3. When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, whether using primary key indexes, unique indexes or ordinary indexes, InnoDB will use row locks to lock data. Lock.
4. Even if an index field is used in the condition, whether to use the index to retrieve data is determined by MySQL by judging the cost of different execution plans. If MySQL believes that the full table scan is more efficient, such as for some very large For small tables, it will not use indexes, in which case InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don't forget to check the SQL execution plan (explain view) to confirm whether the index is actually used.

Lock mode

The lock modes are: read intention lock, write intention lock, read lock, write lock and auto-increment lock (auto_inc).

Compatibility matrix of different mode locks

##CompatibleSCompatibleCompatible##X#AIIntention locks are not mutually exclusive Conflict;

	##IS	IX	S	X	AI
IS	Compatible	Compatible	Compatible		Compatible
IX	Compatible	Compatible
Compatible	Compatible				##To sum up, there are the following points:

S ​​lock is only compatible with S/IS lock and conflicts with other locks;

X lock conflicts with all other locks;
• AI lock is only compatible with intention Lock compatibility;
• Type of lock
• According to the granularity of the lock, the lock can be subdivided into
table lock

and

row lock

, row Locks can be further subdivided according to different scenarios, followed by

Next-Key Lock, Gap Lock gap lock, Record Lock record lock and insert Intention GAP lock . Different locks lock different positions. For example, record lock only locks the corresponding record, while gap lock locks the interval between records, and Next-Key Lock locks the record before the record. Clearance. The locking ranges of different types of locks are roughly as shown in the figure below.

Compatibility matrix of different types of locks

RECORD##RECORDCompatible##NEXT-KEYII GAPCompatible

	GAP	NEXT-KEY	II GAP
##Compatible		Compatible		GAP
Compatible	Compatible	Compatible
Compatible		Compatible
	##compatible		Among them, the first row represents the existing lock, and the first column represents the lock to be added. Inserting intention locks is special, so we first make a summary of inserting intention locks, as follows: Inserting intention locks does not affect other transactions and any other locks. In other words, a transaction has acquired the insertion intention lock and has no impact on other transactions; The insertion intention lock conflicts with the gap lock and Next-key lock. That is to say, if a transaction wants to acquire the insertion intention lock, if another transaction has already added a gap lock or Next-key lock, it will be blocked. The rules for other types of locks are relatively simple: Gap locks do not conflict with other locks (excluding insertion intention locks); Record lock conflicts with record lock, Next-key lock conflicts with Next-key lock, Record lock conflicts with Next-key lock;

The above is the detailed content of Today's in-depth understanding of MySQL lock types and locking principles. For more information, please follow other related articles on the PHP Chinese website!