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

标签：加锁 记录 Lock 聚簇 索引 意向锁 SQL MySQL Record

前提：针对于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 锁的分类简单记录  

 

 

标签：加锁,记录,Lock,聚簇,索引,意向锁,SQL,MySQL,Record
From： https://www.cnblogs.com/harda/p/16743491.html