MySQL InnoDB 浅析:事务隔离级别和死锁

编辑于 2016-09-17

* 移动设备下, 可左滑手指以查看较宽代码

参考书籍: High Performance MySQL

从 MySQL 5.5 开始, InnoDB 就成了 MySQL 的默认存储引擎.

MySQL 包括三层结构:
第一层, 连接层
第二层, 这是 MySQL 比较有意思的部分. 所有跨存储引擎的功能都在这一层实现: 存储过程, 触发器, 视图等.
第三层, 包含存储引擎. 它不会去解析 SQL, 而是实现了几十个底层 API, 诸如"开始一个事务", "根据主键提取一行记录" 等. 不同存储引擎之间也不会相互通信, 而是响应上层服务器的请求. 这里说的 innoDB 就运行的这层.

如果是跨多存储引擎的事务操作, 不支持事务的存储引擎将不会回滚.

隔离级别

在 SQL 标准中定义的四种隔离级别. 较低的级别可以执行更高的并发.

  • READ UNCOMMITTED (未提交读)

    在 READ UNCOMMITTED 级别, 事务中的修改, 即使没有提交, 对其他事务也都是可见的. 事务可以读取未提交的数据, 这也被称为 脏读. 这会导致很多问题, 从性能上来说, READ UNCOMMITTED 不会比其他级别好很太多, 但缺乏其他级别的很多好处. 所以在实际应用中很少使用.

  • READ COMMITTED (提交读, 不可重复读)

    大多数数据库的隔离级别都是 READ COMMITTED (但 MySQL 不是). 它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变. 这种隔离级别也支持所谓的不可重复读(Nonrepeatable Read), 因为同一事务的其他实例在该实例处理其间可能会有新的 commit, 所以同一 select 可能返回不同结果.

  • REPEATBALE READ (可重复读)

    这是 MySQL 的默认事务隔离级别, 它确保同一事务的多个实例在并发读取数据时, 会看到同样的数据行. 不过理论上, 这会导致另一个棘手的问题: 幻读 . 简单的说, 幻读指当用户读取某一范围的数据行时, 另一个事务又在该范围内插入了新行, 当用户再读取该范围的数据行时, 会发现有新的“幻影” 行. InnoDB 和 XtraDB 存储引擎通过多版本并发控制 (MVCC, Multiversion Concurrency Control) 机制解决了该问题.

  • SERIALIZABLE (可串行化)

    这是最高的隔离级别, 它通过强制事务串行执行, 使之不可能相互冲突, 从而解决幻读问题. 简言之, 它是在每个读的数据行上加上共享锁. 在这个级别, 可能导致大量的超时现象和锁竞争.只有在非常需要确保数据一致性且可以接受无并发的情况下, 才使用此隔离级别.

死锁

来看看下面的例子:

#事务1
START TRANSACTION;
UPDATE a SET close = 45.50 WHERE id = 4 AND date = '2002-05-01';
UPDATE a SET close = 19.80 WHERE id = 3 AND date = '2002-05-02';
COMMIT;

#事务2
START TRANSACTION;
UPDATE a SET close = 20.15 WHERE id = 3 AND date = '2002-05-02';
UPDATE a SET close = 47.20 WHERE id = 4 AND date = '2002-05-01';
COMMIT;

两个事务都执行了第一条 UPDATE 语句, 更新了一行数据, 同时也锁定了该行, 接着执行第二条 UPDATE 语句, 却发现该行已被对方锁定, 然后同时等待对方释放锁, 陷入死循环. 除非有外部因素介入才会解除死锁.

有一种解决方式是, 设置一个超时的时间, 等待超时后放弃锁请求, 这种方式通常认为不太好. InnoDB 目前的处理方式是, 将持有最少行级排他锁的事务进行回滚, 这是相对比较简单的死锁回滚算法.

死锁形成时,只有部分或完全回滚其中一个事务,才能打破死锁. 应用程序应该考虑如何处理死锁,大多数情况只需要重新执行因死锁回滚的事务即可.

事务开始时,InnoDB 会隐式的执行锁定,只有提交或回滚时才会同时释放.

另外说明, LOCK TABLE (锁表操作) 和事务之间相互影响的话, 情况会变得非常复杂, 在某些 MySQL 版本中甚至会产生无法预料的结果. 在此建议, 除了事务中禁用了 AUTOCOMMIT (自动提交) 可以使用 LOCK TABLE 之外, 其他任何时候都不要显式执行 LOCK TABLE, 不管用的是什么存储引擎.