MySql锁总结与加锁实验

总体分类说明
全局读锁
表级锁
表锁
一次封锁法
元数据锁
意向锁
自增锁
行级锁(InnoDB锁)
共享锁与排它锁
持有共享锁写数据
持有共享锁写数据导致死锁
两阶段锁协议
InnoDB行级锁分类
记录锁(Record Locks)
间隙锁(Gap Locks)
临键锁(Next-Key Locks)
插入意向锁(Insert Intention Locks)
Predicate Locks for Spatial Indexes
行级锁类型兼容性
查看InnoDB加锁状况
查看简单信息
查看详细信息
标准监视器输出锁部分简单解读
语句加锁情况试验
使用聚簇索引
使用二级唯一索引
使用二级非唯一索引
未使用索引(或使用不上)
修改索引值
插入数据
死锁与死锁检测
死锁处理策略
降低死锁概率策略
思想之悲观/乐观锁
总结
参考资料
toc

总体分类说明

MySql中锁概念非常多，如果不将其分门别类的理解，很容易混淆这些锁概念。
本文以MySql实战45讲为主线，结合MySql官方文档、网络博客、以及自己的储备与实验，试图较为全面的对Mysql中的锁进行总结(网络博客水很深，不实验把握不住，看了几篇博客，都说“加了行级共享锁，任务事务都不能修改数据”，这话问题很大，持有共享锁的事务，是有条件访问到数据的，为什么不能修改数据呢？？？？？？？？？？？？？？？很多博客都是CV来CV去(还标明自己原创，真的很讨厌)，连内容是错误的都不知道-_-||，我参考的博客也不一定是对的，我的博客也可能有错误，实验过程中也发现了自己以前认知上的错误，也利用实验结果证明自己的观点，如果您发现我的博客有错误，也请不吝赐教)
先对MySql中的锁进行分类：

  • 按照粒度划分锁，大致可分为全局读锁、表级锁、行级锁(innoDB实现)
  • 按照兼容性分类，分为共享锁、排它锁
  • 思想上的锁：乐观锁、悲观锁

后文大体上按照粒度划分锁类型

全局读锁

使用全局读锁后，整个库处于完全只读状态。其他线程后续数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句等操作，以及修改表结构的操作（DDL）都将被阻塞
加锁方式：Flush tables with read lock (FTWRL)
解锁方式：

  1. unlock tables
  2. 自动释放（客户端断开连接）

全局锁由MySql Server层提供，适用于使用不支持事务的引擎（如MyISAM）情况下做全库逻辑备份。

使用FTWRL做全库逻辑备份的风险点：

  • 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
  • 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

使用MySQL官方自带的逻辑备份工具mysqldump做备份时：

  • 如果使用支持事务的引擎，使用时添加参数–single-transaction，将会启动事务，如果使用的隔离级别是RC，RR，利用MVCC提供的一致性视图，备份过程中可以正常更新数据库，不会影响业务正常运行。
  • 如果使用的不支持事务的引擎，只能添加参数--lock-all-tables，通过全局锁获取一致性视图。

与set global readonly=true的对比：

  • 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。修改 global 变量的方式影响面更大
  • 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

因此，使用不支持事务引擎做全库逻辑备份应使用FTWRL，而不是set global readonly

表级锁

MySql的表级锁有表锁、元数据锁、意向锁、自增锁，前两个由MySql Server层提供，最后两个由InnoDB实现

表锁

加锁方式：lock table … read/write
解锁方式：

  1. 显示使用unlock tables，一次性解锁当前线程持有的全部表
  2. 自动释放（客户端断开连接）
  3. 再次执行lock table命令会释放之前持有的锁，并加上新表锁
  4. 持有锁线程开启事务，会隐式释放掉表锁

锁定规则：

锁类型 持有锁线程 其他线程
读锁 可以读表，不能写表 可以读表，可以申请读锁，但不能写表，申请写锁会阻塞
写锁 既可以读表，也可以写表 读写表会被阻塞，申请读写锁也会被阻塞

加表级读锁试验

加表级写锁试验

一次封锁法

表锁是一次封锁法，在执行lock table命令时，需要一次性锁定本次需要访问的全部表，在解锁之前，无法访问未锁定表。这样虽然粒度比较大，但可以避免死锁。

元数据锁

