深入浅出InnoDB Locking

newProxyInstance

已于 2023-02-07 14:53:42 修改

阅读量480

点赞数

分类专栏： MySQL 文章标签：数据库

于 2022-12-06 17:50:49 首次发布

本文链接：https://blog.csdn.net/qq_37151886/article/details/128206726

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

前言和参考链接

主要参考官网

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

https://www.modb.pro/db/63763

https://www.cnblogs.com/nevererror/p/16251861.html

15.7.1 InnoDB Locking

Shared and Exclusive Locks 共享锁和排他锁

S = Shared 读锁，共享锁，X = Exclusive 写锁，排他锁。

MySQL使用读写锁来实现行级锁定。要想读一行必须持有S锁，要想删或改一行必须持有X锁。

如果T1在r上持有S锁，另一个事务T2可以持有S锁不能持有X锁。
如果T1在r行持有X锁，另一个事务必须等待T1事务释放X锁。

除了 SS，其他三种组合均互斥。

Intention Locks （意向、意图锁）表锁

InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES … WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:

InnoDB支持多粒度锁定。
允许行锁和表锁共存。
为了实现多粒度级别的锁定。
指明锁的类型。
事务需要对表中的一行进行稍后处理。

An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.
An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

IS和IX锁表示事务想要要在表中的单独行设置S或X锁。

For example, SELECT … FOR SHARE sets an IS lock, and SELECT … FOR UPDATE sets an IX lock.

select * from storage for share
select * from storage for update

The intention locking protocol is as follows:

Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

在事务获取行上的S锁或者X锁之前必须先获取IS或IX锁。
在这里插入图片描述
X - 行排他锁（写锁）S - 行共享锁（写锁）IX - 意图排他锁（表写锁）IS - 意图共享锁（表读锁）

一个事务获取到 X 后，另一个事务不能获取任何锁。
一个事务获取到 IX 后，另一个事务不能获取X和S锁。
一个事务获取到 S 后，另一个事务不能设置 X 或 IX。
一个事务获取到 IS 后，另一个事务不能设置 X。

在这里插入图片描述
所有的意向锁之间都是相互兼容的。

A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.

compatible 共存
conflicts 冲突

Intention locks do not block anything except full table requests (for example, LOCK TABLES … WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

除了全表请求，不要阻塞任何东西
意向锁的作用：表示某个事务正在锁定某行或者想要锁定某行。

意向锁的信息可以通过 SHOW ENGINE INNODB STATUS 语句来查看。

SHOW ENGINE INNODB STATUS

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

Record Locks（记录锁）

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

Record Locks 是在索引记录上的锁。例如我将 10 这一行锁住，其他事务就不能操作这一行。

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking. See Section 15.6.2.1, “Clustered and Secondary Indexes”.

记录锁总是锁定索引记录。即使一个表没有定义索引。对于这种情况。

记录锁的信息可以通过 SHOW ENGINE INNODB STATUS 语句来查看。InnoDB创建一个隐藏的聚集索引，并使用该索引进行记录锁定

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

Gap Locks（间隔、缝隙、间隙锁）

举个例子

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE

上面这段语句会锁定10-20这一个区间上的缝隙，例如不能插入c1=15。

A gap might span a single index value, multiple index values, or even be empty.

跨越单个索引值、多个索引值，甚至为空

Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.

Gap locks是性能和并发权衡的一部分。用在某些隔离接别上。

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:

Gap锁不需要语句，什么语句？使用唯一索引锁定行以搜索唯一行。

SELECT * FROM child WHERE id = 100;

这条语句仅会使用Record Locks记录锁将这一行锁定。

If id is not indexed or has a nonunique index, the statement does lock the preceding gap.
如果id没有索引或者具有不唯一索引，这条语句会锁定前面的gap。

总结：唯一索引等值匹配仅使用行锁，无索引或者非唯一索引会锁定gap。

It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

不同的事务可以在一个gap上持有冲突锁。
如果从索引中清除一个记录，被不同事务持有的 gap 锁必须合并。

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

它们的唯一目的是防止其他事务插入到间隙中。
一个事务持有的gap锁不会阻止其他事务从同一个gap获取gap锁。
shared和exclusive gap locks没有区别。他们不会互相冲突并且它们执行相同的功能。

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

READ COMMITTED级别下会禁用Gap locking ，仅用于外键检查和重复检查。

There are also other effects of using the READ COMMITTED isolation level. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.

Next-Key Locks

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.

Next-Key 锁是一个组合，索引记录上的记录锁和索引记录上的在缝隙上的缝隙锁。

InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

InnoDB实现行级锁。
当它搜索或扫描表索引时。
它在遇到的索引记录上设置共享锁或排他锁。
行级锁实际上是索引记录锁。

Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:

假设一个索引包含值10、11、13和20，划分为以下5个区间，左开右闭。

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 15.7.4, “Phantom Rows”).

REPEATABLE READ 这个隔离接别，InnoDB 使用next-key锁来搜索和索引扫描，可以防止《幻行》。

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

Insert Intention Locks（插入意向锁）

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.

insert intention lock 是gap lock（间隙）的一种，在行插入之前通过INSERT 操作设置。
这个锁表示以这种方式插入的意图，什么样的方式呢（如果它们没有插入间隙内的相同位置，插入同一索引间隙的多个事务不需要彼此等待）。
假设有索引为4和7的记录，

下面我们来说一个案例

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

