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）是个行级锁的变种，它在普通读情况下避免了加锁操作，因此开销更低。即下面这个没有读锁也没有写锁
快照读，不加锁
| 1 | select * from table ... |
当前读，select 语句可以指定读锁和写锁，如下
说明，insert, update, delete 也是当前读，理由如下：
1.update和delete操作流程分解：
首先通过where条件查询到第一个满足的记录，并加锁
对这条记录进行更新，再读取下一条记录
对记录更新，继续读下一条直到完毕
2.insert操作流程分解：
unique key 冲突检测，会有一个当前读
无冲突时，插入
b. sql实例分析
在分析上面的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代码如下:
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为主键
实际执行演示:
b. 写锁的影响
实际执行演示:
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 | 不可能 | 不可能 | 不可能 | 有 |
使用姿势：
| 1 2 3 4 5 | start transaction; -- xxx 具体的sql commit; |
IV. 其他
参考
深入理解Mysql——锁、事务与并发控制
MySQL 加锁处理分析
个人博客： 一灰灰Blog
基于hexo + github pages搭建的个人博客，记录所有学习和工作中的博文，欢迎大家前去逛逛
声明
尽信书则不如，已上内容，纯属一家之言，因本人能力一般，见识有限，如发现bug或者有更好的建议，随时欢迎批评指正
微博地址: 小灰灰Blog
QQ： 一灰灰/3302797840