MDL(metadata lock)锁不需要显示使用，在访问表时会被自动加上，保证访问过程中表结构一致
加锁时间：

  1. 对表执行DQL、DML语句(增删改查)，会被加上MDL读锁
  2. 更改表结构时，会被加上MDL写锁

解锁时间：事务结束后(commit/rollback)

MDL锁和其他锁一样，MDL读锁之间不互斥，可并行；写锁间、读写锁间互斥，会阻塞
注意：

  1. 由于事务执行完毕之后才会释放锁，且读写锁间会互斥，因此需注意，在执行DDL语句前，应先查询information_schema库的innodb_trx表，表中有正在执行的事务，如果需要更改结构的表，刚好在执行长事务，应该先暂停DDL语句
  2. 当需要变更结构的是频繁访问的热点表时，在alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃，后续再重试

意向锁

解释还是直接摘抄官方文档吧

Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.
来源： https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-shared-exclusive-locks

原话翻译：意图锁不会阻塞任何东西，除了全表请求(比如，LOCK TABLES…WRITE)。意图锁定的主要目的是表明某人正在锁定表中的一行，或准备锁定表中的一行。
意向锁的作用：快速判断一个表锁能否请求成功(意向锁不会和行锁冲突)。如果没有意向锁，需要检测每一行是否有行锁，才能判断是否能加上一个表锁。
分类

  • 读意向锁(IS)：表明事务打算对表中的行设置共享锁
  • 写意向锁(IX)：表明事务打算对表中的行设置排他锁

隐式自动加锁
意向锁是隐式自动添加的，不需要进行任何操作。

  • 事务在获得表中行的共享锁之前，必须首先获得表上的IS锁或更强的锁。
  • 事务获得表中行的排他锁之前，必须首先获得表上的IX锁。

表级锁类型兼容性

自增锁

当插入的表中有自增列时就会先添加自增锁(AUTO_INC)，阻塞其他事务的插入操作，以便当前事务插入的行，主键是连续的

  • 自增锁在插入完成后会立刻被解锁，不会等到事务结束
  • 在事务回滚后，自增后的自增值也不会回滚

变量 innodb_autoinc_lock_mode可以控制自增锁的锁定算法，可以参阅 Section 14.6.1.6, “AUTO_INCREMENT Handling in InnoDB”

行级锁(InnoDB锁)

行级锁是在引擎层实现的，是粒度最细的一种锁，目前仅有InnoDB引擎支持行级锁

共享锁与排它锁

锁 持有锁事务 其他事务
共享锁(s) 可以读，可以写(但会升级为排它锁)行数据 可以读，写阻塞(写隐式加排它锁，但加不上)，可以立刻申请到共享锁，申请排它锁会被阻塞
排它锁(x) 可以读或写行数据 仅可以快照读，申请共享锁与排它锁均会被阻塞

持有共享锁写数据

持有共享锁的事务，进行写操作时，共享锁会升级为排它锁

持有共享锁写数据导致死锁




建议：

  • 不要在持有共享锁的时候进行写操作
  • 有写操作时直接申请排它锁

两阶段锁协议

概念：

  • 加锁阶段：事务在对任何数据读写之前，必须获得对该数据的锁，加锁不成功则阻塞直到成功
  • 解锁阶段： 事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作

    一次封锁法要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行，因此一次封锁法遵守两段锁协议；但是两段锁协议并不要求事务必须一次将所有要使用的数据全部加锁，因此遵守两段锁协议的事务可能发生死锁。

加锁方式：

  • 在事务中，InnoDB引擎会根据隔离级别，在需要的时候隐式自动加锁(DML语句执行时自动加排它锁)
  • 使用当前读方式显示加锁
    • select...lock in share mode-------------------------------加共享锁
    • select...for update-------------------------------加排它锁

解锁方式： 事务结束后(commit/rollback)自动释放
建议：

  • 事务中需要锁多个行时，将最可能造成锁冲突、最可能影响并发度的锁的申请时机尽可能往后放，以尽可能减少了事务之间的锁等待，提升并发度
  • 存在锁的情况下，尽量避免长事务

InnoDB行级锁分类

记录锁(Record Locks)

  • 对数据行加的锁，具体来说是对数据行内聚簇索引项加的锁
  • 记录锁除可以加在聚簇索引上外，还可以加在二级索引上
    • where条件中指定列为二级索引列时，会在所有命中的二级索引项以及二级索引对应的聚簇索引项上加锁

