[数据库]MySQL锁

本文针对 MySQL 的 innodb 存储引擎做介绍

---

一.了解锁之前

锁的作用

锁是为了实现事务隔离性所产生的，在多个不同事务实现的时候，为了避免各种问题（幻读，脏读等问题），事务对某个资源持有锁，防止其他事务操作数据影响当前事务。

锁的实现

锁的实现是基于索引实现的，在Innodb中

• SQL 语句条件命中索引，作用于索引的节点，相当于行锁
• SQL 语句条件没有命中索引，作用与表的整个聚簇索引树，相当于表锁
• 命中索引数还是节点，取决于SQL的查询条件

二.MySQL 锁类型

1.根据类型区分

共享锁 shared (S) lock

• 允许多个线程同时获得锁。虽允许多个线程获取锁，但是也有争夺锁失败的情况

排他锁 exclusive (X) lock

• 只允许一个线程获得锁。争夺失败的线程排列在队列中，等获取到锁的线程释放后，其余的线程是按照队列里面一次获取的。

mysql 锁性能监控

show status like 'innodb_row_lock_%';

Variable_name	Value	mean
Innodb_row_lock_current_waits	0	当前等待锁的数量
Innodb_row_lock_time	23265	系统启动到现在、锁定的总时间长度
Innodb_row_lock_time_avg	2115	每次平均锁定的时间
Innodb_row_lock_time_max	7401	最长一次锁定时间
Innodb_row_lock_waits	11	系统启动到现在、总共锁定次数

锁资源有限，共享锁也会有争夺不到锁的情况

2.根据粒度区分

表锁

• 对整个表的数据加锁，当前表有事务访问时，其他事务需等待。

行锁

• 锁住行的记录，当事务在不同行的时候，事务不相互影响。

记录锁 (Record Lock)

• 行锁一种 。
• 查询条件是唯一索引的时候，表中的一条数据。

间隙锁 gap lock

• 该锁是为了解决 事务级别为可重复读 某种场景引进的锁机制。
• 当数据库范围读取的时候，若只对行数据加锁，当有插入该范围的数据时，会有幻读的情况。（如现在数据有id 为 （1、3、5、7），查询条件为 id <= 7 and id >= 1，当插入id = 2 的数据时，会读取该数据，事务回滚后，该数据却不存在）
• 间隙锁是把该范围的数据间隙加上锁，最后匹配到最后一个不符合条件的行前的间隙。
  （//TODO 插图）
• 数据库是通过行锁和间隙锁共同组成的（next-key lock）
• next-key lock 的组合是一个组合 间隙锁 和 行锁 ，组合的特性就是前开后闭。什么意思呢？假设有 id为 0,5 两条数据作范围查询，锁的数据范围为（0,5] ，即是（0,5）区间 和 id=5 的行锁

临键锁 (Next-Key Lock)

• 间隙锁 与 行锁 组合
• 命中索引的范围查询中，会把记录锁住，并把记录之间的间隙也锁住
• 命中无索引的范围查询，会扫描全表，找到对应的id，在聚簇索引中找到对应字段

3.锁的状态区分

• 意向共享锁
• 意向排他锁

意向锁

innodb是基于行锁实现的，排他锁不能与其他排他锁与共享锁共享资源

• 在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁
• 为了解决避免每次申请锁的时候，都全表扫描索引，是否存在有锁占用资源（//TODO 留坑）

4.加锁原则

• 加锁的基本单位是 next-key lock（间隙锁 + 行锁）。next-key lock 是前开后闭区间。
• 数据库在修改操作时，会加排他锁，查询不会使用锁
• 索引上等值查询时，唯一索引加锁时，next-key lock会降级为行锁
• 索引上范围查询时，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 降级为间隙锁

---

三.快照读 与 当前读

• 快照读（Consistent Read） ，基于 MVCC 和 undo log 来实现的，适用于简单 select 语句
• 当前读（也称锁定读，Locking Read），基于 临键锁（行锁 + 间隙锁）来实现的，适用于 insert，update，delete， select … for update， select … lock in share mode 语句，以及加锁了的 select 语句
• MVCC 并发版本控制，是靠 readView (事务视图) 来实现的。多个 readView 组成 undo log（回滚日志）。

-- 快照读
select * from table where ?; 

-- 当前读
select * from table where ? lock in share mode;  -- 使用共享锁
select * from table where ? for update;  -- 使用排他锁
insert into table values (…); -- 使用排他锁
update table set ? where ?;  -- 使用排他锁
delete from table where ?; -- 使用排他锁

