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

最后更新:2023年08月19日 - 13:26:00

原始链接:https://www.liaofuzhan.com/posts/2408076579.html

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

-------------------本文结束 感谢您的阅读-------------------
首页 前进 回顶 刷新 收藏 后退
🌞