MySQL——SQL加锁机制简要分析

前提：针对于InnoDB引擎 行锁讨论 

锁机制

MySQL的锁机制可以分为：锁模型(lock  mode)和锁类型(lock type)

 

锁模型(lock  mode)

共享锁&排他锁

InnoDB 实现了两种类型的标准行锁：共享(S)锁和排他(X)锁。（下文简称S锁和X锁）

• S锁允许持有该锁的事务读取一行记录，可以同时有多个事务对记录加S锁；
• X锁允许持有该锁的事务更新或删除一行记录，同一时间只能有一个事务加X锁；

意象锁

注意： nnoDB支持 多粒度锁 ：即行锁与表锁共存。例如语句 LOCK TABLES ... WRITE   获取表的X锁。InnoDB使用   意向锁   实现在多个粒度

上加锁。意向锁是表锁，用于指明一个事务稍后要获取哪种类型的行锁（S or X）。意向锁有两种类型：

• 共享意向锁（IS）：指明事务将要获取行的共享锁
• 独占意向锁（IX）：指明事务将要获取行的独占锁

例如， SELECT ... FOR SHARE 获取了 IS 锁 ， SELECT ... FOR UPDATE  获取了 IX 锁。

自增锁

LOCK AUTONIC:自增锁    简称AI锁，当插入数据且表中有自增列时，会在生成自增值前为该表加AI锁

 

锁类型

锁类型可以分为表锁和行锁。表锁会对整张表加锁，有MYSQL服务器实现。行锁会锁住某一行，某几行或者行之间的间隙，由存储引擎实现，比如innodb

 

简约介绍几种简单的行锁

记录锁(Record Lock)  单个行记录下的锁，会锁住索引信息。如果没有设置索引会使用隐式的主键来锁定；

间隙锁(Gap Lock)     加在两个索引之间或第一个索引之前，或者最后一个索引之后。并不锁定索引本身;

在可重复读隔离级别下才会产生间隙锁, 它可以防止其他事物在加锁范围内插入或修改记录，保证两次读取加锁范围内的记录数据不变，避免幻读发生;

 

产生间隙锁的场景：

    使用普通索引

    使用多列唯一索引

    使用唯一索引

注意：在上述场景中进行范围查找或者索引列记录不存在，会产生间隙锁

 

Next-key 锁(Next-key locks)    可以认为是记录锁和间隙锁的组合，即锁定范围，又锁定索引记录本身

插入意向锁(Insert Intention locks)    是一种特殊的意向锁，与上文中提到的读，写意象锁是两个不通的概念，只会在Insert 时才会有这个锁。

由下文的行锁兼容矩阵， 在持有间隙锁或 Next-key 锁时，请求插入意向锁会有冲突。因为间隙锁的作用就是防止幻读，而正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行，进而避免了幻读的产生。

 

 

 

规则

在解读死锁日志、找寻死锁发生原因前，先了解几种行锁对应的死锁日志描述：

记录锁（LOCK RECNOT GAP）: lock mode X locks rec but not gap

间隙锁（LOCK GAP）: lock mode X locks gap before rec

Next-key 锁（LOCKORNIDARY）: lock mode X

插入意向锁（LOCK INSERT INTENTION）: lock_mode X locks gap before rec insert intention

 

行锁兼容矩阵（横向是已持有锁，纵向是正在请求的锁）

 

图片加载失败https://media.teamshub.com/10000/tm/2022/01/21/topic/fbb1abd6-49b2-483e-b823-7074d18a9c49.png

测试前提

1、开启lock monitor

mysql> show variables like '%innodb_status_output%';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| innodb_status_output       | OFF   |

| innodb_status_output_locks | OFF   |

+----------------------------+-------+

2 rows in set (0.00 sec)

 

开启lock  monitor

mysql> set global innodb_status_output=on;

mysql> set global innodb_status_output_locks=on;

 

2、准备数据

## 数据库版本：8.0.24 

DROP TABLE IF EXISTS `tb2`;

CREATE TABLE `tb2`  (

  `id` int(11) NOT NULL,

  `c` int(11) NULL DEFAULT NULL,

  `u` int(11) NULL DEFAULT NULL,

  `n` int(11) NULL DEFAULT NULL,

  PRIMARY KEY (`id`) USING BTREE,

  UNIQUE INDEX `idx_u_unique`(`u`) USING BTREE,

  INDEX `idx_n_normal`(`n`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

 

INSERT INTO `tb2` VALUES (10, 11, 12, 13); 

INSERT INTO `tb2` VALUES (20, 21, 22, 23); 

INSERT INTO `tb2` VALUES (30, 31, 32, 33);

----------------------------------测试过程省略(太多了)--------------------------------- 
测试过程中可以通过： 

mysql> show engine innodb status\G; 查看加锁情况

或者

mysql> select * from performance_schema.data_locks\G; 查看加锁情况

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| innodb_print_all_deadlocks | OFF   |

+----------------------------+-------+

 

注意：8.0.24版本没有 information_schema.innodb_lock_waits和information_schema.innodb_locks表了

结论： 
RR模式下： 

等值select for update/delete/update使用索引类型	锁定内容
主键(聚簇索引)	对聚簇索引记录+Record Lock
唯一索引	对辅助索引记录+Record Lock 对聚簇索引记录+Record Lock
普通索引	对相关索引记录+Next-Key Lock(该条记录的前后范围均会被锁住，比如c=23,(13,23],(23,33]均会加锁) 对聚簇索引记录+Record Lock
不使用索引	对聚簇索引全表+ Next-Key Lock

范围select for/update/delete使用索引类型	锁定内容
主键(聚簇索引)	对聚簇索引记录+Record Lock+Next-Key Lock  >=    =< 或者 对聚簇索引记录+Next-Key Lock  > <
唯一索引	对辅助索引记录+Next-Key Lock (该条记录的前后范围均会被锁住，比如u>=4222 and u<=4226 ,(4220 ,4222 ],,(4222,4223],(4223,4226],(4226,4227)均会加锁) 对聚簇索引记录+Record Lock
普通索引	对相关索引记录+Next-Key Lock( 该条记录的前后范围均会被锁住c>43 and c<=433, (43,433],(433，正无穷)均会被锁，而(33,43)这个范围不会被锁，如果c>=43 and c<=433, (33,43]会被锁 ) 对聚簇索引记录+Record Lock
不使用索引	对聚簇索引全表+ Next-Key Lock

RC模式下： 

等值select for update/delete/update使用索引类型	锁定内容
主键(聚簇索引)	对聚簇索引记录+Record Lock
唯一索引	对辅助索引记录+Record Lock 对聚簇索引记录+Record Lock
普通索引	对相关索引记录+Recode Lock 对聚簇索引记录+Record Lock
不使用索引	对聚簇索引全表+Recode Lock

范围select for/update/delete使用索引类型	锁定内容
主键(聚簇索引)	对聚簇索引记录+Record Lock
唯一索引	对辅助索引记录+Record Lock 对聚簇索引记录+Record Lock
普通索引	对相关索引记录+Record Lock 对聚簇索引记录+Record Lock
不使用索引	对聚簇索引全表+Record Lock

个人理解：在RC模式下，由于没有gap锁，也就是间接没有next-key 锁，相当于只有record 锁 

附件为：个人对mysql 锁的分类简单记录