---

四.事务级别

正如上面所说，事务级别是由锁实现的

• READ_UNCOMMITTED 读未提交
  可以读到其他事务未提交的数据。

• READ_COMMITED 读已提交
  一个事务内操作一条数据，可以查询到另一个已提交事务操作同一条数据的最新值。
  （//TODO 该事务级别会产生什么问题？会产生幻读吗？）

• REPEATABLE_READ 可重复读

1. 每个事务只关注自己事务开始查询到的数据值，无论事务查询同一条数据多少次，该数据改了多少次，都只查询到事务开始之前的数据值
2. 是在事务开始的时候生成一个 readView。所以一个事务内的多条查询 sql ，查询同一条数据时，读取到的 readView 都是同一个，那么查询某条数据的值，也是同一个值。
3. MVCC 是一个数据多版本控制，通过readView 来实现，组成 undo log 回滚日志

• SERLALIZABLE 串行化
  将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。

事务隔离级别	脏读	不可重复读	幻读
读未提交 READ_UNCOMMITTED	可能	可能	可能
读已提交(RC) READ_COMMITED	不可能	可能	可能
可重复读(RR) REPEATABLE_READ	不可能	不可能	可能
串行化 SERLALIZABLE	不可能	不可能	不可能

• RC隔离级别，读到别的事务已提交的数据，接着同一数据被删除了，会出现幻读。
• 基于RC的实现逻辑，通过间隙锁实现可重复读（RR）隔离级别

---

五.索引（简单介绍）

---

六.SQL 分析（SQL使用什么锁）

一条简单SQL的分析

以下情况是根据这个事务来分析加锁情况

SQL1：select * from t1 where id = 7;
SQL2：delete from t1 where id = 7;

前提一：id列是不是主键？
前提二：当前系统的隔离级别是什么？
前提三：id列如果不是主键，那么id列上有索引吗？
前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？
前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？

注： 下面的这些组合，我做了一个前提假设，也就是有索引时，执行计划一定会选择使用索引进行过滤 (索引扫描)。但实际情况会复杂很多，真正的执行计划，还是需要根据MySQL输出的为准。

学习目标：当看到这些场景，能够推理出用什么锁就合格了。锁是作用在索引树上的，请记住

• innodb 搜索引擎
• mysql 默认是行锁

组合一：id列是主键，RC隔离级别

• 当条件是主键的时候，直接在主键（聚簇索引）对应的数据上加锁

组合二：id列是二级唯一索引，RC隔离级别

delete from t1 where id = 7

• id 是唯一索引 ，name是主键（聚簇索引），需要加两把锁
• 在唯一索引树上，找到对应的节点 id = 7 加X锁。
• 找到聚簇索引上 name = c 的X锁

组合三：id列是二级非唯一索引，RC隔离级别

• 非唯一索引对应的所有满足SQL查询条件的索引节点，都会被加锁
• 这些记录在主键索引上的节点，也会被加锁

组合四：id列上没有索引，RC隔离级别

• 若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

组合五：id列是主键，RR隔离级别

• 与RC隔离级别 id为主键情况一致
• 等值查询，没有使用间隙锁

组合六：id列是二级唯一索引，RR隔离级别

• 与RC隔离级别 id为唯一索引情况一致
• 等值查询，没有使用间隙锁

组合七：id列是二级非唯一索引，RR隔离级别

• Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 7; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

组合八：id列上没有索引，RR隔离级别

• 在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

组合九：Serializable隔离级别

• Serializable隔离级别，影响的是SQL1：select * from t1 where id = 10; 这条SQL，在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。

---

七.复杂SQL

t1(id ,userid,blogid,pubtime,comment)
RR事务隔离级别
先锁定 where 条件的数据

• where pubtime > 1 and pubtime < 20
• userid = ‘hde’
• comment is not null

查看索引

• id (primary key)
• idx_tl_pu(puptime, userid) 条件中有 pubtime 和 userid，过滤该范围的索引节点加上X锁，并在间隙加上GAP锁

索引下推

• 不使用索引下推，只过滤当前索引的条件复合的数据。
• 使用索引下推，过滤向右所有的索引符合的数据
• 使用索引下推，不需要过滤两次查询对应的记录

(//TODO 不连续的索引条件会使用，会索引下推吗)

---