mysql 之高性能索引
Mysql 之锁与事务
平时的业务中, 顶多也就是写写简单的 sql, 连事务都用的少, 对锁这一块的了解就更加欠缺了, 之前一个大神分享了下 mysql 的事务隔离级别, 感觉挺有意思的, 正好发现一个很棒的博文, 然后也收集了一些相关知识, 正好来学习下, mysql 中锁与事务的神秘面纱, 主要内容包括
共享锁和排它锁的区别以及适合范围
mysql 的表锁和行锁的区别
怎么判断一个 sql 是否执行了锁, 执行的是表锁还是行锁
事务是什么, 怎么用
事务的特性 ACID
事务的隔离级别 (RU, RC, RR, SER)
如何查看 mysql 使用的隔离级别
I. 锁
在学习多线程时, 我们也经常会遇到锁这个东西, 那个时候谈的比较多的是乐观锁和悲观锁, 那这两种锁和 DB 中常说的共享锁和独占锁有什么区别呢? 先给出我们已知的乐观锁和悲观锁定义
乐观锁: 多线程中的 CAS 就是一种乐观锁, 实际上不加锁, 先尝试去执行, 如果失败则重试(或者根据失败策略进行处理)
悲观锁: 上锁, 一次只能有一个线程访问, 其他的都只能等待
1. 共享锁和排它锁
a. 共享锁
突出在共享这个关键词上, 顾名思义, 表示这个锁可以多人共享, 一般又可以称为读锁(S 锁)
在 DB 中, 读锁表示所有的读取数据的小伙伴都不会被锁阻塞, 可以放心大胆的获取数据, 专业一点的说法就是同一时刻, 允许多个连接并发的读取同一资源
b. 排它锁
排它, 表示当某个人持有这个锁之后, 其他的人再来竞争锁就会失败, 只能等待锁释放, 又称为写锁(X 锁)
在 DB 中, 写锁表示同一时刻, 只能有一个小伙伴操作, 其他的不管是读还是写, 都得排队, 专业说法是写锁会阻塞其他的读锁或写锁请求, 确保同一时刻只能有一个连接可以写入资源, 并防止其他连接读取或者写资源
c. gapLock 和 next key lock
next key lock 主要是范围匹配的场景下, 会锁某一个范围区间
gapLock 主要用来锁边界
如下面的 case(说明, columnA 是非唯一索引, RR 隔离级别)
where columnA between 10 and 30
, next key lock 确保不会在 10, 30 之内插入新的数据行
where columnA = 10, gap lock 确保不会再次插入一个 columnA=10 的行
2. 表锁和行锁
对于 DB 的操作, 通常会出现两种情况, 一个是锁表, 一个锁行
表锁: 表示整个表被某一个连接占用了写锁, 导致其他连接的读锁或者写锁都会阻塞; 影响整个表的读写
行锁: 表示表中某些行被某个连接占用了写锁, 但是其他行, 依然可以被其他的连接请求读锁写锁; 仅影响被锁的那些行数据
那么一个问题就来了, 什么 sql 会导致行锁, 什么会导致写锁? 甚至我们如何判断一个 sql 是否会请求锁, 请求的是读锁还是写锁呢?
3. 如何使用锁
上面一节抛出了问题, 那么现在就是来看下如何使用和分析锁了, 首先我们是我们最常见的几个 sql
- select
- update
- delete
- insert
其中很容易得出的结论是
update, delete, insert
三个涉及到写锁; 而且这种操作绝大部分的场景是操作具体的某些行(想想为什么?), 所以更常见的是行锁
select 读操作则有点特殊
a. select 分析
MVCC(multiple-version-concurrency-control)是个行级锁的变种, 它在普通读情况下避免了加锁操作, 因此开销更低即下面这个没有读锁也没有写锁
快照读, 不加锁
select * from table ...
当前读, select 语句可以指定读锁和写锁, 如下
-- 读锁
select * from table lock in share mode;
-- 写锁
select * from table for update;
说明, insert, update, delete 也是当前读, 理由如下:
1.update 和 delete 操作流程分解:
首先通过 where 条件查询到第一个满足的记录, 并加锁
对这条记录进行更新, 再读取下一条记录
对记录更新, 继续读下一条直到完毕
2.insert 操作流程分解:
unique key 冲突检测, 会有一个当前读
无冲突时, 插入
b. sql 实例分析
- --- SQL1:
- select * from t1 where id = 10;
- --- SQL2:
- delete from t1 where id = 10;
在分析上面的 sql 之前, 需要明确几个前提:
id 是否为主键(id 是否有索引)
系统的隔离级别(隔离级别是什么东西可以先看下下文介绍)
分别说明:
case1: 主键 + RC 级别
sql1 不加锁, MySQL 是使用多版本并发控制的, 读不加锁
sql2 加写锁(即 X 锁), 只锁 id=10 这一行
case2: 唯一索引 + rc 级别
sql2 加写锁, 如下图的 case, 就两把锁, 一个对应于 id unique 索引上的 id = 10 的记录, 另一把锁对应于聚簇索引上的 [name=d,id=10] 的记录
case3: id 非唯一索引 + RC
sql2 加写锁, 如下图的 case, 会有四个写锁
case4: 无索引 + RC
sql2 分析: 若 id 列上没有索引, SQL 会走聚簇索引的全扫描进行过滤, 由于过滤是由 MySQL Server 层面进行的因此每条记录, 无论是否满足条件, 都会被加上写锁(X 锁)
但是, 为了效率考量, MySQL 做了优化, 对于不满足条件的记录, 会在判断后放锁, 最终持有的, 是满足条件的记录上的锁, 但是不满足条件的记录上的加锁 / 放锁动作不会省
case5: 主键 + RR
加锁同 case1
case6: 唯一索引 + RR
加锁同 case2
case7: 非唯一索引 + RR
RR 级别不允许出现幻读, 简单来说, 在加锁的过程中, 不允许在新增 or 修改满足条件的记录
即下图中, 除了图三中类似的 x 锁之外, 还会新增一个 gap 锁, 这个 gap 锁主要确保那几个位置上不能插入新的记录
case8: 无索引 + RR
在 Repeatable Read 隔离级别下, 如果进行全表扫描的当前读, 那么会锁上表中的所有记录, 同时会锁上聚簇索引内的所有 GAP, 杜绝所有的并发 更新 / 删除 / 插入 操作
case9: Serializable 级别
sql2: Serializable 隔离级别对于 SQL2:delete from t1 where id = 10; 来说, Serializable 隔离级别与 Repeatable Read 隔离级别完全一致
SQL1: 在 RC,RR 隔离级别下, 都是快照读, 不加锁但是在 Serializable 隔离级别, SQL1 会加读锁, 也就是说快照读不复存在, MVCC 并发控制降级为 Lock-Based CC
II. 事务
事务可谓是 db 中非常重要的一个知识点了, 接下来我们的目标就是弄懂什么是事务, 怎么使用事务, 以及事务与锁之间的关联是怎样的
说明: 本文的分析主要是以 mysql 的 innordb 存储引擎为标准
1. 定义
事务就是一组原子性的 sql, 或者说一个独立的工作单元
事务就是说, 要么 mysql 引擎会全部执行这一组 sql 语句, 要么全部都不执行(比如其中一条语句失败的话)
2. ACID 特性
a. A:atomiciy 原子性
一个事务必须保证其中的操作要么全部执行, 要么全部回滚, 不可能存在只执行了一部分这种情况出现
b. C:consistency 一致性
数据必须保证从一种一致性的状态转换为另一种一致性状态
c. I:isolation 隔离性
在一个事务未执行完毕时, 通常会保证其他 Session 无法看到这个事务的执行结果
d. D:durability 持久性
事务一旦 commit, 则数据就会保存下来, 即使提交完之后系统崩溃, 数据也不会丢失
3. 隔离级别
前面在分析锁的 sql 时, 就提到了隔离级别, 通常有四种: RU, RC, RR, Serializable
在说明这个之前, 先了解几个概念
a. 基本概念
脏读: 读取到一个事务未提交的数据, 因为这个事务最终无法保证一定执行成功, 那么读取到的数据就无法保证一定准确
不可重复读: 简单来说就是在一个事务中读取的数据可能产生变化, 同样的 sql, 在一个事务中执行多次, 可能得到不同的结果
幻读: 会话 T1 事务中执行一次查询, 然后会话 T2 新插入一行记录, 这行记录恰好可以满足 T1 所使用的查询的条件然后 T1 又使用相同 的查询再次对表进行检索, 但是此时却看到了事务 T2 刚才插入的新行
加锁读:
select * from table ...
的执行是否加了读锁 (这个可以参考上面的 sql 加锁分析)
b. RU: Read Uncommited 未提交读
事务中的修改, 即使没有提交, 对其他会话也是可见的, 即表示可能出现脏读, 一般数据库都不采用这种方案
c. RC: Read Commited 提交读
这个隔离级别保证了一个事务如果没有完全成功(commit 执行完), 事务中的操作对其他会话是不可见的, 避免了脏读的可能
但是可能出现不可重复度的情况, 举例说明:
会话 T1, 执行查询
select * from where id=1
, 第一次返回一个结果
会话 T2, 执行修改
update table set updated=xxx where id=1
并提交
会话 T1, 再次执行查询
select * from where id=1
, 这次返回的结果中 update 字段就和前面的不一样了
实际的生产环境中, 这个级别用的比较多, 特意查了下公司的 db 隔离级别就是这个
一个 RC 级别的演示过程:
会话 1, 开启事务, 查询
会话 2, 开启事务, 更新 DB, 提交事务
会话 1, 再次查询, 提交事务
从下面的实际演示结果可以知道, 会话 1, 同一个 sql, 两次执行的结果不同
相关的 sql 代码如下:
-- 设置会话隔离级别
set session transaction ioslation read commited;
-- 查看当前会话隔离级别
select @@tx_isolation;
-- 会话 1 的操作
- start transaction;
- select * from newuser where userId=1;
-- 会话 2 开始操作
- start transaction;
- select * from newuser where userId=1;
- update newuser set updated=1521786092 where userId=1;
- select * from newuser where userId=1;
- commit;
-- 再次进入会话 1, 同样执行上次的 sql, 对比两次输出结果
select * from newuser where userId=1;
-- 注意观察, 会话 1, 前后两次这个 sql 的输出结果, 特别是 updated 字段
-- 正常情况会如上面的 demo 图, 会发生改变
-- 关闭会话
commit;
-- 再次查询
select * from newuser where userId=1;
d. RR: Repeatable Read 可重复度
一个事务中多次执行统一读 SQL, 返回结果一样 这个隔离级别解决了脏读的问题, 幻读问题
实例演示解决脏读的过程(将上面的过程同样来一次)
发现不管会话 1 同一个 sql, 返回的结果都是相同的
e. Serializable 可串行化
最强的隔离级别, 通过给事务中每次读取的行加锁, 写加写锁, 保证不产生幻读问题, 但是会导致大量超时以及锁争用问题
f. 常用命令
查看当前会话隔离级别:
select @@tx_isolation
查看系统当前隔离级别:
select @@global.tx_isolation
设置当前会话隔离级别:
set session transaction isolation level read committed;
设置系统当前隔离级别:
set global transaction isolation level read committed;
命令行,
开始事务:
start transactioin;
提交: commit;
4. 使用姿势
前面演示事务隔离级别的时候, 给出的实例就演示了事务的使用姿势, 一般作为三步骤:
开始事务 start transaction;
执行你的业务 sql
提交事务 commit;
我们现在演示以下一个事务中, 读锁写锁对另一个事务的影响
a. 读锁的影响
我们采用 mysql 默认的 RR 级别进行测试, userId 为主键
-- 会话 1
- start transaction;
- select * from newuser where userId=1 lock in share mode;
-- 转入会话 2
start transaction;
select * from newuser where userId=1; -- 会输出
select * from newuser where userId=1 lock in share mode; -- 会输出
update newuser set updated=1521787137 where userId=1; -- 会挂起
-- 转入会话 1
-- 提交, 此时观察会话 2 的写是否完成
commit;
-- 转入会话 2
commit;
实际执行演示:
b. 写锁的影响
-- 会话 1
- start transaction;
- select * from newuser where userId=1 for update;
-- 转入会话 2
start transaction;
select * from newuser where userId=1; -- 会输出
select * from newuser where userId=1 lock in share mode; -- 会挂住
-- update newuser set updated=1521787137 where userId=1; -- 会挂住
-- 转入会话 1
-- 提交, 此时观察会话 2 的写是否完成
commit;
-- 转入会话 2
commit;
实际执行演示:
c. 小结
读锁, 会阻塞其他请求写锁的 sql 执行
写锁, 会阻塞其他读锁和写锁的 sql 执行
事务只有在提交之后, 才会释放锁
额外注意, 上面事务在提交之后才会释放锁, 因此如果两个事务循环依赖锁时, 可能发生死锁
III. 小结
锁和事务可谓是 db 中非常重要的知识点了, 在我们实际的编码过程中(一般针对 mysql, innordb 存储引擎, rr 隔离级别), 做出下面的一些总结
1. sql 分析
- select * from table where xxx;
- (读快照, 一般不加锁)
- select * from table where xxx lock in share mode;
- (读锁, 会阻塞其他的写锁请求, 但其他的读锁请求没有影响)
- select * from table where xxx for update;
- (写锁, 会阻塞其他的读写请求)
- update tableName set xxx
- (写锁)
- insert (写锁)
- delete (写锁)
2. 事务
简单来讲, 事务就是一组 sql, 要么全部执行成功, 要么全部失败
四个特性: A(原子性)C(一致性)I(隔离性)D (持久性)
四种隔离级别:(mysql 默认采用的是 RR 级别)
隔离级别 | 脏读 | 不可重复读 | 幻读 | 加锁读 |
---|---|---|---|---|
read uncommited | 可能 | 可能 | 可能 | 无 |
read commited | 不可能 | 可能 | 可能 | 无 |
repeatable read | 不可能 | 不可能 | 不可能 | 无 |
serializable | 不可能 | 不可能 | 不可能 | 有 |
使用姿势:
start transaction;
-- xxx 具体的 sql
commit;
IV. 其他
参考
深入理解 Mysql 锁事务与并发控制
MySQL 加锁处理分析
个人博客: 一灰灰 Blog
基于 hexo + github pages 搭建的个人博客, 记录所有学习和工作中的博文, 欢迎大家前去逛逛
来源: https://juejin.im/post/5ab5e44a6fb9a028c97a013d