MySQL 事务隔离级别

2018年4月23日 MySQL 发表评论

mysql 拥有分层的架构。上层是服务器层的服务和查询执行引擎，下层则是存储引擎。

mysql 支持 LOCK TABLES 和 UNLOCK TABLES 语句，这是在服务器层实现的，和存储引擎无关。

mysql 服务器层 不管理事务，事务（行级锁）是由下层的存储引擎实现的。

事务

事务就是一组原子性的 SQL 查询，或者说一个独立的工作单元。事务内的语句，要么全部执行成功，要么全部执行失败。

银行应用是解释事务必要性的一个经典例子。假设一个银行的数据库有两张表：支票（checking）表和储蓄（savings）表。现在要从某个用户的支票账户转移 200 美元到他的储蓄账户，事务 SQL 的样本如下：

1 START TRANSACTION;
2 SELECT balance FROM checking WHERE customer_id = 10233276;
3 UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
4 UPDATE savings  SET balance = balance + 200.00 WHERE customer_id = 10233276;
5 COMMIT;

1. 事务的标准 ACID 特性

原子性（atomicity）
- 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
一致性（consistency）
- 数据库总是从一个一致性的状态转换到另一个一致性的状态。一致性是对数据可见性的约束，保证在一个事务中的多次操作的数据中间状态对其它事务不可见，这些中间状态是一个过渡状态，与事务的开始状态和事务的结束状态是不一致的。在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失 200 美元。
隔离性（isolation）
- 通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外一个账户汇总程序开始运行，则其看到的支票账户的余额并没有被减去 200 美元。
持久性（durability）
- 一旦事务提交，则其所做的修改就会永久保存到数据库中。

原子性和一致性的侧重点不同：原子性关注状态，要么全部成功，要么全部失败。而一致性关注数据的可见性。

2. 隔离级别

在 SQL 标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

下面简单地介绍一下四种隔离级别：

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
READ UNCOMMITTED	Yes	Yes	Yes	No
READ COMMITTED	No	Yes	Yes	No
REPEATABLE READ	No	No	Yes	No
SERIALIZABLE	No	No	No	Yes

READ UNCOMMITTED（未提交读）
- 在 READ UNCOMMITTED 级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为 脏读（Dirty Read）。
READ COMMITTED（提交读）
- 大多数数据库系统的默认隔离级别都是 READ COMMITTED（但 MySQL 不是）。READ COMMITTED 满足：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做 不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。
REPEATABLE READ（可重复读）
- REPEATABLE READ 解决了脏读的问题。保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别无法解决幻读问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生 幻行（Phantom Row）。InnoDB 和 XtraDB 存储引擎通过 多版本并发控制（MVCC） 解决了幻读的问题。
- 可重复读是 MySQL 的默认事务隔离级别。
SERIALIZABLE（可串行化）
- SERIALIZABLE 强制事务串行执行，避免了前面说的幻读问题。简单来说，SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁挣用问题。

3. 死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

例如，设想下面两个事务同时处理 StockPrice 表：

事务 1
    START TRANSACTION;
    UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
    UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
    COMMIT;     

事务 2
    START TRANSACTION;
    UPDATE StockPrice SET close = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
    UPDATE StockPrice SET close = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
    COMMIT;

如果凑巧，两个事务都执行了第一条 UPDATE 语句，更新了一行数据，同时也锁定了该行数据，接着每个事务都尝试去执行第二条 UPDATE 语句，却发现该行已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环。除非有外部因素介入才可能解除死锁。

MyISAM 表锁不会产生死锁。InnoDB 目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚（这是相对简单的死锁回滚算法）。

参考资料：

数据库事务原子性、一致性是怎样实现的？

事务

1. 事务的标准 ACID 特性

2. 隔离级别

3. 死锁

发表评论 取消回复

发表评论取消回复