第一个事务执行 START TRANSACTION 和 SELECT * FROM child WHERE id > 100 FOR UPDATE
第二个事务执行 START TRANSACTION 和 INSERT INTO child (id) VALUES (101)
这个时候会看到如下效果
在这里插入图片描述
第一个事务执行SHOW ENGINE INNODB STATUS，在

------- TRX HAS BEEN WAITING 28 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 4 n bits 72 index PRIMARY of table `test01`.`child` trx id 30299 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000007654; asc     vT;;
 2: len 7; hex 82000000f8011d; asc        ;;

------------------

AUTO-INC Locks 自增锁

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.

AUTO-INC lock 是一种特殊的表级别锁，发生在事务插入带有自增列的表的时候。在最简单的情况下，如果一个事务正在向表中插入数据，任何其他事务必须等待自己的插入操作，为了第一个加锁的事务收到连续的主键。

The innodb_autoinc_lock_mode variable controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

如何权衡。
自动增量值的可预测序列。
插入操作的最大并发性。

innodb_autoinc_lock_mode
show VARIABLES where VARIABLE_name =  'innodb_autoinc_lock_mode'

在这里插入图片描述

For more information, see Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

两个原则和两个优化

出自 MySQL45讲

间隙锁在可重复读隔离级别下才有效

原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间。（ ] 。
原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

实战

背景：在架构300讲中第269集设计到MySQL锁定相关知识，这里做一个全面的实验。

目的：探究 update 语句对唯一索引和普通索引的锁。

准备

表结构准备

CREATE TABLE `tb_269` (
  `id` int unsigned NOT NULL,
  `r_id` int DEFAULT NULL,
  `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `r_id_index` (`r_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

数据准备

truncate `mysql_itlaoqi`.`tb_269`;

INSERT INTO `mysql_itlaoqi`.`tb_269` (`id`, `r_id`, `name`) VALUES (10, 10, 'a');

INSERT INTO `mysql_itlaoqi`.`tb_269` (`id`, `r_id`, `name`) VALUES (20, 20, 'b');

唯一索引

每一轮都执行代码

INSERT INTO `mysql_itlaoqi`.`tb_269` (`id`, `r_id`, `name`) VALUES (5, 5, '5'); //1

UPDATE `mysql_itlaoqi`.`tb_269` SET `name` = 'fix' WHERE `id` = 10 //2

INSERT INTO `mysql_itlaoqi`.`tb_269` (`id`, `r_id`, `name`) VALUES (15, 15, '15'); //3

UPDATE `mysql_itlaoqi`.`tb_269` SET `name` = 'fix' WHERE `id` = 20  //4

INSERT INTO `mysql_itlaoqi`.`tb_269` (`id`, `r_id`, `name`) VALUES (25, 25, '25'); //5

结果：

begin;  UPDATE `mysql_itlaoqi`.`tb_269` SET `name` = 'fix' WHERE `id` = 5

在这里插入图片描述

结论：除了1其余语句均执行成功。锁定 id < 10，唯一索引等值匹配退化为间隙锁。

begin;  UPDATE `mysql_itlaoqi`.`tb_269` SET `name` = 'fix' WHERE `id` = 10

在这里插入图片描述

结论：除了2其余语句均执行成功，只锁定id=10，唯一索引等值匹配退化为行锁。

begin;  UPDATE `mysql_itlaoqi`.`tb_269` SET `name` = 'fix' WHERE `id` = 15

在这里插入图片描述
结论：3执行失败，锁定 10 < id < 20，优化2退化成间隙锁。

begin;  UPDATE `mysql_itlaoqi`.`tb_269` SET `name` = 'fix' WHERE  `id` > 8 and `id` < 12

在这里插入图片描述
结论：4、5语句执行成功，锁定区间 id < 20。

普通索引

将id改为r_id后在次进行测试

结果：

begin;  UPDATE `mysql_itlaoqi`.`tb_269` SET `name` = 'fix' WHERE `r_id` = 5

在这里插入图片描述
结论：除了1其余语句均执行成功。锁定 id < 10，优化2退化成间隙锁。

begin;  UPDATE `mysql_itlaoqi`.`tb_269` SET `name` = 'fix' WHERE `r_id` = 10

在这里插入图片描述
结论：1、2、3语句执行失败，锁定 id < 20，优化2遍历到20，退化为间隙锁。

begin;  UPDATE `mysql_itlaoqi`.`tb_269` SET `name` = 'fix' WHERE `r_id` = 15

在这里插入图片描述
结论：3语句执行失败，锁定 10 < id < 20，优化2遍历到20，退化为间隙锁。

begin;  UPDATE `mysql_itlaoqi`.`tb_269` SET `name` = 'fix' WHERE `id` > 8 and `r_id` < 12

在这里插入图片描述
结论：5执行成功，锁定 id <= 20

总结

newProxyInstance

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
深入浅出InnoDB Locking

深入浅出InnoDB Locking
复制链接

扫一扫

专栏目录

深入浅出InnoDB Locking

前言和参考链接

15.7.1 InnoDB Locking

Shared and Exclusive Locks 共享锁和排他锁

Intention Locks （意向、意图锁）表锁

Record Locks（记录锁）

Gap Locks（间隔、缝隙、间隙锁）

Next-Key Locks

Insert Intention Locks（插入意向锁）

AUTO-INC Locks 自增锁

两个原则和两个优化

实战

准备

唯一索引

普通索引

总结

“相关推荐”对你有帮助么？