锁

MVCC机制遗留的问题

为什么在可重复读级别下，幻读没有产生？

回想一下在事务隔离级别那篇文章中，可串行化是通过什么保证的？

对操作的每一行记录加读锁、写锁和范围锁；任何其他事务都必须等待持有锁的事务释放锁之后才能进行操作；

而可重复读级别相比之下唯一少的就是范围锁，所以无论你是否了解过具体原因，都应该去猜测推理，大概率是加了范围锁。而在这里，他有一个特殊的名字，叫做间隙锁。

虽然我很想直接上间隙锁相关的内容，但是为了更加有体系化，最好还是完整梳理一下；

本篇文章最好是有一点基础再看，因为本身就是自记录，没有打算写一篇完整的教学博客。

读锁和写锁（共享锁和排它锁）

Shared Lock 共享锁（S锁），也叫读锁；不和读锁冲突，但和写锁冲突；

当事务A持有读锁的时候，事务B依然可以加读锁；但是除了事务A自己可以加写锁，其他事务都无法对这条记录加写锁。

Exclusive Lock 排他锁（X锁），也叫写锁；和谁都冲突；

即当事务A持有记录的写锁时，其他事务读锁和写锁都加不了


	S	X
S	兼容	冲突
X	冲突	冲突

行和列代表不同事务

表锁

上锁和解锁

lock tables 表名 [as alisa] 锁类型;
unlock tables ;

表锁的命令就是上述两行，且表锁也分读写锁，表级读写的兼容冲突和读写锁一致。

通过lock tables 命令加锁的session，在释放锁之前，能且只能执行lock tables 命令后面指定的表，命令类型和锁类型保持一致；比如 lock tables A read,那么后面就只能读A表，而不能执行读B表，或者写A表；如下面的例子一样；另外如果使用了别名，那么需要确保查询语句涉及的别名和lock table的别名完全一致；

lock tables simple read;
select * from simple;
select * from batch_insert;
//[HY000][1100] Table 'batch_insert' was not locked with LOCK TABLES
update simple set name=3 where id=2;
Table 'simple' was locked with a READ lock and can't be updated

Unlock tables 会显式的释放所有该session之前加的所有表；另一个作用是释放FLUSH TABLES WITH READ LOCK命令所加的全局读锁；

Another use for UNLOCK TABLES is to release the global read lock acquired with the FLUSH TABLES WITH READ LOCK statement, which enables you to lock all tables in all databases. See Section 13.7.8.3, “FLUSH Statement”.

lock tables、start transcation命令可以隐式的释放之前持有的锁；

查看锁情况

可通过下面的命令查看表是否上锁，name_locked为0表示上锁

show OPEN TABLES where In_use > 0;

WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one session obtains a READ lock and then another session requests a WRITE lock, subsequent READ lock requests wait until the session that requested the WRITE lock has obtained the lock and released it.

对于读-写-读的情况，由于锁的优先级较高，如果申请写的session迟迟获取不到锁，会阻塞后续其他session申请读锁；具体分析看Case1；

全局读锁

关于全局锁，我一共只在两篇文档中看到过；一个是《Mysql45讲》的06篇，一个mysql官方文档的lock-table文章和FLUSH Statement文章，所以了解的并不全，加上此时的我还不太关心数据库主从的问题，所以也没有深入研究。

FLUSH TABLES WITH READ LOCK

Closes all open tables and locks all tables for all databases with a global read lock.

元数据锁

Statements acquire metadata locks one by one, not simultaneously, and perform deadlock detection in the process.
DML statements normally acquire locks in the order in which tables are mentioned in the statement.
DDL statements, LOCK TABLES, and other similar statements try to reduce the number of possible deadlocks between concurrent DDL statements by acquiring locks on explicitly named tables in name order.

元数据锁是一个个获取的，DML和DDL通过不同的方式定义执行的顺序；官网提供了一个rename table的顺序例子，但那个例子挺迷的；

//可以通过这个表查看元数据锁的情况
select * from performance_schema.metadata_locks;

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table’s structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