间隙锁(Gap Locks)

  • 间隙锁是加在两个索引项之间，或者是第一个索引项之前、最后一个索引项之后的，用于锁定一个区间的锁(不锁定索引项本身)
  • 间隙锁仅有一个作用：防止其他事务往锁住的间隙内插入数据，即说间隙锁与插入意向锁冲突
  • 间隙锁间可以共存，不同事务可以锁定相同的间隙，并且共享间隙锁与独占间隙锁间没有区别
  • 在RC隔离级别下，间隙锁不会被使用

临键锁(Next-Key Locks)

  • 记录锁与该索引项之前间隙锁的组合
  • 必要时，最后一条记录后面的间隙也可能用间隙锁锁住
  • 在RC隔离级别下，临键锁也不会被使用
  • 在RR隔离级别下，innoDB引擎使用临键锁进行搜索和索引扫描，这可以防止幻读

插入意向锁(Insert Intention Locks)

  • 插入意向锁是一种特殊的间隙锁*，insert语句执行之前会向待插入间隙加上此锁
  • 插入意向锁与间隙锁、临键锁单向冲突，被锁住的间隙不能插入数据，持有插入意向锁锁不影响加间隙锁、临键锁
  • 插入意向锁之间不冲突，可以同时向同一间隙插入不同主键的数据

Predicate Locks for Spatial Indexes

空间索引谓词锁，不太熟悉，后文也不会再有这个，直接拿官方文档内容吧

To enable support of isolation levels for tables with SPATIAL
indexes, InnoDB
uses predicate locks. A SPATIAL
index contains minimum bounding rectangle (MBR) values, so InnoDB
enforces consistent read on the index by setting a predicate lock on the MBR value used for a query. Other transactions cannot insert or modify a row that would match the query condition.
来源：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-shared-exclusive-locks

行级锁类型兼容性

已存在的锁\ 要加的锁 记录锁 间隙锁 临键锁 插入意向锁
记录锁 不兼容 兼容 不兼容 兼容
间隙锁 兼容 兼容 兼容 不兼容
临键锁 不兼容 兼容 不兼容 不兼容
插入意向锁 兼容 兼容 兼容 兼容

表格中，第一列表示已经持有的锁类型，第一行表示要加的锁类型
表格兼容性解释如下：

  • 记录锁与记录锁冲突，由于临键锁含有记录锁部分，所以，临键锁之间、临键锁与记录锁间冲突
  • 由于间隙锁不锁索引项，间隙锁可以共存，并且临键锁防止了其他事物往间隙插入数据，所以间隙锁仅阻塞插入意向锁，而与其他锁兼容
  • 由于临键锁含有记录锁部分，所以临键锁和间隙锁兼容，阻塞插入意向锁
  • 插入意向锁不影响其他任何锁，但间隙锁和临键锁阻塞插入意向锁

查看InnoDB加锁状况

查看简单信息

//innodb_locks表记录了所有innodb正在等待的锁，和被等待的锁
 select * from information_schema.innodb_locks;
//innodb_lock_waits表记录了所有innodb锁的持有和等待关系
select * from information_schema.innodb_lock_waits;

这两个语句在事务正常时，是查不到任何数据的，只有在有事务被阻塞时才有数据

图中结合表innodb_locks与表innodb_lock_waits的结果，可知，事务17202的加X锁的操作，被事务421988326348640的S锁阻塞

查看详细信息

查看锁详细信息需要开启锁监视器，使标准监视器包更详细的锁信息(不开启时也有锁信息，只是没有那么详细)

//打开锁监视器
set global innodb_status_output_locks=ON;
//显示标准监视器中innoDB引擎信息
show engine innodb status;

由于使用show engine innodb status;显示信息时，其输出大小被限制为1MB，可以将内容输出到错误日志来跳过这个限制，并且得到持久化的信息

set global innodb_status_output=ON;    //15s写入一次

标准监视器输出锁部分简单解读

show engine innodb status;后，在TRANSACTIONS部分就可以看到锁的详细信息了(同样还是有事务被阻塞时才能看见)，其中内容有很多，简单关注:

  • 事务号
  • 加锁的索引
  • 锁类型
    • 记录锁: lock_mode X locks rec but not gap
    • 间隙锁: lock_mode X locks gap before rec
    • 临键锁: lock_mode X
    • 插入意向锁: lock_mode X locks gap before rec insert intention
  • 等待锁(获取操作被阻塞)还是已持有
  • 锁加在哪个索引项上(十六进制显示)

