MySQL事务隔离级别和锁

数据库为了维护事务的几种性质,尤其是一致性和隔离性,一般使用加锁这种方式。同时数据库又是个高并发的应用,同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理能力。所以对于加锁的处理,可以说就是数据库对于事务处理的精髓所在。

事务的基本要素(ACID)

  1. 原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体。

  2. 一致性(Consistency):指事务将数据库从一种状态转变为另一种一致的的状态,事务开始前和结束后,数据库的完整性约束没有被破坏。比如A向B转账,不可能A扣了钱,B却没收到。

  3. 隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账。(注:MySQL 通过锁机制来保证事务的隔离性)

  4. 持久性(Durability):事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。(注:MySQL 使用 redo log 来保证事务的持久性)

事务的并发问题

  1. 脏读:事务 A 读取了事务 B 更新的数据,然后 B 回滚操作,那么 A 读取到的数据是脏数据

  2. 不可重复读:事务 A 多次读取同一数据,事务 B 在事务 A 多次读取的过程中,对数据作了更新并提交,导致事务 A 多次读取同一数据时,结果不一致。

  3. 幻读:系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级,但是系统管理员 B 就在这个时候插入了一条具体分数的记录,当系统管理员 A 改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

不可重复读:侧重于 修改
幻读:侧重于 新增删除。(Phantom Rows:幻影行)
解决不可重复读的问题只需锁住满足条件的行(行锁),解决幻读需要锁表(表锁)

MySQL Reference Manual

事务的四种隔离级别

在数据库操作中,为了有效保证并发读取数据的正确性,提出的事务隔离级别,数据库锁也是为了构建这些隔离级别存在的。

隔离级别脏读(Dirty Read)不可重复读(NonRepeatable Read)幻读(Phantom Read)
未提交读(Read uncommitted)可能可能可能
已提交读(Read committed)不可能可能可能
可重复读(Repeatable read)不可能不可能可能
串行化(Serializable )不可能不可能不可能
READ UNCOMMITTED(读未提交)

该隔离级别的事务会读到其它未提交事务的数据,此现象也称之为脏读。

  1. 新建一个名为 test 的数据库和一张 test 表,写入一条测试数据
1
2
3
4
create database test;
use test;
create table test(id int primary key, age int);
insert into test(id, age) values(1, 18);
  1. 准备两个终端,在此命名为终端 1 和终端 2,分别执行如下 sql ,调整隔离级别为 READ UNCOMMITTED
1
SET @@session.transaction_isolation = 'READ-UNCOMMITTED'; // SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  1. 在终端 1 ,开启一个事务,将 id 为 1 的 age 值更新为 20
1
2
3
begin;
update test set age = 20 where id = 1;
select * from test; -- 此时看到一条 age 为 20 的记录
  1. 在终端 2,开启一个事务后查看表中的数据。
1
2
begin;
select * from test; -- 此时看到一条 age 为 20 的记录

终端 2 读取到了终端 1 中未提交的事务(没有 commit 提交动作),即产生了脏读。如果此时终端 1 的事务因为某种原因回滚,所有的操作都将会被撤销,那终端 2 查询到的数据其实就是脏数据。

  1. 在终端 1 执行 rollback
1
2
rollback;
select * from test; -- 此时看到一条 age 为 18 的记录
  1. 如果此时在终端 2 执行更新语句
1
2
update test set age = age + 2 where id = 1;
select * from test; -- 此时看到一条 age 为 20 的记录

得到的 age 值是 20,而不是 22,因为 mysql 知道其它会话回滚了。要想解决脏读的问题,可以采用读已提交的隔离级别。

READ COMMITTED(读已提交)

一个事务可以读取另一个已提交的事务,多次读取会造成不一样的结果,此现象称为不可重复读问题,是 Oracle 和 SQL Server 的默认隔离级别。

  1. 重置 test 数据库 test 表数据为原始状态后,在终端 1 和 终端 2 分别调整隔离级别为 READ COMMITTED
1
SET @@session.transaction_isolation = 'READ-COMMITTED';// SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  1. 在终端 1,开启一个事务,将 age 为 18 的记录更新为 20,并确认记录数变更过来。
1
2
3
begin;
update test set age = 20 where id = 1;
select * from test; -- 此时看到一条 age 为 20 的记录
  1. 在终端 2,开启一个事务后,查看表中的数据
1
2
begin;
select * from test; -- 此时看到一条 age 为 18 的记录

终端 1 事务还没提交,终端 2 不能查询到 age 已经更新的数据,解决了脏读问题

  1. 在终端 1,提交事务