如果一个session或者一个事务持有某个表的元数据锁，那么另一个session或者事务就无法执行DDL操作；

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

读写阻塞问题

关于元数据锁，在《Mysql45讲》中有提到一个问题，后加的读锁会被前面的写锁所阻塞，很类似于表锁最后提到的优先级问题，有没有可能是一个原因呢？具体见case2

行锁（Record Lock)

A record lock is a lock on an index record.

行锁是在索引上的一个锁。这句话非常重要！

这里的索引可以是聚簇索引也可以是二级索引，如果表中没有索引或者查询的条件没有索引，又或者优化器认为索引没有作用，这个时候就会退化为“表锁”，但我总感觉像是锁定了所有行。

另外，如果表中没有定义聚簇索引，会自动生成一个隐藏的索引。

间隙锁（Gap Lock）

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

单靠行锁是无法解决幻读的问题的，所以innodb引入了间隙锁的概念，只在RR级别生效。间隙锁是一个范围锁，比如所以索引1和索引3之间就存在（1，3）这样一个间隙，当这个间隙被锁定的时候，就无法插入值为2的记录。

不同的事务对于同一个间隙加锁是允许发生的，因为都是在保护这个间隙不被插入数据。

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.

当查询条件是唯一索引，如果查询的值存在且是唯一的一行记录，那么是不需要加间隙锁的；因为间隙锁的出现就是为了防止幻读，对于加了唯一索引的表，同样的查询条件永远只能查出唯一的一条，既然已经保证了唯一，那么就没有间隙锁的必要了。

那如果查询结果不存在？以及查询条件是范围查询？又或者是普通索引甚至没有索引呢？

关于这些情况的排列组合，见case3

临键锁(next-key Lock)

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.

临建锁=行锁+间隙锁，是innodb RR级别默认加的锁；由于锁定的是当前索引记录行和索引前的部分，所以一般总结为左开右闭；

假如存在索引10,11,13,20，那么就会存在以下几个区间，最后一个范围是mysql会假定一个非常大的supremum，但由于实际并不存在这个值，所以是左开右开。

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

意向锁

表意向锁

innodb支持多粒度锁，即允许行锁和表锁同时存在，并且在加锁的时候需要进行冲突检测；

比如事务A已经持有了a表的一条记录索引的行锁，这个时候B事务想要给a表加表锁，就需要一行行查看是否存在行锁；为了优化这种情况，innodb引入了意向锁的概念。

表意向锁是个表级锁，分为读意向锁（IS）和写意向锁（IX），它们添加的时机是在对行索引添加S锁和X锁之前；即如果想要对某一行加锁，就必须先取得这个表的意向锁。这样当另一个事务需要判断时，就不需要一行行进行检查，只需要查看这个表是否具有意向锁即可。

意向锁的作用主要是用来阻塞表锁的。所以其互相之间是不存在互斥的，只和表锁存在冲突，即读写冲突，具体就像是下面表格这样；


	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

插入意向锁

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.

插入意向锁是间隙锁类型的一种意向锁，锁的是间隙；是在进行插入之前必须申请获得的锁，所以和间隙锁是冲突的；换句话说，如果你想插入一条语句，那么这个语句对应的间隙必须不存在锁，这样你才能加上插入意向锁，进而插入数据；

而且，插入意向锁只要插入的不是同一行，那么就可以同时插入；

自增锁（AUTO-INC Locks）

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.

如官方文档所说，自增锁其实是只针对于自增的字段，算是一个表级锁，一般对我们来说就是自增主键；当有多个事务同时想要插入，由于自增的值必须保持连续，所以多个事务的插入必须串行；

Case

case1（表锁的读-写-读阻塞）

先看正常情况，表锁的读锁是可以加多个的，如下，通过两个查询命令也可以看到确实同时加上了，没有阻塞；

//console1
lock tables simple read;
//console2
lock tables simple read;

select * from performance_schema.metadata_locks;

show OPEN TABLES where In_use > 0;

但是在两次读中间插入一次写锁的获取，后面的读锁也会同时被阻塞

