事务的隔离级别与锁实践

vlambda
2020-08-27

事务的隔离级别与锁实践

TOC

1. 事务特性

1.1 Atomicity 原子性

1.2 Consistency 一致性

1.3 Isolation 隔离性

1.4 Durability 持久性

2. 事务并发常见的问题

2.1 Dirty reads 脏读

2.2 Non-repeatable reads 不可重复读

2.3 Phantom reads 幻读

3. 怎么解决

4. 事务的隔离级别

4.1 定义

4.2 特点

4.3 隔离级别分类

4.4 隔离级别 Vs 读现象

5. MySQL 相关配置

5.1 隔离级别设置

5.2 事务锁超时设置

6. 实践 MySQL 锁

6.1 Read uncommitted 隔离级别

6.2 RC 隔离级别

6.3 RR 隔离级别

7. 参考

1. 事务特性

事务有四个特性，简称 ACID，如下：

1.1 Atomicity 原子性

事务通常由多个语句组成，原子性保证了每个事务都被视为独立的单元，要么全都执行成功，要么全都执行失败；如果一个事务中的部分语句执行失败了，那么整个事务都是失败的，且数据库不受任何影响。

举个例子：银行资金转账，从账户 A 到账户 B，包含了两个操作：从账户 A 转出资金，然后保存到账户 B。一个事务执行这些操作，要确保数据库最终会保持在一个一致性的状态，也就是说，如果这两个操作任意一个失败，那么账户 A 的钱不会减少，账户 B 的钱不会增多。

1.2 Consistency 一致性

一致性确保一个事务仅能将数据库从一个有效状态带到另一个有效状态，保持数据库的不变性：任意写入数据库的数据都要确保一定是有效的，也就是遵守已经定义的规则：约束、级联、触发器以及这些相互组合的规则。

举个例子：账户 A、账户 B 发生次的转账操作，事务结束之后，两个人账户的总金额不会变，和转账操作之前是一样的。

1.3 Isolation 隔离性

通常多个事务会并发的执行（如：多个事务在同一时间读写同一张表）。隔离性确保了多个并发事务的执行结果，对数据库带来的影响，和这些事务按顺序、串行地执行带来的结果一样。隔离性的主要目的就是并发控制。一个不完整的事务带来的影响，对其他事务是不可见的。

举个例子：很多账户同时向账户 A 转账，给账户 A 带来的影响，就像是这些账户按顺序依次给其转账一样。

在 MySQL 中，是通过锁（locking）的机制来保证隔离性。

1.4 Durability 持久性

持久性确保一旦一个事务提交了，就会永久存储其对数据库的带来的影响（如更新、添加数据等），即使出现了系统故障（停电、系统崩溃等），也不会丢失。

2. 事务并发常见的问题

2.1 Dirty reads 脏读

脏读指的是：允许一个事务读取另一个事务正在更改的行数据，而这个更改操作对应的事务却没有提交。

举个例子：账户 a1 当余额为200元，事务 A：给账户 a1 进行存钱操作，存款金额为：500 元；此时，事务 B：读取了此时账户 a1 余额为 700元；然后，事务 A 操作存款失败回滚了；显然，事务 B 读取的是脏数据。

事务 A	事务 B
balance(a1) = 200
deposit(a1): balance(a1) += 500
	balance(a1) = 700
rollback(deposit(a1))
balance(a1) = 200

2.2 Non-repeatable reads 不可重复读

不可重复指的是：在一个事务处理过程中，对同一行数据前后两次读取的结果不一致。和脏读的区别：

•脏读：读取的是其他事务「更改」且「未提交」的数据；•不可重复读：前后两次读取数据结果不一致，在第二次读取之前，数据被另一个事务「更改」且「提交」了

举个例子：账户 a1 当余额为 200 元，事务 A：此时读取 a1 余额为 200 元；接着，事务 B 进行存钱操作并成功提交，存款金额为 500元。此时，事务 A 再读取 a1 余额为 700 元；前后读取的两次余额结果不一致。

事务 A	事务 B
balance(a1) = 200
	deposit(a1): balance(a1) += 500
	commit(deposit(a1))
balance(a1) = 700

2.3 Phantom reads 幻读

有时也称作虚读；幻读指的是：当一个事务对某一范围的数据进行读取时，另一个事务对此范围的数据做了「添加」或者「删除」操作，导致前后读取的「数据集」不一致的现象。和不可重复读的区别：

•二者均是读取了其他事务已经提交的数据；•不可重复读：一般针对某一项数据，或者是确定的某一行数据；•幻读：一般是针对某一批数据

举例：

