MySQL 开发随笔

设置事务隔离级别

事务隔离级别的介绍

事务表示多个数据操作组成一个完整的事务单元,在这个事务内的所有数据操作要么同时成功,要么同时失败。数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响,避免各种并发问题。一个事务与其他事务隔离的程度称为 隔离级别。SQL 标准中规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但数据库的并发性能越差。为了解决事务并发问题(脏读、不可重复读、幻读),主流的关系型数据库都会提供以下四种事务隔离级别。

  • 读未提交(Read Uncommitted)
    • 在该隔离级别,所有事务都可以看到其他未提交事务所做的改变
    • 该隔离级别是最低的隔离级别,虽然拥有超高的并发处理能力及很低的系统开销,但很少用于实际应用。
    • 该隔离级别只能解决第一类更新丢失问题,不能解决脏读、不可重复读、幻读的问题。
  • 读已提交(Read Committed)
    • 这是大多数数据库系统的默认隔离级别(但不是 MySQL 默认的),例如 Oracle 数据库。
    • 该隔离级别满足了隔离的简单定义:一个事务只能看见已提交事务所做的改变
    • 该隔离级别可以解决脏读问题,但会出现不可重复读、幻读问题。
  • 可重复读(Repeatable Read)
    • 这是 MySQL 的默认事务隔离级别,它可以确保在整个事务过程中,对同一条数据的读取结果是相同的,不管其他事务是否在对共享数据进行更新,也不管其他事务更新提交与否
    • 该隔离级别可以解决脏读、不可重复读的问题,但会出现幻读问题。
  • 串行化(Serializable)
    • 这是最高的事务隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决脏读、不可重复读、幻读、第一类更新丢失、第二类更新丢失问题
    • 该隔离级别可以解决所有的事务并发问题,但可能导致大量的超时现象和锁竞争,通常数据库不会用这个隔离级别,可用其他的方案来解决这些问题,例如乐观锁和悲观锁。

事务隔离级别的案例

上图中是典型的第二类丢失更新问题,后果非常严重。当数据库隔离级别为读已提交(Read Committed)及以下隔离级别时,会出现不可重复读的现象。从上面的表格可以看出,当事务隔离级别设置为可重复读(Repeatable Read)时,可以避免不可重复读的现象出现。

事务隔离级别的设置

1
2
3
4
5
6
7
8
-- 设置下一次事务的隔离级别
set transaction isolation level repeatable read;

-- 设置当前会话的事务隔离级别
set session transaction isolation level repeatable read;

-- 设置全局的事务隔离级别
set global session transaction isolation level repeatable read;

事务隔离级别的总结

上述四种事务隔离级别会产生的并发问题如下(YES 表示存在对应的问题,NO 表示不存在对应的问题):

各种关系型数据库对事务隔离级别的支持程度如下(YES 表示支持,NO 表示不支持):

OracleMySQL
Read UncommittedNOYES
Read CommittedYES (默认)YES
Repeatable ReadNOYES (默认)
SerializableYESYES

提示

  • 在 Spring 框架中,事务隔离级别可以通过 @Transactional 注解中的 isolation 属性定义。
  • MySQL 的默认隔离级别是可重复读 (Repeatable Read),而 Oracle 的默认隔离级别是读已提交 (Read Committed)。

使用 SQL 语句手动加锁

锁机制的介绍

在 MySQL 中,锁机制可以用来解决事务并发问题。

  • 共享锁:允许事务读取数据,但不允许修改。
  • 排他锁:只允许一个事务读取和修改数据,其他事务不能读取或修改。

锁机制的使用

SQL 语句加锁的语法

1
2
3
4
5
-- 加共享锁
SELECT * FROM table_name WHERE condition FOR SHARE;

-- 加排他锁
SELECT * FROM table_name WHERE condition FOR UPDATE;

SQL 语句加锁的案例

假设有一张 accounts

1
2
3
4
CREATE TABLE accounts (
id INT PRIMARY KEY,
balance DECIMAL(10, 2)
);

如果希望防止脏读、不可重复读和幻读,可以这样使用事务和锁

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 事务 1
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- 事务 2
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- 事务 1 可以修改 balance
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 事务 2 在事务 1 提交之前无法继续执行