锁
本文针对 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 可重复读
- 每个事务只关注自己事务开始查询到的数据值,无论事务查询同一条数据多少次,该数据改了多少次,都只查询到事务开始之前的数据值
- 是在事务开始的时候生成一个 readView。所以一个事务内的多条查询 sql ,查询同一条数据时,读取到的 readView 都是同一个,那么查询某条数据的值,也是同一个值。
- 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 不连续的索引条件会使用,会索引下推吗)