锁类型后面会说，现在有个印象就好
锁部分的详细内容就像这样：


看到这个加锁信息，还让我尴尬了一会儿，事务17227的语句是

begin;explain select * from t_table_lock for update;

看加锁情况，给索引a的每一项加了临键锁，给聚簇索引每一项加了记录锁，应该走的是索引a，但是我表里只有6条数据，数据量很少，走索引a还涉及回表，不是最好的选啊，难道是我搞错了？？？然后我使用explain查看了下，确实走的索引a

当时我都懵逼了。。。。。。后面我才反应过来，表里只有索引a与聚簇索引id，二级索引a里面直接就有聚簇索引的值，这是利用了覆盖索引，不用回表。。。小丑竟是我自己。。。

语句加锁情况试验

普通select：

  • RU下不会有任何加锁操作
  • RC\RR下由于MVCC的的关系，进行快照读(一致性非锁定读取)，也不会加锁
  • S下会被隐式转换为select ... lock in share mode;(autocommit 禁用时均是，未被禁用则其他事务写时是)

加锁select：

  • 加锁的select是当前读，update、delete内部也含有当前读操作，读之前都会加锁
  • select可以加共享锁或排它锁，update、insert加的是排它锁

下面验证加锁情况，先建表，并插入数据

为方便查看，画了一个表格

pk ui i v
主键 唯一索引 非唯一索引 数据
1 1 1 1
5 5 1 5
10 10 2 10
15 15 2 15
20 20 3 20
25 25 3 25

下文仅考虑RC、RR隔离级别

使用聚簇索引

精确查询，命中聚簇索引

  • 精确查询时，命中的索引项上会被加记录锁
mysql> update t_row_lock set v = 1 where pk = 1;    //pk=1的聚簇索引项上会被加X记录锁

select * from t_row_lock where pk = 1 lock in share mode;    //pk=1的聚簇索引项会被加上S记录锁

监视器中没有共享锁的信息，查询innoDB_locks结果如下

范围查询

  • 范围查询时，如果是RC隔离级别，所有命中的聚簇索引项都会被加上记录锁
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;update t_row_lock set v = 0 where pk > 10 and pk <= 20;    //pk大于10,小于等于20的聚簇索引项(15、20)被加记录锁

  • 范围查询时，如果是RR隔离级别，所有命中的聚簇索引项都会被加上临键锁，最后被命中索引项的后一条索引项，也会被加上临键锁
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;update t_row_lock set v = 0 where pk > 10 and pk <= 20;    //pk大于10，小于等于20，以及20后一条聚簇索引项(15、20、25)被加上临键锁
Query OK, 0 rows affected (0.00 sec)


精确查询，未命中聚簇索引(指定索引不存在)

  • 如果是RC隔离级别，没有加锁
  • 如果是RR隔离级别，没有加锁
begin;update t_row_lock set v = 0 where pk = 6;    //RR、RC都没有加锁

没有发生事务阻塞，查询不了锁信息，没法贴图

使用二级唯一索引

精确查询，命中二级唯一索引

  • 精确查询时，命中的二级唯一索引项以及对应的聚簇索引上都会被加记录锁
update t_row_lock set v = 0 where ui = 5;     //ui=5的二级唯一索引项上会被加X记录锁； pk=5的聚簇索引项上会被加X记录锁


范围查询

  • 范围查询时，如果是RC隔离级别，所有命中的二级唯一索引项以及对应的聚簇索引上都会被加记录锁
