示例表

  1. CREATE TABLE hero (
  2.     number INT,
  3.     name VARCHAR(100),
  4.     country varchar(100),
  5.     PRIMARY KEY (number)
  6. ) Engine=InnoDB CHARSET=utf8;
INSERT INTO hero VALUES(1, '刘备', '蜀');

mysql> SELECT * FROM hero;
+--------+--------+---------+
| number | name   | country |
+--------+--------+---------+
|      1 | 刘备   | 蜀      |
+--------+--------+---------+
1 row in set (0.00 sec)

事务隔离级别

事务并发执行遇到的问题

  • 脏写（Dirty Write）

如果一个事务修改了另一个未提交事务修改过的数据，那就意味着发生了脏写

image.png

  • 脏读（Dirty Read）

如果一个事务读到了另一个未提交事务修改过的数据，那就意味着发生了脏读

image.png

  • 不可重复读（Non-Repeatable Read）

如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读

image.png

  • 幻读（Phantom）

如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读

幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录。

image.png

SQL标准中的四种隔离级别

我们给这些问题按照严重性来排一下序：

脏写 > 脏读 > 不可重复读 > 幻读

4个隔离级别：

  • READ UNCOMMITTED：未提交读。
  • READ COMMITTED：已提交读。
  • REPEATABLE READ：可重复读。
  • SERIALIZABLE：可串行化。

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED Possible Possible Possible
READ COMMITTED Not Possible Possible Possible
REPEATABLE READ Not Possible Not Possible Possible
SERIALIZABLE Not Possible Not Possible Not Possible

脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生。

MySQL中支持的四种隔离级别

不同的数据库厂商对SQL标准中规定的四种隔离级别支持不一样，比方说Oracle就只支持READ COMMITTED和SERIALIZABLE隔离级别。本书中所讨论的MySQL虽然支持4种隔离级别，但与SQL标准中所规定的各级隔离级别允许发生的问题却有些出入，MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的

MySQL的默认隔离级别为REPEATABLE READ，我们可以手动修改一下事务的隔离级别。

如何设置事务的隔离级别

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

其中的level可选值有4个：

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}
  • 使用GLOBAL关键字（在全局范围影响）
    • 只对执行完该语句之后产生的会话起作用。
    • 当前已经存在的会话无效。
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  • 使用SESSION关键字（在会话范围影响）
    • 对当前会话的所有后续的事务有效
    • 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务。
    • 如果在事务之间执行，则对后续的事务有效。
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  • 上述两个关键字都不用（只对执行语句后的下一个事务产生影响）
    • 只对当前会话中下一个即将开启的事务有效。
    • 下一个事务执行完后，后续事务将恢复到之前的隔离级别。
    • 该语句不能在已经开启的事务中间执行，会报错
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

启动 mysql 时可以指定隔离级别:

--transaction-isolation=SERIALIZABLE

查看当前会话默认的隔离级别:

mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)