MySQL 시리즈의 다섯 번째 부분인 주요 내용은 잠금(Lock)이며 잠금의 세분화된 분류, 행 잠금, 간격 잠금 및 잠금 규칙을 포함합니다.
MySQL에서 잠금을 도입하는 목적은 동시 쓰기 문제를 해결하는 것입니다. 예를 들어 두 트랜잭션이 동시에 동일한 레코드에 쓰는 것이 허용되면 dirty가 발생합니다. write 문제는 격리 수준에서 발생이 허용되지 않는 모든 종류의 예외이며 잠금 기능은 더티 쓰기 문제를 방지하기 위해 두 개의 동시 쓰기 작업이 특정 순서로 실행되도록 허용하는 것입니다.
먼저 이 글에 사용된 예제는
CREATE TABLE `user` ( `id` int(12) NOT NULL AUTO_INCREMENT, `name` varchar(36) NULL DEFAULT NULL, `age` int(12) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `age`(`age`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1;insert into user values (5,'重塑',5),(10,'达达',10),(15,'刺猬',15);复制代码
이 글에 설명된 예제는 모두 MySQL InnoDB 스토리지 엔진과 반복 읽기(Repeatable Read) 격리 수준에 속한다는 점을 말씀드립니다.
MySQL의 잠금은 잠금 세분성 측면에서 전역 잠금, 테이블 수준 잠금, 행 잠금의 세 가지 유형으로 나눌 수 있습니다.
전역 잠금은 데이터베이스 전체를 잠그게 됩니다. 이때 데이터베이스는 읽기 전용 상태가 됩니다. 데이터베이스를 수정하는 모든 문에는 DDL(데이터 정의 언어) 및 DML(데이터 조작 언어)이 포함됩니다. 추가, 삭제 및 수정의 경우 데이터베이스 전역 잠금이 해제될 때까지 명령문이 차단됩니다.
글로벌 잠금을 사용하는 가장 일반적인 장소는 전체 데이터베이스 백업을 수행하는 것입니다. 다음 명령문을 통해 글로벌 잠금의 잠금 및 잠금 해제 작업을 구현할 수 있습니다.
-- 加全局锁flush tables with read lock;-- 释放全局锁unlock table;复制代码
클라이언트 연결이 끊어지면 자동으로 연결이 끊어집니다. 전역 잠금을 해제합니다.
MySQL의 테이블 수준 잠금에는 테이블 잠금, 메타데이터 잠금(메타 데이터 잠금), 의도 잠금(의도 잠금)이 포함됩니다. ) 및 자동 증가 잠금(AUTO-INC 잠금).
테이블 잠금을 잠그고 해제하는 방법:
테이블 tableName 읽기/쓰기 잠금;
lock table tableName read/write;unlock table;
需要注意的是，表锁的加锁也限制了同一个客户端链接的操作权限，如加了表级读锁（lock table user read），那么在同一个客户端链接中在释放表级读锁以前，对同一张表（user 表）也只能进行读操作，无法进行写操作，而其他客户端链接对该表（user 表）只能进行读操作，无法进行写操作。
如加了表级写锁（lock table user write），在同一个客户端链接中可对表进行读写操作，而其他客户端链接既无法进行读操作也无法进行写操作。
第二种表级锁是元数据锁（MDL, Meta Data Lock），元数据锁会在客户端访问表的时候自动加锁，在客户端提交事务时释放锁，它防止了以下场景出现的问题：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user; | |
| alter table user add column birthday datetime; | |
| select * from user; |
如上表，sessionA 开启了一个事务，并进行一次查询，在这之后另外一个客户端 sessionB 给 user 表新增了一个 birthday 字段，然后 sessionA잠금 해제: Unlock table;
lock table user)을 추가하는 등 동일한 클라이언트 연결의 작업 권한도 제한한다는 점에 유의해야 합니다. 읽기), 동일한 클라이언트 연결에서 테이블 수준 읽기 잠금이 해제되기 전에 동일한 테이블(사용자 테이블)은 읽을 수만 있고 쓸 수는 없지만 다른 클라이언트 연결에서는 읽기 작업만 수행할 수 있습니다. 이 테이블(사용자 테이블)에서는 쓰기 작업을 수행할 수 없습니다. 테이블 수준 쓰기 잠금(lock table user write)이 추가되면 동일한 클라이언트 연결에서 테이블을 읽고 쓸 수 있지만 다른 클라이언트 연결에서는 읽거나 쓸 수 없습니다. 운영. 테이블 수준 잠금의 두 번째 유형은 메타데이터 잠금(MDL, 메타데이터 잠금)입니다. side 클라이언트가 트랜잭션을 제출하면 잠금이 자동으로 잠기므로 다음 시나리오에서 문제가 발생하지 않습니다.
| alter table user add 열 birthday datetime; | |
|---|---|
위 표와 같이 sessionA 켜짐 트랜잭션이 시작되고 쿼리가 수행된 후 다른 클라이언트 sessionB가 user 테이블에 생일 필드를 추가했습니다. 그런 다음 sessionA 쿼리를 다시 수행하세요. 메타데이터 잠금이 없으면 동일한 트랜잭션에서 두 쿼리의 레코드 및 테이블 필드 수가 일치하지 않는 것으로 나타날 수 있습니다. . |
DDL 작업은 다른 트랜잭션의 메타데이터 읽기 및 쓰기 잠금과 호환되지 않는 메타데이터 쓰기 잠금을 테이블에 추가합니다. DML 작업은 메타데이터 읽기 잠금과 공유할 수 있는 메타데이터 읽기 잠금을 테이블에 추가합니다. 그러나 다른 트랜잭션의 메타데이터 쓰기 잠금과 호환되지 않습니다. | 1.2.3 의도 잠금
| 의도 잠금 으로, 이는 트랜잭션이 테이블의 특정 행에 대한 잠금(공유 잠금 또는 배타적 잠금)을 획득하려고 함을 나타냅니다. |
의도 잠금은 테이블 잠금을 적용하고 테이블에 행 잠금이 이미 존재할 때 행 잠금이 있는지 확인하기 위해 테이블 잠금을 적용하고 테이블의 각 행을 스캔하는 다른 트랜잭션의 시스템 소비를 방지하는 것입니다. |
例如，sessionA 开启了一个事务，并对 id=5 这一行加上了行级排它锁，此时 sessionB 将对 user 表加上表级排它锁（只要 user 表中有一行被其他事务持有读锁或写锁即加锁失败）。
如果没有意向锁，sessionB 将扫描 user 表中的每一行，判断它们是否被其他事务加锁，然后才能得出 sessionB 的此次表级排它锁加锁是否成功。
而有了意向锁之后，在 sessionB 将对 user 表加锁时，会直接判断 user 表是否被其他事务加上了意向锁，若有则加锁失败，若无则可以加上表级排它锁。
意向锁的加锁规则：
第四种表级锁是自增锁，这是一种特殊的表级锁，只存在于被设置为 AUTO_INCREMENT 自增列，如 user 表中的 id 列。
自增锁会在 insert 语句执行完成后立即释放。同时，自增锁与其他事务的意向锁可共享，与其他事务的自增锁、共享锁和排它锁都是不兼容的。
行锁是由存储引擎实现的，从行锁的兼容性来看，InnoDB 实现了两种标准行锁：共享锁（Shared Locks，简称S锁）和排它锁（Exclusive Locks，简称X锁）。
这两种行锁的兼容关系与上面元数据锁的兼容关系是一样的，可以用下面的表格表示。
| 事务A\事务B | 共享锁（S锁） | 排它锁（X锁） |
|---|---|---|
| 共享锁（S锁） | 兼容 | 冲突 |
| 排它锁（X锁） | 冲突 | 冲突 |
而从行锁的粒度继续细分，又可以分为记录锁（Record Lock）、间隙锁（Gap Lock）、Next-key Lock。
我们一般所说的行锁都是指记录锁，它会把数据库中的指定记录行加上锁。
假设事务A中执行以下语句（未提交）：
begin;update user set name='达闻西' where id=5;复制代码
InnoDB 至少会在 id=5 这一行上加一把行级排它锁（X锁），不允许其他事务操作 id=5 这一行。
需要注意的是，这把锁是加在 id 列的主键索引上的，也就是说行级锁是加在索引上的。
假设现在有另一个事务B想要执行一条更新语句：
update user set name='大波浪' where id=5;复制代码
这时候，这条更新语句将被阻塞，直到事务A提交以后，事务B才能继续执行。
间隙锁，顾名思义就是给记录之间的间隙加上锁。
需要注意的是，间隙锁只存在于可重复读（Repeatable Read）隔离级别下。
不知道大家还记不记得幻读？
幻读是指在同一事务中，连续执行两次同样的查询语句，第二次的查询语句可能会返回之前不存在的行。
间隙锁的提出正是为了防止幻读中描述的幻影记录的插入而提出的，举个例子。
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where age=5;(N1) | |
| insert into user values(2, '大波浪', 5) | |
| update user set name='达闻西' where age=5; | |
| select * from user where age=5;(N2) |
sessionA 中有两处查询N1和N2，它们的查询条件都是 age=5，唯一不同的是在N2处的查询前有一条更新语句。
照理说在 RR 隔离级别下，同一个事务中两次查询相同的记录，结果应该是一样的。但是在经过更新语句的当前读查询后（更新语句的影响行数是2），N1和N2的查询结果并不相同，N2的查询将 sessionB 插入的数据也查出来了，这就是幻读。
而如果在 sessionA 中的两次次查询都用上间隙锁，比如都改为select * from user where age=5 for update。那么 sessionA 中的当前读查询语句至少会将id在(-∞, 5)和(5, 10)之间的间隙加上间隙锁，不允许其他事务插入主键id属于这两个区间的记录，即会将 sessionB 的插入语句阻塞，直到 sessionA 提交之后，sessionB 才会继续执行。
也就是说，当N2处的查询执行时，sessionB 依旧是被阻塞的状态，所以N1和N2的查询结果是一样的，都是(5,重塑,5)，也就解决了幻读的问题。
Next-key Lock 其实就是记录锁与记录锁前面间隙的间隙锁组合的产物，它既阻止了其他事务在间隙的插入操作，也阻止了其他事务对记录的修改操作。
不知道大家有没有注意到，我在行锁部分描述记录锁、间隙锁加锁的具体记录时，用的是「至少」二字，并没有详细说明具体加锁的是哪些记录，这是因为记录锁、间隙锁和 Next-key Lock 的加锁规则是十分复杂的，这也是本文主要讨论的内容。
关于加锁规则的叙述将分为三个方面：唯一索引列、普通索引列和普通列，每一方面又将细分为等值查询和范围查询两方面。
需要注意的是，这里加的锁都是指排它锁。
在开始之前，先来回顾一下示例表以及表中可能存在的行级锁。
mysql> select * from user; +----+--------+------+| id | name | age | +----+--------+------+| 5 | 重塑 | 5 | | 10 | 达达 | 10 | | 15 | 刺猬 | 15 | +----+--------+------+3 rows in set (0.00 sec)复制代码
表中可能包含的行级锁首先是每一行的记录锁——(5,重塑,5),(10,达达,5),(15,刺猬,15)。
假设 user 表的索引值有最大值 maxIndex 和最小值 minIndex，user 表还可能存在间隙锁(minIndex,5),(5,10),(10,15),(15,maxIndex)。
共三个记录锁和四个间隙锁。
首先来说唯一索引列的等值查询，这里的等值查询可以分为两种情况：命中与未命中。
当唯一索引列的等值查询命中时：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where id=5 for update; | |
| insert into user values(1,'斯斯与帆',1),(6,'夏日阳光',6),(11,'告五人',11),(16,'面孔',16); | |
| update user set age=18 where id=5;（Blocked） | |
| update user set age=18 where id=10; | |
| update user set age=18 where id=15; |
위 표의 sessionB 실행 결과는 id=5 행의 업데이트 문이 차단된 것을 제외하고는 다른 명령문은 정상적으로 실행된 것입니다. sessionB 的执行结果是除了 id=5 行的更新语句被阻塞，其他语句都正常执行。
sessionB 中的 insert 语句是为了检查间隙锁，update 语句是为了检查记录锁（行锁）。执行结果表明 user 表的所有间隙都没有被上锁，记录锁中只有 id=5 这一行被上锁了。
所以，当唯一索引列的等值查询命中时，只会给命中的记录加锁。
当唯一索引列的等值查询未命中时：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where id=3 for update; | |
| insert into user values (2,'反光镜',2);（Blocked） | |
| update user set age=18 where id=5; | |
| insert into user values (6,'夏日阳光',6); | |
| update user set age=18 where id=10; | |
| insert into user values (11,'告五人',11); | |
| update user set age=18 where id=15; | |
| insert into user values (16,'面孔',16); |
上表的执行结果是 sessionB 中 id=2 的记录插入被阻塞，其他语句正常执行。
根据执行结果可以知道 sessionA 给 user 表加的锁是间隙锁(1,5)。
所以，当唯一索引列的等值查询未命中时，会给id值所在的间隙加上间隙锁。
范围查询比等值查询要更复杂一些，它需要考虑到边界值存在于表中，以及是否命中边界值。
首先来看边界值存在于表中，但未命中的情况：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where id | |
| insert into user values (1,'斯斯与帆',1);（Blocked） | |
| update user set age=18 where id=5;（Blocked） | |
| insert into user values (6,'夏日阳光',6);（Blocked） | |
| update user set age=18 where id=10;（Blocked） | |
| insert into user values (11,'告五人',11); | |
| update user set age=18 where id=15; | |
| insert into user values (16,'面孔',16) ; |
此时 sessionA 给 user 表加上的锁是记录锁 id=5,id=10 以及间隙锁(minIndex,5),(5,10)。
我们知道间隙锁+记录锁就是 Next-key Lock，所以上述的加锁情况可以看作是两条 Next-key Lock：(minIndex, 5],(5,10]，即 Next-key Lock
sessionB의 insert 문은 gap lock을 확인하는 것이고, update 문은 레코드 잠금(row lock)을 확인하는 것입니다. 실행 결과 사용자 테이블의 모든 gap은 잠기지 않았으며, id=5인 행만 레코드 잠금에 잠긴 것으로 나타났습니다. 
는 적중 기록
만 잠급니다.| 사용자 값에 삽입 (2,'reflector',2);( | |
| 사용자 세트 연령=18 업데이트(id=5; | |
| 사용자 값에 삽입 (6,'Summer Sun',6); | |
| 사용자에 삽입 값 (11,'5명 고소',11); |
|
| insert into user value (16,'face' ,16) ; | |
|
위 표의 실행 결과는 sessionB에 id=2인 레코드 삽입이 차단되고, 다른 명령문은 정상적으로 실행되는 것입니다. |
실행 결과에 따르면 sessionA가 사용자 테이블에 추가한 잠금이 gap 잠금(1,5)임을 알 수 있습니다. |
id =5,id=10 및 간격 잠금(minIndex,5),(5,10)입니다. 🎜🎜gap 잠금 + 레코드 잠금이 Next-key Lock인 것을 알고 있으므로 위의 잠금 상황은 두 가지 Next-key Lock으로 간주할 수 있습니다. (minIndex, 5 ] ,(5,10], 즉 Next-key Lock - (minIndex,10]. 🎜🎜🎜🎜🎜🎜테이블에 경계값이 존재하면서 동시에 히트하는 경우: 🎜 🎜🎜 🎜🎜sessionA🎜🎜sessionB🎜🎜🎜🎜🎜🎜begin;🎜🎜🎜🎜🎜🎜🎜업데이트하려면 ID=10인 사용자에서 *를 선택하세요.🎜🎜🎜🎜🎜 🎜 🎜🎜🎜🎜사용자 값에 삽입 (1, 'Sisi and Sail',1);(🎜Blocked🎜)🎜🎜🎜🎜🎜🎜🎜update user set age=18 여기서 id=5;(🎜Blocked🎜)🎜🎜🎜🎜🎜🎜🎜insert into 사용자 값 ( 6,'Summer Sunshine',6);(🎜Blocked🎜)🎜🎜🎜🎜🎜🎜🎜update user set age=18 여기서 id=10;(🎜Blocked🎜)🎜🎜🎜🎜🎜🎜🎜 사용자 값에 삽입 (11,'Sue five people',11);(🎜Blocked🎜)🎜🎜🎜🎜🎜🎜🎜update user set age=18 where id=15;(🎜Blocked🎜)🎜🎜🎜🎜🎜🎜 🎜사용자 값에 삽입 (16,'face',16) ;🎜🎜🎜🎜此时 sessionA 给 user 表加上的锁是Next-key Lock —— (minIndex,15]。
当边界值不存在于表中时，不可能命中，故只有未命中一种情况：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where id | |
| insert into user values (1,'斯斯与帆',1);（Blocked） | |
| update user set age=18 where id=5;（Blocked） | |
| insert into user values (6,'夏日阳光',6);（Blocked） | |
| update user set age=18 where id=10;（Blocked） | |
| insert into user values (11,'告五人',11); | |
| update user set age=18 where id=15; | |
| insert into user values (16,'面孔',16) ; |
此时 sessionA 给 user 表加上的锁是 Next-key Lock —— (minIndex,10]，与第一种情况一样。
综上所述，在对唯一索引进行范围查询时：
Next-key Lock 加到第一个边界之外的记录上）需要注意的是，第一条中所说的间隙指的是，边界值所在的间隙，如间隙为(5,10)，查询条件为 id>7 时，这个间隙锁就是(5,10)，而不是(7,10)。
第二条举例1：查询条件为 idNext-key Lock 锁会加到 id=10 的记录上，被锁住的范围是(minIndex,10]。
第二条举例2：查询条件为 idNext-key Lock 锁会加到 id=15 的记录上，被锁住的范围是(minIndex,15]。
第二条举例3：查询条件为 id>10，第一个边界之外的记录是 id=10，
Next-key Lock锁会加到 id=10 的记录上，由于Next-key Lock锁指的是记录以左的部分，所以被锁住的范围是(5,maxIndex]。
普通索引与唯一索引的区别就在于唯一索引可以根据索引列确定唯一性，所以等值查询的加锁规则也有不同之处。
给 user 表再加一条记录:
INSERT INTO user VALUES (11, '达达2.0', 10);复制代码
这时 user 表的索引 age 结构如下图所示：
在索引 age 中可能存在的行锁是4个记录锁以及5个间隙锁。
先来看索引 age 上的加锁情况：
| sessionA | sessionB |
|---|---|
| begin; | |
| select * from user where age=10 for update; | |
| insert into user values (2,'达达',2); | |
| update user set name='痛仰' where age=5; | |
| insert into user values (6,'达达',6);（Blocked） | |
| update user set name='痛仰' where age=10 and id=10;（Blocked） | |
| update user set name='痛仰' where age=10 and id=16;）（Blocked） | |
| insert into user values (17,'达达',10);（Blocked） | |
| insert into user values (11,'达达',11);（Blocked） | |
| update user set name='痛仰' where age=15; | |
| insert into user values (16,'面孔',16) ; |
위 표의 문장과 실행 결과로 볼 때, 인덱스 연령에 대한 잠금 상황은
즉, 인덱스 연령에 대한 잠금 영역은 (5, 15)입니다.
일반 인덱스는 레코드의 고유성을 결정할 수 없기 때문에 일반 인덱스 열 동등한 쿼리에서 인덱스 연령을 잠그면 10보다 작은 첫 번째 연령 값(예: 5)과 10보다 큰 첫 번째 연령 값을 찾습니다. 값(예: 15), 이 범위 내의 간격은 간격 잠금으로 추가되고 레코드는 레코드 잠금으로 추가됩니다.
이것은 인덱스 연령에 대한 잠금 상황입니다. 쿼리 문은 레코드의 모든 열을 쿼리하는 것이므로 쿼리 규칙에 따라 인덱스 연령에 해당하는 id 값을 기본 키 인덱스 트리로 반환하여 수행합니다. 모든 열을 얻기 위한 테이블 반환 작업이므로 기본 키 인덱스에도 잠금이 적용됩니다. 여기서 age=10을 만족하는 레코드의 기본 키 ID는 각각 10과 16이므로 이 두 행도 기본 키 인덱스에서만 독점적으로 잠깁니다.
즉, 일반 인덱스 열 동등 쿼리를 테이블에 반환해야 하는 경우 조건을 충족하는 레코드에 해당하는 기본 키에도 레코드 잠금이 추가됩니다.
여기에
sessionA中的查询改为select id from user where age=10 lock in share mode;를 넣으면 커버링 인덱스 최적화로 인해 테이블 반환 작업이 수행되지 않으므로 기본 키 인덱스가 잠기지 않습니다.
여기에서는 잠금 범위(일반 인덱스만 논의됨)가 더 작다는 점을 언급해야 합니다.
| sessionA | sessionB |
|---|---|
| BEGIN; eg egSelect * 사용자에서 나이 = 10 제한 1 업데이트의 경우 1; ,2); | |
| update 사용자 세트 이름='Pain Yang' 여기서 age=5; | |
| 사용자 값에 삽입 (6,'Dada',6);( 차단됨 | )|
| 사용자 값에 삽입 (11,'Dada',11); |
|
| limit를 추가하지 않은 것과 비교하면 두 개의 insert 문이 더 원활하게 실행되는 것을 볼 수 있습니다. | |
| 다음과 같이 알 수 있습니다. | limit 구문은 조건을 충족하는 레코드에만 잠금을 추가합니다|
| 2.5 일반 인덱스 열 범위 쿼리 | 다음으로 일반 인덱스 열에 대한 범위 쿼리를 살펴보겠습니다. (여기서는 인덱스 연령의 잠금 범위만 논의합니다. 기본 키 인덱스를 잠금하면 해당 id 값이 잠깁니다. 반환 테이블입니다): |
| sessionA | sessionB
begin;
업데이트하려면 age>8 및 age
| Blocked | )|
|---|---|
| update 사용자 세트 이름='pain Yang'(나이=10 및 ID) =10;( | Blocked) |
| update user set name='Pain Yang' 여기서 age=10, id=16;( Blocked | )|
| 사용자 값에 삽입 (17,'Dada',10);( | |
| |
|
| Blocked ) |
|
| 차단됨 ) |
|
|
与普通索引列等值查询不同的是，范围查询比等值查询多了一个 age=15 的记录锁。
这个边界值与唯一索引列范围查询的原理是一样的，可以参照上文所述来理解，这里不多加赘述了。
3. 温故知新
select * from user where age=10 for update;select * from user where age>=10 and age=10 and age<blockquote><p><strong>更多相关免费学习推荐：</strong><a href="https://www.php.cn/course/list/51.html" target="_blank"><strong>mysql教程</strong></a><strong>(视频)</strong></p></blockquote> 로그인 후 복사 |
위 내용은 MySQL 5에 대한 나의 이해: 잠금 및 잠금 규칙의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