•当事务 A 更改一批黑名单账户的状态，由冻结更改为正常；但在此时此刻，事务 B 新增加了一个黑名单账户，状态为冻结，且成功提交入库；稍后事务 A 查看刚刚修改的数据后，发现有一账户状态还是冻结状态。•当事务 A 查询状态为冻结的账户列表，得到结果集 r1；但在此时此刻，事务 B 新增加了一个冻结账户，且成功提交入库；稍后事务 A 再次查询状态为冻结的账户列表，发现结果集是 r2，和 r1 不一致。

事务 A	事务 B
queryBlockedAccount: r1=(id=1,2,3,4)
	addBlockedAccount(id=5)
	commit(addBlockedAccount(id=5))
queryBlockedAccount: r1=(id=1,2,3,4,5)

3. 怎么解决

事务并发等造成的脏读、不可重复读、幻读问题是存在的；这些问题是否要彻底的解决掉，又要依赖于不同的事务隔离级别的选择。

4. 事务的隔离级别

4.1 定义

事务的隔离性即 ACID 中的 I；

4.2 特点

•事务具有隔离性，理论上来说，事务之间的执行不应该产生相互影响，其对数据库的影响应该和它们串行执行的结果一样•完全的隔离性，很大程度上会降低系统的并发性能，降低资源的利用率，因而在实际的应用中，会对隔离性有所放宽，这也在一定程度上，会降低数据库系统的一致性。

4.3 隔离级别分类

SQL 标准为事务定义了不同的隔离级别，参考：

•https://en.wikipedia.org/wiki/Isolation_(database_systems)•https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

隔离级别 isolation levels，参照 ansi/iso SQL 标准，从低到高依次有以下四种隔离级别：

•Read uncommitted（读未提交）最低的隔离级别；允许脏读，因此，一个事务可以看到其他事务未提交的更新。•Read committed（读已提交，RC）能够保证在任何时刻读到的数据，都是已经成功提交过的。不会读到中间的、未提交的脏数据，消除了脏读。是 Oracle 数据库的默认隔离级别。•Repeatable reads (可重复读，RR) 可以消除不可重复读。MySQL 默认的隔离级别。•Serializable（串行化）最高的隔离级别；可以消除脏读、不可重复读、幻读

4.4 隔离级别 Vs 读现象

隔离级别	脏读	不可重复读	幻读
读未提交	-	-	-
读已提交	+	-	-
可重复读	+	+	-
串行化	+	+	+
注：

+ 不可能出现 - 可能出现

5. MySQL 相关配置

5.1 隔离级别设置

 mysql> select @@tx_isolation; -- 当前 session 隔离级别 +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.01 sec) mysql> select @@global.tx_isolation; -- 系统全局 隔离级别 +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set (0.00 sec) mysql> set session transaction isolation level read committed; -- 更改 session 的隔离级别 Query OK, 0 rows affected (0.00 sec)

5.2 事务锁超时设置

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 全局事务锁超时时间 300s+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 300 |+--------------------------+-------+1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; -- session 事务锁超时时间 300s+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 300 |+--------------------------+-------+1 row in set (0.00 sec)
mysql> SET innodb_lock_wait_timeout=10; -- 更改session 事务锁超时时间为 10sQuery OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 再次查看 session 事务锁超时时间，已经更改为 10s+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 10 |+--------------------------+-------+1 row in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 全局的事务锁超时时间不变+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 300 |+--------------------------+-------+1 row in set (0.00 sec)

6. 实践 MySQL 锁

MySQL 有多种存储引擎，有 MyISAM、InnoDB、Memory、Archive 等，其中日常我们大部分都只讨论下属 MyISAM、InnoDB 这两种引擎：

•MyISAM

•不支持事务•支持表级锁（table-level locking），不适合高并发的场景

•InnoDB

• MySQL 默认的存储引擎• 支持事务（ACID 特性）• 支持行级锁（row-level locking），因而并发能力高

这里我们仅讨论基于支持事务的 InnoDB 的锁。不同的隔离级，锁的情况是不一样的。

6.1 Read uncommitted 隔离级别

读未提交，此隔离级别下，是没有任何锁可言的。无加锁、释放锁等性能开销，虽然性能好，但是三种常见读问题无法避免（脏读、不可重复读、幻读），无讨论必要性。

6.2 RC 隔离级别

Read committed （读已提交）隔离级别，简称 RC 隔离级别。在 RC 隔离级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除都是要加锁的。测试如下：

mysql -h 172.17.11.134 -u astro_basis -p 4C0hr+20b99s0smysql> use astro_basismysql> select @@tx_isolation; -- 查询当前事务的隔离级别+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)mysql> select * from `user_info` \G*************************** 1. row *************************** id: 2name: Tom uid: 1*************************** 2. row *************************** id: 5name: Jack uid: 2*************************** 3. row *************************** id: 7name: Aaron uid: 3