1
commit;
  1. 切换到终端 2
1
select * from test; -- 此时看到一条 age 为 20 的记录

终端 2 在开启了一个事务之后,在第一次读取 test 表(此时终端 1 的事务还未提交)时 age 为 18,在第二次读取 test 表(此时终端 1 的事务已经提交)时 age 已经变为 20,说明在此隔离级别下已经读取到已提交的事务,然而终端 2 两次读取的结果不一致,即产生了不可重复读的问题。

REPEATABLE READ(可重复读)

该隔离级别是 MySQL 默认的隔离级别,在同一个事务里,select 的结果是事务开始时时间点的状态,因此,同样的 select 操作读到的结果会是一致的,但是,会有幻读现象。MySQL 的 InnoDB 引擎可以通过 next-key locks 机制来避免幻读。

  1. 重置 test 数据库 test 表数据为原始状态后,在终端 1 和 终端 2 分别调整隔离级别为 REPEATABLE-READ
1
SET @@session.transaction_isolation = 'REPEATABLE-READ'; //SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  1. 在终端 1,开启一个事务
1
2
begin;
select * from test; -- 此时只有一条 age 为 18 的记录
  1. 在终端 2,开启一个事务
1
2
begin;
select * from test; -- 此时只有一条 age 为 18 的记录
  1. 切换到终端 1,增加一条记录并提交
1
2
insert into test(id, age) values(2, 30);
commit;
  1. 切换到终端 2
1
select * from test; --此时仍只有一条 age 为 18 的记录

通过这一步可以证明,在该隔离级别下已经读取不到别的已提交的事务,如果想看到终端 1 提交的事务,在终端 2 将当前事务提交后再次查询就可以读取到终端 1 提交的事务了。

  1. 此时接着在终端 2 插入一条数据
1
insert into test(id, age) values(2, 30); -- 报错:ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

也许到这里您心里可能会有疑问,明明在第 5 步没有数据,为什么在这里会报错呢?其实这就是该隔离级别下可能产生的问题,MySQL 称之为幻读。注意我在这里强调的是 MySQL 数据库,Oracle 数据库对于幻读的定义可能有所不同。

幻读的另一种解释:当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(PhantomRow)

SERIALIZABLE(串行化)

在该隔离级别下事务都是串行化执行的,MySQL 数据库的 InnoDB 引擎会给读操作隐式加一把读共享锁,从而避免了脏读、不可重读复读和幻读问题。

  1. 重置 test 数据库 test 表数据为原始状态后,在终端 1 和 终端 2 分别调整隔离级别为 SERIALIZABLE
1
SET @@session.transaction_isolation = 'SERIALIZABLE'; // set session transaction isolation level serializable;
  1. 在终端 1,开启一个事务,并写入一条数据
1
2
begin;
insert into test(id, age) values(3, 30);
  1. 打开终端 2,开启一个事务
1
2
begin;
insert into test(id, age) values(4, 40); // or select * from test;

此时终端 2 会一直卡住,直到超时(除非终端 1 提交事务),报错信息如下:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction,其中超时参数是由 innodb_lock_wait_timeout 控制。在 mysql 中事务隔离级别为 serializable 时会锁表,因此不会出现幻读的情况,这种隔离级别并发性极低,开发中很少会用到。

不要看到 select 就说不会加锁了,在 Serializable 这个级别,还是会加锁的!

InnoDB 和 XtraDB 存储引擎通过多版本并发控制(Multi Version Concurrency Control)解决了幻读的问题,那么 MVCC 是如何解决幻读的呢?

MVCC(多版本并发控制)

MySQL 的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑,它们一般都同时实现了 MVCC。不仅是 MySQL,包括 Oracle、PostgreSQL 等其他数据库系统也都实现了 MVCC,但各自的实现机制不尽相同,因为 MVCC 没有一个统一的实现标准。可以认为MVCC是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低。虽然实现机制有所不同,但大都实现了非阻塞的读操作,写操作也只锁定必要的行。

MVCC 的实现,是通过保存数据在某个时间点的快照来实现的。也就是说,不管需要执行多长时间,每个事务看到的数据都是一致的。根据事务开始的时间不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的。不同存储引擎的 MVCC 实现是不同的,典型的有乐观并发控制控制(乐观锁)和悲观并发控制(悲观锁)。

