Mysql之锁与事务

平时的业务中,顶多也就是写写简单的sql,连事务都用的少,对锁这一块的了解就更加欠缺了,之前一个大神分享了下mysql的事务隔离级别,感觉挺有意思的,正好发现一个很棒的博文,然后也收集了一些相关知识,正好来学习下,mysql中锁与事务的神秘面纱,主要内容包括

  1. 共享锁和排它锁的区别以及适合范围
  2. mysql的表锁和行锁的区别
  3. 怎么判断一个sql是否执行了锁,执行的是表锁还是行锁
  4. 事务是什么,怎么用
  5. 事务的特性ACID
  6. 事务的隔离级别 (RU, RC, RR, SER)
  7. 如何查看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 语句可以指定读锁和写锁,如下

1
2
3
4
5
-- 读锁
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实例分析

1
2
3
4
5
--- 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这一行

180323_LOCK2.jpg

case2: 唯一索引+rc级别

  • sql2加写锁,如下图的case,就两把锁,一个对应于id unique索引上的id = 10的记录,另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录

180323_LOCK3.jpg

case3: id非唯一索引+RC

  • sql2加写锁,如下图的case,会有四个写锁

180323_LOCK4.jpg

case4: 无索引+RC

  • sql2分析:若id列上没有索引,SQL会走聚簇索引的全扫描进行过滤,由于过滤是由MySQL Server层面进行的。因此每条记录,无论是否满足条件,都会被加上写锁(X锁)。
  • 但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省

180323_LOCK5.jpg

case5: 主键+RR

加锁同case1

case6: 唯一索引+RR

加锁同case2

case7: 非唯一索引+RR

RR级别不允许出现幻读,简单来说,在加锁的过程中,不允许在新增or修改满足条件的记录

即下图中,除了图三中类似的x锁之外,还会新增一个gap锁,这个gap锁主要确保那几个位置上不能插入新的记录

180323_LOCK6.jpg

case8: 无索引+RR

  • 在Repeatable Read隔离级别下,如果进行全表扫描的当前读,那么会锁上表中的所有记录,同时会锁上聚簇索引内的所有GAP,杜绝所有的并发 更新/删除/插入 操作

180323_LOCK7.jpg

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,两次执行的结果不同

180323_LOCK8.gif

相关的sql代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 设置会话隔离级别
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,返回的结果都是相同的

180323_LOCK9.gif

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 会话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;

实际执行演示:

180323_LOCK10.gif

b. 写锁的影响

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 会话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;

实际执行演示:

180323_LOCK11.gif

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. 其他

参考

个人博客: 一灰灰Blog

基于hexo + github pages搭建的个人博客,记录所有学习和工作中的博文,欢迎大家前去逛逛

声明

尽信书则不如,已上内容,纯属一家之言,因本人能力一般,见识有限,如发现bug或者有更好的建议,随时欢迎批评指正

扫描关注

QrCode