由于 MySQL 的 innodb 默认隔离级别是 RR (Repeatable reads) 级别，所以先更改当前 session 的隔离级别为 RC，并且设置 binlog 模式：

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

mysql> set session transaction isolation level read committed;Query OK, 0 rows affected (0.00 sec)mysql> set session binlog_format = 'ROW';mysql> select @@tx_isolation;+----------------+| @@tx_isolation |+----------------+| READ-COMMITTED |+----------------+1 row in set (0.00 sec)mysql> show session variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set (0.00 sec)

事务 A：

mysql> begin -- 显式开启一个事务（需要手动 commit） -> ;Query OK, 0 rows affected (0.00 sec)mysql> update `user_info` set `name` = 'Tommy' where `uid` = 1; -- 语句1Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> commit; -- 在执行语句1后，不立即 commit，然后在事务 B 执行语句1，则发现事务 B 一直在等待 事务 A 释放锁（行锁：uid=1 行数据），如果一直不 commit，则 事务 B 会wait直到超时（可以设置超时时间）Query OK, 0 rows affected (0.03 sec)

事务 B：

mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 锁超时时间为 300s+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 300 |+--------------------------+-------+1 row in set (0.00 sec)mysql> update `user_info` set `name` = 'Thomas' where `uid` = 1; -- 当事务 A commit 后，才能拿到锁（行锁：uid=1 行数据）进行更新Query OK, 0 rows affected (23.32 sec) -- 事务 A 在语句1 执行后23.32秒后，才 commitRows matched: 1 Changed: 0 Warnings: 0mysql> select * from user_info \G -- uid 为 1 的 name 已经更改为 Thomas*************************** 1. row *************************** id: 2name: Thomas uid: 1*************************** 2. row *************************** id: 5name: Jack uid: 2*************************** 3. row *************************** id: 7name: Aaron uid: 33 rows in set (0.00 sec)

事务 A 、事务 B ，均设置 session 事务锁超时时间为10s，测试 wait 直到超时：

mysql> SET innodb_lock_wait_timeout=10; -- 设置 session 事务锁超时时间为10sQuery OK, 0 rows affected (0.00 sec)

事务 A：

mysql> begin -- 显式开启一个事务（需要手动 commit） -> ;Query OK, 0 rows affected (0.00 sec)mysql> update `user_info` set `name` = 'Tommy-1' where `uid` = 1; -- 语句1Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> commit; -- 在执行语句1后，不立即 commit，然后在事务 B 执行语句1，则发现事务 B 一直在等待 事务 A 释放锁（行锁：uid=1 行数据），如果一直不 commit，则 事务 B 会wait直到超时（可以设置超时时间）Query OK, 0 rows affected (0.03 sec)

事务 B：

mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 锁超时时间为 300s +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 300 | +--------------------------+-------+ 1 row in set (0.00 sec) mysql> update `user_info` set `name` = 'Thomas-1' where `uid` = 1; -- 事务 A 一直不 commit，在则过 10s 后，超时报错 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- 此时，若事务 A commit，则 uid 为 1 的 name 已经更改为 Tommy-1

上述事务的执行流程：

时间序号	事务 A	事务 B
0	begin;
1	update `user_info` set `name` = 'Tommy' where `uid` = 1;
2		update `user_info` set `name` = 'Thomas' where `uid` = 1;
3	commit;

上述事务 A，为 uid=1 加了行锁，如果一直不 commit 去释放行锁，那么事务 B 就无法更改 uid=1 这行记录，但是可以更新其他行的记录；但是上述讨论的锁范围是行锁，uid 是有索引的，如果 uid 没有索引会发生什么？或者说是事务 A 修改条件是基于 name 列呢？譬如：

update `user_info` set `uid` = 11 where `name` = 'Tom' ;

此时，则将会锁住整张表，即使其他的行数据，也无法更新，测试如下：事务 A：

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> mysql> update `user_info` set `uid`=1 where `name`='Tom'; -- name 列无索引，锁住了整张表，在手动 commit 之前，事务 B 、事务 C 一直在等待释放表锁Query OK, 0 rows affected (0.00 sec)Rows matched: 1 Changed: 0 Warnings: 0mysql> mysql> commit -> ;Query OK, 0 rows affected (0.01 sec)

事务 B：

mysql> update `user_info` set `name` = 'Jack' where `uid` = 2;Query OK, 0 rows affected (8.28 sec) -- 事务 A 在 8.28s 后释放了表锁Rows matched: 1 Changed: 0 Warnings: 0

事务 C：

