vlambda博客
学习文章列表

mysql> USE `test_lock`;

mysql> CREATE TABLE `l` (
  `a` INT(11) NOT NULL,
  `b` INT(11) DEFAULT NULL,
  `c` INT(11) DEFAULT NULL,
  `d` INT(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_b` (`b`),
  UNIQUE KEY `uniq_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> INSERT INTO `l` VALUES(2,4,6,8),(4,6,8,10),(6,8,10,12),(8,10,12,14);

我们创建了一张存储引擎为InnoDB，表名为l的表，a列为主键，b列为普通二级索引，c列为唯一索引，d列没有索引。

为了方便查看锁相关信息，我们还需要打开一个参数（见名知意：InnoDB锁状态输出）：

mysql> SET GLOBAL innodb_status_output_locks='ON';
mysql> SHOW VARIABLES LIKE '%innodb%lock%';

共享锁（S）和排他锁（X）都属于行级别（row-level）的锁，且都比较好理解，S锁与X锁互相冲突，X锁与X锁也是互相冲突，S锁与S锁不会冲突。

• 当读取一行记录时，为了防止别人修改，需要添加S锁。

• 当修改一行记录时，为了防止别人同时进行修改，需要添加X锁。

Lock（锁）就是用于管理对共享资源的并发访问的。所以，现在再来看这个X锁和S锁的兼容矩阵，是不是就很好理解了。

从事MySQL相关工作的小伙伴都知道MySQL的多版本并发控制（MVCC，Mutil-Version Concurrency Control），虽然目前还没有学这部分知识，不要着急，等我。在通常情况下，普通的查询属于非锁定读，不会添加任何锁（即一致性读）。还有一种是锁定读（即当前读），那么以下两种情况会触发查询操作的锁定读（当前读）：

• SELECT ... LOCK IN SHARE MODE; 这个语法会添加S锁，对于其他事务可以读但不可以修改
  

• SELECT ... FOR UPDATE; 这个语法会添加X锁，其他事务修改或者执行SELECT ... FOR UPDATE操作都会被阻塞。

验证一下：

# Transaction 1：
mysql> BEGIN;
mysql> SELECT * FROM test_lock.l WHERE a=2 LOCK IN SHARE MODE;

# Transaction 2:
mysql> BEGIN;
mysql> SELECT * FROM test_lock.l WHERE a=2 LOCK IN SHARE MODE;
mysql> SELECT * FROM test_lock.l WHERE a=2 FOR UPDATE;

Transaction 2中的SELECT ... LOCK IN SHARE MODE没有被阻塞，SELECT ... FOR UPDATE语句被阻塞了。

综上，锁冲突发生时，另一个事务执行的SQL就会被阻塞，下面就来看下如何定位锁的相关信息（在上述SELECT ... FOR UPDATE语句被阻塞的同时，开启另外一个Session，执行命令，“TRANSACTIONS”信息栏下）：

mysql> SHOW ENGINE INNODB STATUS\G

• lock mode S ：代表S（共享）锁；
  
• lock_mode X ：代表X（排他）锁；

额外补充一点，锁相关内容需要配合事务隔离级别来看，上面的例子我们已经提前将当前我们的环境配置成READ COMMITTED（读已提交）事务隔离级别：

mysql> SET tx_isolation='READ-COMMITTED';
mysql> SELECT @@tx_isolation;

继续，除了看到锁类型外，还有很多输出信息，我们一一来看一下：

• 第1行：LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)：表示现在整个实例下总共有4个锁结构，堆大小为1136，2个行锁；

• 第2行：MySQL thread id 9, OS thread handle 140510686721792, query id 15314 localhost root statistics：MySQL线程、操作系统线程等信息；

• 第3行：SELECT * FROM test_lock.l WHERE a=2 FOR UPDATE：当前发生阻塞的SQL语句；

• 第4行：------- TRX HAS BEEN WAITING 189 SEC FOR THIS LOCK TO BE GRANTED:：表示当前的SQL为了获取（以下内容）锁已被阻塞189秒；

• 第5行：RECORD LOCKS space id 142 page no 3 n bits 72 index PRIMARY of table `test_lock`.`l` trx id 3373864 lock_mode X locks rec but not gap waiting：描述了当前SQL的要获取的锁信息，当前SQL申请的锁粒度是RECORD LOCKS（记录锁），要申请锁的这行记录在space id（表空间编号）为142，page no（页号）为3，在表`test_lock`.`l`上的index PRIMARY（聚簇索引）上，锁的类型是X（排他）锁，waiting表示当前这个锁正在等待被GRANTED（被获取）。

• 第6行：Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0：记录锁，当前记录所在的heap no（当前记录在记录物理堆的位置信息）为2，n_fields（记录中列的数量）总共有6列，记录的format（行格式）为compact。

• 第7行至第12行：这些就是加锁记录的完整信息。

 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000337b1d; asc    3{ ;;
 2: len 7; hex b7000000980110; asc        ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000006; asc     ;;
 5: len 4; hex 80000008; asc     ;;

• 第1列：列编号，0表示第一列，1表示第二列，后面依次类推；

• 第2列：字段长度，单位字节（B），len 4表示的是隐藏列DB_ROW_ID，因为我们l表定义了PRIMARY KEY（PK）主键，所以row_id就是PK，因为定义的是INT类型，所以长度4字节；len 6表示的是隐藏列DB_TRX_ID（事务ID），长度6字节；len 7表示的是DB_ROLL_PTR（回滚指针），长度7字节；后面的依次类推，这样就可以理解上面n_fields为什么有6列了；

• 第3列：完整的记录值，是以hex（十六进制方式）显示的，80000002 → 8表示该字段类型是有符号的（值为1，代表有符号），000000337bid → 0表示该字段类型是无符号的（值为0，代表无符号），80000002 → 2就表示第一个值是2，后面的值分别是4、6、8，也就是我们表第一行的完整的记录。

• 第4列：记录排序方式，asc（升序，默认值）。

后面的信息主要显示了哪些记录上还加了什么类型的锁，相同的内容解析同上，不再赘述。看红框所示的内容，熟不熟悉，TABLE LOCK ... lock mode IS、TABLE LOCK ... lock mode IX，没错，就是我们说的IS（意向共享）锁和IX（意向排他）锁，为了文章条理性，放在下一模块马上介绍。

到现在我们已经会查看锁的相关信息了，以上这部分内容非常重要，希望大家都能掌握，在日常工作中对分析锁时和后面的内容都非常有用，虽然不好理解，但是，书读百遍其义自见，大家多看几遍自然就懂了，毕竟写的这么通俗易懂。

其实看到这里，有木有发现，锁相关内容用到了很多之前学习的知识，如果大家有所遗忘，快去考个古。由此观之，InnoDB存储引擎层的锁是加在索引（记录）上的。

n_fields（记录中列的数量）总共有6列，编号是从0开始直到5，包含了隐藏列。

page no（页号）为3，InnoDB存储引擎中，page no为3的页（也就是每个表空间的第4个页）所有真实的记录都是从这个页开始写，也是聚簇索引整个B+树的root根节点。那么前3个页是干什么的呢？忘记的可以移步至。

• 意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁；

• 意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁。

”意向锁其实设计的主要目的就是：为了在一个事务中揭示下一层将被请求的锁类型“，这句话，大家有没有“ 所有字都认识，就是不知道是什么意思 ”的感觉？那么下面我来给翻译翻译：

是不是瞬间顿悟了。

官档中说：意向锁都是table-level locks。这样说的原因是：InnoDB存储引擎不像MySQL Server层的MDL锁，锁住的对象直接就是表这个对象。虽然有库、表、页、记录4个层级（粒度），但是在InnoDB存储引擎中，是没有数据库、数据页级别的锁的，这就意味着，对于InnoDB存储引擎来说，相当于所有的意向锁都是加在表级别上面的（而非表这个对象），所以就称之为它为表（table-level）级别锁，这个结论是通过反向推导得来的，这也就理解了官档中“InnoDB支持标准的行级锁，意向锁是表锁的”原因。

mysql> BEGIN;
mysql> DELETE FROM test_lock.l WHERE a=2;

mysql> SHOW ENGINE INNODB STATUS\G

• 掌握如何解读持有锁的信息，如多少个锁结构、多少行锁、锁住的记录信息（n_fields、page no 3代表的含义）、锁的类型等；

• InnoDB存储引擎层的加锁顺序是：库 → 表 → 页 → 记录；

• InnoDB存储引擎设计意向锁的主要目的就是：为了在一个事务中揭示下一层将被请求的锁类型。即意向锁表示的是下一层级要加什么锁，对于当前这个层级，大家都是互相兼容的（大家加的都是锁的意向【有想法，但还未落实，落实的事情交给下一层手下的人去做】），所以锁兼容矩阵中 → 意向锁之间都是互相兼容的。

• MySQL Server层MDL锁的表锁和InnoDB存储引擎意向锁的表（table-level）锁的区别；

• 掌握锁兼容矩阵，分清MySQL Server层MDL元数据锁和InnoDB存储引擎层的锁区别。

其实InnoDB存储引擎层的锁实现比想象中的要复杂的多，因个人水平有限，不能配合源码来更深层次的解读，望大家见谅。

今天的文章我用比较通俗易懂的话讲解了InnoDB存储引擎层加锁顺序和原理，也详细的介绍了持有锁信息的解读，这些内容都非常重要。本来打算写Record Lock（记录锁）、Gap Lock（间隙锁）和Next-key Lock（临键锁），出于篇幅和锁知识难理解的原因，还是将这部分拆分成下一篇文章，这样也可以让大家更好的吸收这部分知识。

希望今天文章的书写顺序能顺应大家的“阅读口味”，每天进步一点点，希望今天的文章能帮助到每一位小伙伴！~