在 InnoDB 中,是通过在每行记录后面保存两个隐藏的列来实现 MVCC 的。这两个列,一个保存了行的创建时间,一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值,而是系统版本号(system version number),每开始一个新的事务,系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。在可重读 Repeatable Read 事务隔离级别下:

  • SELECT时,读取创建版本号 <= 当前系统版本号,删除版本号为空或 > 当前系统版本号。

  • INSERT时,保存当前系统版本号为行的创建版本号

  • DELETE时,存当前系统版本号为行的删除版本号

  • UPDATE时,插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识。

通过 MVCC,虽然每行记录都需要额外的存储空间,更多的行检查工作以及一些额外的维护工作,但可以减少锁的使用,使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单,性能很好,并且也能保证只会读取到符合需求的行。

MVCC 只在 REPEATABLE-READ 和 READ-COMMITTED 这两个隔离级别下工作。

其他两个隔离级别都和 MVCC 不兼容,因为 READ-UNCOMMITTED 总是读取最新的数据行,而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

MySQL 中的锁

MySQL中锁的种类很多,常见的有表锁和行锁,也有新加入的 Metadata Lock 等等,表锁是对一整张表加锁,虽然可分为读锁和写锁,但毕竟是锁住整张表,会导致并发能力下降,一般是做 ddl 处理时使用。行锁则是锁住数据行,这种加锁方法比较复杂,由于只锁住有限的数据,对于其它数据不加限制,所以并发能力强,MySQL 一般都是用行锁来处理并发事务。这里主要讨论的也就是行锁。

一次封锁 or 两段锁?

因为有大量的并发访问,为了预防死锁,一般应用中推荐使用一次封锁法,就是在方法的开始阶段,已经预先知道会用到哪些数据,然后全部锁住,在方法运行之后,再全部解锁。这种方式可以有效的避免循环死锁,但在数据库中却不适用,因为在事务开始阶段,数据库并不知道会用到哪些数据。