mysql> update `user_info` set uid=22 where `name`='Jack';Query OK, 1 row affected (5.81 sec) -- 事务 A 在 5.81s 后释放了表锁Rows matched: 1 Changed: 1 Warnings: 0

为什么当 name 列无索引时，会锁表？因为无法通过索引去检索数据，所以就只能对所有数据进行加锁，即：

当检索条件的列有索引时，innodb 启用行级锁；检索条件无索引时，启用表锁。注：即使是表锁，MySQL 也已对这种情况进行了部分优化，会对不满足条件的进行锁释放。

对于 Repeatable reads（RR）可重复读的隔离级别，也同样出现这种情况。因而，当对一定量级的数据表做更新时，尽量使用带索引的检索条件，否则检索、更新都将出现相应锁带来的性能问题。

6.3 RR 隔离级别

Repeatable reads（可重复读）隔离级别，简称 RR 隔离级别。主要测试以下两个问题：

•RR 级别下解决不可重复读，怎么解决？•RR 级别下，MySQL innodb 存储引擎是否可以解决幻读？如果可以，怎么解决？

首先测试在 RC 情况下，不可重复读的情况：默认的隔离级别 RR ，更改为 RC：

mysql> select @@tx_isolation; -- 默认为 RR+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)mysql> set session transaction isolation level read committed; -- 更改为 RCQuery OK, 0 rows affected (0.00 sec)mysql> select @@tx_isolation; +----------------+| @@tx_isolation |+----------------+| READ-COMMITTED |+----------------+1 row in set (0.00 sec)

事务 A :

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from `user_info` \G*************************** 1. row *************************** id: 2name: Tom uid: 1*************************** 2. row *************************** id: 5name: Jack uid: 22*************************** 3. row *************************** id: 7name: Aaron uid: 33 rows in set (0.00 sec)-- 此时等待事务 B 事务执行提交mysql> select * from `user_info` \G -- 待事务 B 执行提交后，再次查询，发现 id=2 的 name 已经更改为 Tommy，则前后两次读取的结果不一样，即为不可重复读*************************** 1. row *************************** id: 2name: Tommy uid: 1*************************** 2. row *************************** id: 5name: Jack uid: 22*************************** 3. row *************************** id: 7name: Aaron uid: 33 rows in set (0.00 sec)

事务 B：

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update `user_info` set name = 'Tommy' where `id`=2; -- 更改 id=2 的 name 为 TommyQuery OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> commit;Query OK, 0 rows affected (0.12 sec)

上述事务执行的流程为：

时间序号	事务 A	事务 B
0	begin;
1	select * from `user_info`;
2		begin;
3		update `user_info` set name = 'Tommy' where `id`=2;
4		commit;
5	select * from `user_info`;

可以发现，在 RC 隔离级别下，会出现不可重复读现象。

再次测试在 RR 隔离级别下：

查询当前事务隔离级别设置：

mysql> select @@tx_isolation; -- 已经为 RR 隔离级别+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)

‍

事务 A：

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from `user_info` \G*************************** 1. row *************************** id: 2name: Tom uid: 1*************************** 2. row *************************** id: 5name: Jack uid: 22*************************** 3. row *************************** id: 7name: Aaron uid: 33 rows in set (0.00 sec)-- 此时等待事务 B 事务执行提交mysql> select * from `user_info` \G -- 查询后，发现和前一次的结果一致，id=2 的 name 并没有更改为 Tommy*************************** 1. row *************************** id: 2name: Tom uid: 1*************************** 2. row *************************** id: 5name: Jack uid: 22*************************** 3. row *************************** id: 7name: Aaron uid: 33 rows in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from `user_info` \G -- 事务 A 提交后，再次查询，发现 id=2 的 name 更改为 Tommy*************************** 1. row *************************** id: 2name: Tommy uid: 1*************************** 2. row *************************** id: 5name: Jack uid: 22*************************** 3. row *************************** id: 7name: Aaron uid: 33 rows in set (0.00 sec)

事务 B：

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> mysql> update `user_info` set `name`='Tommy' where `id`=2; -- 更改 id=2 的 name 为 TommyQuery OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> commit;Query OK, 0 rows affected (0.10 sec)

二者事务的执行流程和 RC 隔离级别下的测试流程一样，但是可以发现在 RR 级别下，可以进行重复读。其中的原理与 RR 下 MySQL 如何解决幻读问题，下期见。

7. 参考

•数据库隔离级别

• https://en.wikipedia.org/wiki/Isolation_(database_systems)

•MySQL innodb 事务隔离级别

•https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

•MySQL 系统变量

•https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

•MySQL 存储引擎

•https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html •https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

vlambda博客
学习文章列表