begin;update t_row_lock set v = 0 where ui > 10 and ui <= 20;    //ui大于10,小于等于20的二级唯一索引项(15、20)及对应聚簇索引项(15、20)被加X记录锁

  • 范围查询时，如果是RR隔离级别，所有命中的
    • 二级非唯一索引项都会被加上临键锁，最后被命中二级唯一索引项的后一条索引项，也会被加上临键锁
    • 对应的聚簇索引会被加上记录锁(包括最后一个局促索引项之后的一条索引项）
 //ui大于10，小于等于20，以及20后一条聚簇索引项(15、20、25)被加上临键锁
//对应聚簇索引被加上X记录锁(15、20、25)
begin;update t_row_lock set v = 0 where ui > 10 and ui <= 20;


精确查询，未命中二级唯一索引索引(指定索引不存在)

  • 如果是RC隔离级别，没有加锁
  • 如果是RR隔离级别，没有加锁
begin;update t_row_lock set v = 0 where ui = 6;    //RR、RC都没有加锁

没有发生事务阻塞，查询不了锁信息，没法贴图

使用二级非唯一索引

精确查询，命中二级非唯一索引

  • 精确查询时，如果是RC隔离级别，所有命中的二级非唯一索引项以及对应的聚簇索引上都会被加记录锁
set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
//i=1的所有二级非唯一索引项与对应的聚簇索引项(pk为1、5)被加X记录锁
mysql> begin;update t_row_lock set v = 0 where i = 1;

  • 精确查询时，如果是RR隔离级别，所有命中的二级非唯一索引项会被加上临键锁，最后被命中二级非唯一索引项后被加上了间隙锁；对应聚簇索引项上被加记录锁
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
//i=1的所有二级非唯一索引项均被加上临键锁，i=1与i=2间被加上间隙锁
//对应聚簇索引被加上X记录锁
mysql> begin;update t_row_lock set v = 0 where i = 1;

范围查询

  • 范围查询时，如果是RC隔离级别，所有命中的二级非唯一索引项以及对应的聚簇索引上都会被加记录锁
set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
//i大于1,小于等于2的二级非唯一索引项(2)被加X记录锁
//对应聚簇索引项(10、15)被加X记录锁
mysql> begin;update t_row_lock set v = 0 where i > 1 and i <= 2;

  • 范围查询时，如果是RR隔离级别，所有命中的二级非唯一索引项会被加上临键锁，最后被命中二级非唯一索引项的后一条索引项，也会被加上临键锁；对应聚簇索引项(包括最后一条的后一条对应的)上被加记录锁
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;update t_row_lock set v = 0 where i > 1 and i <= 2;


精确查询，未命中二级非唯一索引(指定索引不存在)

  • 如果是RC隔离级别，没有加锁
  • 如果是RR隔离级别，没有加锁
 begin;update t_row_lock set v = 0 where i = 6;   //RR、RC都没有加锁

没有发生事务阻塞，查询不了锁信息，没法贴图

未使用索引(或使用不上)

  • 如果是RC隔离级别，没有使用索引(或使用不上)时，会进行全表扫描
    • 对于update 与delete语句，InnoDB只会为更新或删除的行加锁，其过程及原因如下：
      • 会对读到的每一行添加X记录锁(聚簇索引上)，然后由MySql Server层进行过滤，不匹配where条件的锁会被释放。
    • 对于update语句，如果行已经被锁定，InnoDB会执行半一致性读(semi-consistent read)，返回上一次已提交的版本给Server层，以供Server层判断是否匹配where条件。命中情况下，MySql Server会再次读取该行，这一次该行要么被锁定(另一事务释放锁)，要么锁等待(另一事务未释放锁)。

官方文档原文如下：

Using READ COMMITTED
has additional effects:For UPDATE or DELETE statements, InnoDB
holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE
condition. This greatly reduces the probability of deadlocks, but they can still happen.
For UPDATE statements, if a row is already locked, InnoDB
performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE
condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB
either locks it or waits for a lock on it.
来源：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

我们的SQL中只看到了具体的行被加锁，但根据官方文档的解释，实际上每行都被加了记录锁的！！！

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
//每一行的聚簇索引项都会被加X记录锁，随后会释放不符合pk=5(v=5)的记录锁
mysql> begin;update t_row_lock set v = v where v = 5;

  • 如果是RR隔离级别， 没有使用索引(或使用不上)时，也会使用聚簇索引项(可能是隐藏的)进行全表扫描，会对读到的每一行加上临键锁，直到提交或回滚才会释放。（表中的全部行以及间隙统统都被锁住了！！！！！！！！！！！）

官方文档原文如下：

When using the default REPEATABLE READ
isolation level, the first UPDATE acquires an x-lock on each row that it reads and does not release any of them:
The second UPDATE blocks as soon as it tries to acquire any locks (because first update has retained locks on all rows), and does not proceed until the first UPDATE commits or rolls back:
来源：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
//表中每行聚簇索引项上均被添加临键锁
mysql> begin;update t_row_lock set v = v where v = 5;


修改索引值

不管是RC隔离级别还是RR隔离级别，仅有使用到的索引项与对应聚簇索引项会被加记录锁，被修改的索引不会被加锁

begin;update t_row_lock set i = 1 where ui = 10;    //ui=10的索引项以及对应聚簇索引项(10)被加X记录锁

插入数据

不管是RC隔离级别还是RR隔离级别，一般来说，插入数据前设置的是插入意向锁，此锁不会阻塞其他类型的锁，但是当事务同时插入相同的主键时，会发生阻塞：

  • 首先执行insert语句的事务在聚簇索引项上加X记录锁
  • 后执行insert语句的事务在聚簇索引项上加S记录锁
//事务1
begin;insert into t_row_lock values(6, 6, 6, 6);
//事务2
insert into t_row_lock values(6, 6, 6, 6);

死锁与死锁检测

死锁概念：并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态。

死锁处理策略

  • 设置等待超时时间，事务等待锁直到超时，超时后回滚当前语句(不是整个事务)。超时时间可以通过参数 innodb_lock_wait_timeout (默认为50S)来设置
  • 设置死锁检测，死锁出现后，它会主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。参数 innodb_deadlock_detect 为 on时开启
    • 死锁检测可能耗费大量CPU资源：可以在数据库服务端做并发度控制，控制同时访问数据库的线程，降低死锁检测带来的负担

降低死锁概率策略

在了解了InnoDB的行锁规则后，我们可以知道降低死锁概率的策略了：

  • 使用索引访问数据，不管RC还是RR，不使用(或使用不到)索引代价都太大
  • 索引的区分度尽量高，这可以降低锁定范围
  • 批量处理数据时，尽量使用聚簇索引保证加锁顺序一致(使用其他索引，对聚簇索引的加锁顺序无法保证)
  • 避免长事务，将长事务拆分为多个小事务，降低持有锁时间
  • 降低隔离级别，减少锁定间隙对插入的影响
  • 有写操作时，直接申请排他锁，而不是升级升级共享锁
  • 采用切分思想，将一行数据拆分为逻辑上的多行，将操作分散到多个子行去
  • 使用lock table … read/write的方式来避免(如果业务可接受)

思想之悲观/乐观锁

乐观锁与悲观锁并不是Mysql中实际的锁，而是一种锁定数据的思想，作为思想，它的应用范围就不止于数据库。
乐观锁：假定不会发生数据竞争，线程在读取数据时不进行加锁操作，仅在更新数据前读取数据，并判断数据是否被其他线程更改，根据判断条件确定最后更新还是重试。

  • 最后的读取与更新操作需要是原子的，一般使用CAS操作实现(也可以使用锁)，并利用数据版本号解决ABA问题。
  • 适用于读多写少的情况，更新太多，重试的次数概率大，影响性能
  • 适用于重试成本不高的业务，避免过高的重试成本

悲观锁：假定会发生数据竞争，线程在读取数据时都进行加锁操作，通过加锁来避免数据竞争。

  • 跟平时试用锁的过程无异，访问数据前，先对数据加锁(可能阻塞)，访问结束后解锁
  • 适用于写多的情况，读写必然加锁
  • 悲观锁会降低并发度

总结

  1. 全局读锁可用于全库逻辑备份
  2. 表级锁有表锁、元数据锁、意向锁、自增锁 3. 表锁锁定范围为全表，锁定规则严格，使用时需一次封锁需访问全部表，这三点特性，可以避免死锁
  3. MDL锁是Server自动添加的，需要注意修改表结构的时机，不能影响线上查询与更新
  4. 意向锁用于快速判断表锁是否能被加锁成功
  5. 自增锁用于保证主键连续
  6. 持有行级共享锁时写数据，共享锁会变为排它锁
    • 共享锁升级排它锁在并发环境下很可能引发死锁
    • 建议有写操作时直接申请排它锁
  7. 行锁遵守两阶段锁协议，行锁可由InnoDB自动加锁，也可手动加锁，事务结束时释放行锁
  8. 事务中涉及多行操作时，控制加锁时机，将锁的申请时机尽量往后放
  9. 避免长事务存在，长事务会长期占有锁影响性能
  10. 行锁有记录锁、间隙锁、临键锁、插入意向锁、Predicate Locks for Spatial Indexes
  11. 记录锁对索引加锁，会对二级索引以及对应的聚簇索引都加上锁，行数据的一致性，由行数据内聚簇索引的锁定实现
  12. 间隙锁锁定区间，仅用于防止其他事务往锁定间隙插入数据，间隙锁间可以共存，RC隔离级别下无间隙锁
  13. 临键锁是记录锁与间隙锁的组合，RR级别下使用临键锁进行搜索与索引扫描，以防止幻读，RC下不使用临键锁
  14. 插入意向锁表达对某个间隙的插入意愿，由含间隙锁功能的锁决策插入是否成功
  15. 锁记录的锁(记录锁、临键锁)间相互冲突， 锁间隙的锁(间隙锁、临键锁)阻塞插入意向锁，剩余情况兼容
  16. 查询information_schema.innodb_locks可以查看阻塞时加锁情况，查询information_schema.innodb_lock_waits可以知道谁有锁，谁在等待锁
  17. 设置global innodb_status_output_locks，打开锁监视器后，可以看到阻塞时的事务加锁细节，使用global innodb_status_output_locks可直接显示，也可设置global innodb_status_output输出到错误日志
  18. 可通过innodb_lock_wait_timeout或innodb_deadlock_detect设置死锁发生时的动作，前者执行等待超时，回滚当前执行语句(不是整个事务)，后者回滚事务
  19. 降低死锁概率的方法：
    • 控制加锁范围：索引访问；索引区分度要高；降低隔离级别；拆分行数据为多个逻辑子行
    • 控制加锁顺序：批处理时，使用聚簇索引
    • 控制加锁粒度：拆分长事务
    • 控制加锁权限：写操作直接申请排它锁
    • 直接使用表锁：业务可接受时，直接lock table … read/write
  20. 乐观/悲观锁是一种锁定思想
    • 乐观锁适用于读多的情况，不加锁读增加了吞吐，写时采用原子读写，版本号解决ABA问题
    • 悲观时适用于写多的情况，读写必然加锁，保证数据一致性

21.各操作对应加锁类型

操作类型\锁类型 无锁 共享锁 排它锁
select √ (一致性读)

select...lock in share mode
√
select...for update

√
DML语句

√

各种索引使用情况加锁类型

索引使用条件\索引类型 聚簇索引 二级唯一索引 二级非唯一索引
精确匹配(RC级别) 命中索引项记录锁 命中索引项、对应聚簇索引项记录锁 命中索引项、对应聚簇索引项记录锁
精确匹配(RR级别) 命中索引项记录锁 命中索引项、对应聚簇索引项记录锁 命中索引项临键锁，后一索引项间隙锁，对应聚簇索引项记录锁
范围匹配(RC级别) 所有命中索引项记录锁 所有命中索引项、对应聚簇索引项记录锁 所有命中索引项、对应聚簇索引项记录锁
范围匹配(RR级别) 所有命中索引项、后一索引项临键锁 所有命中索引项、后一索引项临键锁，对应聚簇索引项记录锁 所有命中索引项、后一索引项临键锁，对应聚簇索引项记录锁
索引不存在 不加锁(RC、RR) 不加锁(RC、RR) 不加锁(RC、RR)
不使用索引(RC级别) 全表扫描，读到的每行加记录锁(server释放不符合条件的锁)

不使用索引(RR级别) 全表扫描，读到的每一行加临键锁，直到事务结束释放

操作对应加锁类型

操作\锁类型 X记录锁 S记录锁
修改索引值 被使用的索引项(并未被修改的索引项) 无
插入数据 主键相同时，首先执行insert 主键相同时，随后执行insert

参考资料

MySql实战45讲
14.7.1 InnoDB Locking
15.7.3 Locks Set by Different SQL Statements in InnoDB
14.18.3 InnoDB Standard Monitor and Lock Monitor Output
24.4.14 The INFORMATION_SCHEMA INNODB_LOCKS Table
14.7.2.1 Transaction Isolation Levels
MySQL加锁分析
MySQL加锁处理分析--何登成
解决死锁之路（终结篇）- 再见死锁





posted @ 2021-06-10 08:01  無雙  阅读(677)  评论(1)  编辑  收藏  举报