mysql之锁与事务详解

文章目录
  1. Mysql之锁与事务
    1. I. 锁
      1. 1. 共享锁和排它锁
        1. a. 共享锁
        2. b. 排它锁
        3. c. gapLock 和 next key lock
      2. 2. 表锁和行锁
      3. 3. 如何使用锁
        1. a. select分析
        2. b. sql实例分析
    2. II. 事务
      1. 1. 定义
      2. 2. ACID特性
        1. a. A:atomiciy 原子性
        2. b. C:consistency一致性
        3. c. I:isolation 隔离性
        4. d. D:durability 持久性
      3. 3. 隔离级别
        1. a. 基本概念
        2. b. RU: Read Uncommited 未提交读
        3. c. RC: Read Commited 提交读
        4. d. RR: Repeatable Read 可重复度
        5. e. Serializable 可串行化
        6. f. 常用命令
      4. 4. 使用姿势
        1. a. 读锁的影响
        2. b. 写锁的影响
        3. c. 小结
    3. III. 小结
      1. 1. sql分析
      2. 2. 事务
    4. IV. 其他
      1. 参考
      2. 个人博客: 一灰灰Blog
      3. 声明
      4. 扫描关注

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

# Mysql

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×