Kill Anxiety-MySQL事务隔离级别

1. 隔离级别概述

隔离级别用于限定事务内外的哪些改变是可见的，哪些是不可见的。

MySQL实现了标准定义的4类隔离级别。
隔离级别从低到高依次是：Read Uncommitted, Read Committed, Repeatable Read, Serializable

低级别的隔离一般支持更高的并发，并且系统开销更低。

MySQL的默认事务隔离级别是 Repeatable Read。

文中针对每一种隔离级别包含实战部份，涉及的表结构和数据如下：

create table product(
	id bigint not null auto_increment,
	name varchar(100) not null,
	stock int not null default 0,
	primary key (id),
	index product_name(name)
);
insert into product(id,name,stock) values(1,'Java并发编程的艺术',100);

需要用到的一些命令的说明

# 查看当前会话事务隔离级别
select @@tx_isolation;

# 查看全局的事务隔离级别
select @@global.tx_isolation;

# 设置事务全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

# 设置事务会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABL;

2. Read Uncommitted (读取未提交内容)

概念：所有事务都可以看到其它未提交事务的执行结果。

问题：脏读

实战：
step1: 会话1

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select stock from product where id = 1;
+-------+
| stock |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

mysql>

setp2: 会话2

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select stock from product where id = 1;
+-------+
| stock |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

mysql> update product set stock=99 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

setp3: 回到会话1

mysql> select stock from product where id = 1;
+-------+
| stock |
+-------+
|    99 |
+-------+
1 row in set (0.00 sec)

可以看到， 事务隔离级别为 READ UNCOMMITTED 时，会话1读取到了会话2未提交的库存数据修改。

3. Read Committed (读取已提交内容)

概念：一个事务只能看到已经提交事务所做的改变。

问题：一个事务中，同一个select可能返回不同的结果。（其它事务提交，可能修改了数据）

实战：
step1: 会话1

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select stock from product where id = 1;
+-------+
| stock |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

mysql>

setp2: 会话2

mysql> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update product set stock=99 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

step3: 会话1

mysql> select stock from product where id = 1;
+-------+
| stock |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

step4: 会话2

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

step5: 会话1

mysql> select stock from product where id = 1;
+-------+
| stock |
+-------+
|    99 |
+-------+
1 row in set (0.00 sec)

可以看到，事务隔离级别为 READ COMMITTED 时，会话1只有在会话2提交后，才能读到会话2对库存数据的修改。

4. Repeatable Read (可重读)

概念：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行.

问题：幻读 (指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。)

InnoDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。(见本文后面章节)

实战：
step1: 会话1

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select stock from product where id = 1;
+-------+
| stock |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

step2: 会话2

mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update product set stock=99 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

step3: 会话1

mysql> select stock from product where id = 1;
+-------+
| stock |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select stock from product where id = 1;
+-------+
| stock |
+-------+
|    99 |
+-------+
1 row in set (0.00 sec)

可以看到，事务隔离级别为 REPEATABLE READ 时，会话1 在执行过程中，反复的读取的库存数据都是一致的，即使这个过程中，会话2 提交了库存数据修改事务。

5. Serializable (可串行化)

概念：强制事务排序，使之不可能相互冲突，从而解决幻读问题。

问题：在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

实战：
setp1: 会话1

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select stock from product where id = 1;
+-------+
| stock |
+-------+
|    99 |
+-------+
1 row in set (0.00 sec)

step2: 会话2

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update product set stock = 98 where id =1;

上面更新语句被卡住，在等待锁

setp3: 会话1

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

step4: 会话2

Query OK, 1 row affected (5.46 sec)
Rows matched: 1  Changed: 1  Warnings: 0

可以看到，事务隔离级别为 SERIALIZABLE 时，会话1 ，会话2 事务串行执行。

6. MVCC

多版本并发控制 (Multiversion Concurrency Control)

MVCC是行级锁的一个变种，它在很多情况下避免了加锁操作，所以开销更低。

MVCC只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作，其它两个隔离级别与MVCC不兼容。

MVCC通过保存数据在某个时间点的快照来实现的，不管事务要执行多长时间，每个事务看到的数据都是一致的。

实现机制：
在每行记录后面添加两个隐藏的列：创建时版本号，删除时版本号

每开始一个新的事务，系统版本号都会自动递增，将自增后的系统版本号作为该事务的事务版本号。

SELECT
根据下面2个条件检查每行记录：
a. 只查找早于当前事务版本号的数据行(即行的创建时版本号小于或等于当前事务的版本号)。这样可以确保事务事务读取的行，要么是在事务开始前已经存在，要么是事务自身插入或者修改过的

b. 行的删除时版本号要么未定义，要么大于当前事务版本号，这可以确保事务读取到的行，在事务开始之前未被删除。

INSERT
新插入的每一行保存当前事务版本号作为行版本号（创建时版本号字段）。

DELETE
为删除的每一行保存当前事务版本号做为删除标识（删除时版本号字段）

UPDATE
插入一行新记录，保存当前事务版本号作为行版本号（创建时版本号字段），同时保存当前事务版本号到原来的行，作为行删除标识。（删除时版本号字段）

保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

参考：

高性能MySQL第3版20分钟彻底搞清MySQL的事务与锁