数据库遵循的是两段锁协议,将事务分成两个阶段,加锁阶段和解锁阶段(所以叫两段锁

  • 加锁阶段:在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁(共享锁,其它事务可以继续加共享锁,但不能加排它锁),在进行写操作之前要申请并获得X锁(排它锁,其它事务不能再获得任何锁)。加锁不成功,则事务进入等待状态,直到加锁成功才继续执行。

  • 解锁阶段:当事务释放了一个封锁以后,事务进入解锁阶段,在该阶段只能进行解锁操作不能再进行加锁操作。

事务加锁/解锁处理
begin;
insert into test …..加 insert 对应的锁
update test set…加 update 对应的锁
delete from test ….加 delete 对应的锁
commit;事务提交时,同时释放 insert、update、delete 对应的锁

虽然无法避免死锁,但是两段锁协议可以保证事务的并发调度是串行化(串行化很重要,尤其是在数据恢复和备份的时候)的。

Next-Key 锁

在 REPEATABLE-READ 级别中,通过 MVCC 机制,虽然让数据变得可重复读,但我们读到的数据可能是历史数据,不是数据库当前实时的数据!这在一些对于数据的时效特别敏感的业务中,就很可能出问题。

对于这种读取历史数据的方式,我们叫它快照读 (snapshot read),而读取数据库当前版本数据的方式,叫当前读 (current read)。很显然,在 MVCC 中:

  • 快照读:就是 select

    • select * from table … ;
  • 当前读:特殊的读操作,插入/更新/删除操作,属于当前读,处理的都是当前的数据,需要加锁。

    • select * from table where ? lock in share mode;
    • select * from table where ? for update;
    • insert …;
    • update …;
    • delete …;

事务的隔离级别实际上都是定义了“当前读”的级别,MySQL 为了减少锁处理(包括等待其它锁)的时间,提升并发能力,引入了快照读的概念,使得 select 不用加锁。而 update、insert 这些“当前读”,就需要另外的模块来解决了。

  • Record Locks(也称行锁)

    • 该锁为索引记录上的锁,如果表中没有定义索引,InnoDB 会默认为该表创建一个隐藏的聚簇索引,并使用该索引锁定记录。
  • Gap Locks

    • 该锁会锁定一个范围,但是不括记录本身。可以通过修改隔离级别为 READ COMMITTED 或者配置 innodb_locks_unsafe_for_binlog 参数为 ON。
  • Next-key Locks

    • 是 Record Locks 和 Gap Locks 的组合,即锁定一个范围并且锁定该记录本身。

Gap Locks:如果没有使用索引,那么会给全表加入 gap 锁。它不能像 Record Locks 那样经过MySQL Server 过滤自动解除不满足条件的锁。因为没有索引,则这些字段也就没有排序,也就没有区间。除非该事务提交,否则其它事务无法插入任何数据。

Next-key Locks:如果索引有唯一属性,则 InnnoDB 会自动将 Next-key Locks 降级为 Record Locks。举个例子,如果一个索引有 1, 3, 5 三个值,则该索引锁定的区间为 (-∞,1], (1,3], (3,5], (5,+ ∞)

Record Locks 防止别的事务修改或删除,GAP 锁防止别的事务新增,Record Locks 和 GAP 锁结合形成的的 Next-Key 锁共同解决了 RR 级别在写数据时的幻读问题。

死锁

死锁是指两个或两个以上的进程在执行过程中,由于竞争资源或者由于彼此通信而造成的一种阻塞的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。InnoDB 引擎采取的是 wait-for graph 等待图的方法来自动检测死锁,如果发现死锁会自动回滚一个事务。

还是以 test 表中的数据来演示:

  1. 在终端 1,开启一个事务,手动给 id 为 1 的记录加 X 锁
1
2
begin;
select * from test where id = 1 for update;
  1. 在终端 2,开启一个事务,手动给 id 为 2 的记录加 X 锁
1
2
begin;
select * from test where id = 2 for update;
  1. 切换到终端 1,手动给 id 为 2 的记录加 X 锁,此时会一直卡住,因为此时在等待第 2 步中 X 锁的释放,直到超时,超时时间由 innodb_lock_wait_timeout 控制。
1
select * from test where id = 2 for update;
  1. 在锁超时前立刻切换到终端 2,手动给 id 为 1 的记录加 X 锁,此时又会等待第 1 步中 X 所的释放,两个终端都在等待资源的释放,所以 InnoDB 引擎会立马检测到死锁产生,自动回滚一个事务,以防止死锁一直占用资源。
1
2
select * from test where id = 1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

此时,通过 show engine innodb status\G 命令可以看到 LATEST DETECTED DEADLOCK 相关信息,即表明有死锁发生;或者通过配置 innodb_print_all_deadlocks(MySQL 5.6.2 版本开始提供)参数为 ON 将死锁相关信息打印到 MySQL 的错误日志。

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
32
33
34
35
36
37
38
39
40
41
42

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-10-08 15:54:49 0x70000be86000
*** (1) TRANSACTION:
TRANSACTION 11824, ACTIVE 51 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3177, OS thread handle 123145500876800, query id 1847 localhost 127.0.0.1 root statistics
select * from test where id = 2 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 83 page no 4 n bits 80 index PRIMARY of table `test1`.`test` trx id 11824 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000002df4; asc - ;;
2: len 7; hex 81000000bd0110; asc ;;
3: len 4; hex 8000001e; asc ;;

*** (2) TRANSACTION:
TRANSACTION 11825, ACTIVE 30 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3178, OS thread handle 123145502089216, query id 1851 localhost 127.0.0.1 root statistics
select * from test where id = 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 83 page no 4 n bits 80 index PRIMARY of table `test1`.`test` trx id 11825 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000002df4; asc - ;;
2: len 7; hex 81000000bd0110; asc ;;
3: len 4; hex 8000001e; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 83 page no 4 n bits 80 index PRIMARY of table `test1`.`test` trx id 11825 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000002de3; asc - ;;
2: len 7; hex 010000017402f8; asc t ;;
3: len 4; hex 80000012; asc ;;

*** WE ROLL BACK TRANSACTION (2)
锁的优化建议

如果利用不好,会给业务造成大量的卡顿现象,在了解了锁相关的一些知识点后,我们可以有意识的去避免锁带来的一些问题。

  1. 合理设计索引,让 InnoDB 在索引键上面加锁的时候尽可能准确,尽可能的缩小锁定范围,避免造成不必要的锁定而影响其他 Query 的执行。

  2. 尽可能减少基于范围的数据检索过滤条件,避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。

  3. 尽量控制事务的大小,减少锁定的资源量和锁定时间长度。

  4. 在业务环境允许的情况下,尽量使用较低级别的事务隔离,以减少 MySQL 因为实现事务隔离级别所带来的附加成本。

参考文章

InnoDB Multi-Versioning
高性能MySQL
MySQL的四种事务隔离级别
MySQL 事务隔离级别和锁
Innodb中的事务隔离级别和锁的关系

本文标题:MySQL事务隔离级别和锁

文章作者:北宸

发布时间:2019年10月07日 - 18:00:06

最后更新:2019年10月08日 - 23:03:15

原始链接:https://leafjame.github.io/posts/2408076579.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

-------------------本文结束 感谢您的阅读-------------------