//console1
lock tables simple read;
//console2
lock tables simple write;//被console1阻塞
//console3
lock tables simple read;//被console2阻塞

实验证明确实如文档所说，但暂时没有分析原理；

case2（元数据锁读-写-读）

mysql45讲中提到的一个问题，具体分析见mysql MDL读写锁阻塞，以及online ddl造成的“插队”现象_花落的速度的博客-CSDN博客

case3(next-key lock 和 primary key)

在分析之前，先贴一下45讲的总结,该总结版本是 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13，而我测试的版本是8.0.33

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

目前的数据

CREATE TABLE `simple` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '字符',
  `seq` bigint NOT NULL COMMENT '消息序号',
  `type` tinyint NOT NULL COMMENT '类型，tinyint值',
  `version` int NOT NULL DEFAULT '1' COMMENT '版本值',
  `msg` text COLLATE utf8mb4_bin COMMENT '消息',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  `yn` tinyint NOT NULL DEFAULT '1' COMMENT '是否有效',
  `uni` int NOT NULL COMMENT '唯一索引',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unidx` (`uni`),
  KEY `seqidx` (`seq`)
) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='简单测试表'

单一查询且查询结果存在(id=15)

存在一个意向表锁和行级读锁，理论上锁住的应该是(5, 15]这部分，但是由于是主键索引（唯一），所以只会锁15这一行，没有必要锁前面的间隙；这是优化1的体现；

LOCK_MODE为S,REC_NOT_GAP，我理解应该是说只有行锁，行锁类型是读锁；

start transaction ;
select * from simple where id = 15 lock in share mode ;
select * from performance_schema.data_locks;

单一查询且结果不存在(id=16）

将查询条件从15换成了16，理论上锁住的是（15,20]这部分，但是实验表明，20这行不会加行锁，所以最终表现为(15,20)；这是优化2的体现；

LOCK_MODE为S,GAP，我理解应该是说只有间隙锁，即（15,20）；

start transaction ;
select * from simple where id = 16 lock in share mode ;
select * from performance_schema.data_locks;
//console2
start transaction;
insert into simple (id,name,type,seq) value (16,5,5,5);//会被阻塞
select * from simple where id=20 for update ;//发现这行可以执行成功
既然可以成功，那就证明id = 16 的查询并没有锁20这一行，不然不可能加的上写锁

那这里如果我把id为20的更新成id为16会怎样？

update simple set id=16 where id=20;

经实验16-19都不能更新，20以后可以更，比如update simple set id=21 where id=20就可以成功；所以间隙锁是不是也能防止更新；又或者说，其实是因为更新的本质是删除再插入，再插入的被阻塞了，这里感兴趣的可以研究一下；

范围查询

id>5

按照理论，应该锁住的后5往后的所有范围，即(5,15],(15,20],(20,23],(23,super..];

所以我推测LOCK_MODE只有一个S，代表加的是临键锁，类型是读锁，没有特殊表明缺少行锁或者间隙锁就是完整的临建锁，并且我在console2尝试插入id为6或者36的，都会被阻塞

//console1
start transaction ;
select * from simple where id>5 lock in share mode ;
select * from performance_schema.data_locks;
//console2
都会被阻塞
insert into simple (id,name,type,seq) value (6,5,5,5);
insert into simple (id,name,type,seq) value (36,5,5,5);

id>=5

和上面的唯一区别就是多了个等于5，那么5上是临键锁还是行锁呢？我觉得是行锁，因为优化1，而且这样和我们的认知也是比较符合的；

实际看到确实是这样；

start transaction ;
select * from simple where id>=5 lock in share mode ;
select * from performance_schema.data_locks;

id>5 and id<20

首先5<x<20，那么正常情况应该是(5,15]和(15,20]，然后20因为不等于会被优化（触发了优化2)，所以是(5,20)

start transaction ;
select * from simple where id>5 and id<20 lock in share mode ;
select * from performance_schema.data_locks;

id>5 and id<=20

假如是5<x<=20，那就会是(5,20]；

但是注意我们前面提到过一个bug，可是我们看到目前就是锁到20为止，并不是(5,23)，翻看评论区说在MySQL 8.0.18 已经修复,而我的版本是8.0.33，这里难道是修复了吗？先存疑，因为这里只能证明主键索引修复了，后面唯一索引那里还是乱的一批

id>30

应该会直接锁(23,super…)

case4(next-key lock和 unique key)

和case3唯一的区别就是将主键索引换成了唯一索引，猜测应该是一模一样的，因为文档里的特殊规则说的也都是唯一索引，而没有限制到主键上；

单一查询且查询结果存在(uni=15)

start transaction ;
select * from simple where uni = 15 lock in share mode ;
select * from performance_schema.data_locks;

理想很美好，现实很骨感；这是什么？？突然想到行锁和间隙锁都是锁在索引上的锁，由于我查询结果是所有字段，所以会发生回表查询；当命中到唯一索引的时候会锁一次，然后根据主键id再锁一次；

但是现在我的uni和id字段值是一样的，所以为了区分，我将uni这一列都加了100，然后执行下面的句子

start transaction ;
select * from simple where uni = 115 lock in share mode ;
select * from performance_schema.data_locks;

select id from simple where uni = 115 lock in share mode ;

可以看到primary那行应该是因为回表操作，而unidx那行应该则是对应唯一索引的查询，实际锁的范围和主键索引是一致的，只不过锁的内容我不理解，lock_data为115,15，为什么？

而且如果我们查询的不是select *,而是select id ,锁的信息就不包含primary那行了；

单一查询且结果不存在(uni=116）

start transaction ;
select * from simple where uni = 116 lock in share mode ;
select * from performance_schema.data_locks;

由于查询不到，所以也不会回表查询，就不存在primary那行了

uni>105

start transaction ;
select id from simple where simple.uni>105 lock in share mode ;
select * from performance_schema.data_locks;

我理解到每个索引节点的时候，都会执行一次select * from simple where id = x；所以会多出几行只有行锁primary的记录；

uni>=105只是会在unidx和primary上各多一个锁，但范围和唯一索引依然一致，就不贴了

uni>105 and uni<120

//console1
commit ;
start transaction ;
select * from simple where uni>105 and uni<120 lock in share mode ;
select * from performance_schema.data_locks;
//console2
select * from simple where uni=120 for update ;//被阻塞

这里和上面不一样的是，这里把120这行也锁上了，主键索引锁20是间隙锁，这里是临键锁；为什么这里会锁上呢？就很像是bug并没有修复，依然锁到了第一个不满足条件的，并且加了临键锁

uni>105 and uni<=120

commit ;
start transaction ;
select * from simple where uni>105 and uni<=120 lock in share mode ;
select * from performance_schema.data_locks;

这里更离谱，这里为什么把123都给锁上了？？感觉bug依然存在，多锁了一个区间

uni>130和上面的id>30结果一样，就不贴了

总结：对于唯一索引来说，因为存在主键，那么会产生回表操作，回表操作会给主键再加一把锁；而那个bug依旧存在，只有主键索引的修复了，非主键唯一索引依然存在这个bug;

case5（索引加在哪）

//console1
start transaction ;
select id from simple where  uni=105 lock in share mode ;
select * from performance_schema.data_locks;
//console2
start transaction ;
update simple set name='new' where id=5;

现在我们已经清楚，执行完console1之后，会给unidx加一个行锁，因为没有回表，所以主键上没有锁；那么console2能否成功执行呢？

答案是，可以的；

我个人理解，是因为锁是加在索引上的，而索引是列维度的，不是行维度的；console2执行语句只会去判断id这个索引上，有没有5这个锁；

接下来我们反过来

//console1
start transaction ;
select * from simple where  id=5 lock in share mode ;
select * from performance_schema.data_locks;
//console2
start transaction ;
update simple set name='new' where uni=105;

你试着一起敲一下就会发现，咦，console2怎么阻塞了呢？按上面所说的，不是不应该吗？

实际上console1的执行锁的确实是id；

但是你console2的执行，会回表啊，会尝试给id加写锁，但是id已经加了读锁了，所以自然不行了；

所以，不要盲目的只看查询条件，要理解当前语句都会加什么锁，是否和已经加的锁冲突；

最后，我们再来看一个附加题，下面两个语句加的锁是否一样呢？

start transaction ;
select id from simple where  uni=105 lock in share mode ;
select * from performance_schema.data_locks;


start transaction ;
select id from simple where  uni=105 for update ;
select * from performance_schema.data_locks;

在我没有尝试之前，我理解都没有回表，那么就应该一个是唯一索引加读锁，一个是唯一索引加写锁；

但是实际结果却是lock in share mode是对的，for update会认为你要更新语句，自动给主键加锁了

case6（next-key lock 和index）

吸取uni的教训，我给seq的值都加了200，现在这个表是这样的

seq=215

start transaction ;
select * from simple where  seq=215 lock in share mode ;
select * from performance_schema.data_locks;

除了意向锁，其他三个我们一个个看；

seqidx(S)这行是普通索引执行时加的临键锁，由于不是唯一索引，所以不能优化（因为可能存在重复）

primary(S,REC_NOT_GAP)这是回表操作带来的

seqidx(S,GAP)这行是因为不是唯一索引，所以在查询到匹配的值之后不会立马停止（因为后面可能还存在相同的值），所以必须要到不符合条件的值为止，而所有查询过的都会加索引，所以存在一个间隙锁。

seq=216

start transaction ;
select * from simple where  seq=216 lock in share mode ;
select * from performance_schema.data_locks;

我理解，应该是从205开始查，查到第一个不符合条件的值是215，加上中间没有回表，所以就这一个锁；理论应该是(215,220]，但由于优化2，所以退化为间隙锁；

seq>215 and seq<220

start transaction ;
select * from simple where  seq>215 and seq <220 lock in share mode ;
select * from performance_schema.data_locks;

从215开始匹配，第一个不符合条件的是220，所以只能是（215,220]

seq>215 and seq <=220

start transaction ;
select * from simple where  seq>215 and seq <=220 lock in share mode ;
select * from performance_schema.data_locks;

这里和上面区别就是不符合条件的会到223为止，另外中间因为匹配成功会回一次表

seq>230和前面unidx>130和id>30都一样

case7(next-key和没有索引）

alter table simple drop index  seqidx;
start transaction ;
select * from simple where  seq=215 lock in share mode ;
select * from performance_schema.data_locks;

前面提到过，查询条件匹配不到索引或者只是索引的一部分，这个时候为了保证数据的准确性，会给整个表“加锁”，其实给表里所有的记录都加锁(这里我不知道描述的对不对，因为表锁！=所有记录加锁，虽然效果相似，但并不是一个东西）.

同时因为这个表存在意向读锁，通过lock tables simple write 加写的表锁会冲突；

case8(全表没有索引)

CREATE TABLE `simple_no_idx` (
  `id` bigint NOT NULL  COMMENT '没加索引的主键',
  `name` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '字符'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='简单测试表';
insert into simple_no_idx (id, name) VALUES (1,'1'),(2,'2');

执行下面代码，会发现在没有聚簇索引时会自动生成聚簇索引

start transaction ;
select * from simple_no_idx lock in share mode ;

select * from performance_schema.data_locks;

疑问

对于事务和表锁一起用的情况，到底要使用set autocommit=0吗？why？

The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. For example, if you need to write to table t1 and read from table t2, you can do this:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here
...COMMIT;
UNLOCK TABLES;

When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its internal table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have autocommit = 1, because then InnoDB releases its internal table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. InnoDB does not acquire the internal table lock at all if autocommit = 1, to help old applications avoid unnecessary deadlocks.

参考文档

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-shared-exclusive-locks

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

06 | 全局锁和表锁：给表加个字段怎么有这么多阻碍？-极客时间

mysql MDL读写锁阻塞，以及online ddl造成的“插队”现象_花落的速度的博客-CSDN博客

MYSQL查看表是否被锁、以及解锁_mysql查看锁表_清石小猿的博客-CSDN博客