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
目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚(这是相对简单的死锁回滚算法)。
参考资料: