关系型数据库事务隔离级别

07-26 db-transaction-isolation

SQL 标准定义了 4 种隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

00. 查看隔离级别

# 全局
select @@global.tx_isolation;

# 当前会话
select @@session.tx_isolation;

# 设置隔离级别
set [global|session] transaction isolation level 隔离级别;

01. SERIALIZABLE（可串行化）

# 设置隔离级别
mysql1> set session transaction isolation level serializable;
mysql2> set session transaction isolation level serializable;

# 开启事务
mysql1> start transaction;
mysql2> start transaction;

# 查询
mysql1> select * from tb1;
Empty set (0.00 sec)
mysql2> select * from tb1;
Empty set (0.00 sec)

# 不能新增数据
mysql1> insert into tb1(value) values("hello");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql2> insert into tb1(value) values("hello");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql1> commit;
mysql1> insert into tb1(value) values("hello"); # 无法新增数据
mysql2> commit;
mysql1> insert into tb1(value) values("hello"); # 增加成功
mysql2> insert into tb1(value) values("world"); # 增加成功

总结：如果开启事务，并且需要 select 数据，那么整个表会被这个事务占据，其他客户不能执行写操作。这是最高的隔离级别，强制所有事务都串行的执行。

02. REPEATABLE READ（可重复读）

# 设置隔离级别
mysql1> set session transaction isolation level repeatable read;
mysql2> set session transaction isolation level repeatable read;

# 开启事务
mysql1> start transaction;
mysql2> start transaction;

# 查询
mysql1> select * from tb1;
Empty set (0.00 sec)
mysql2> select * from tb1;
Empty set (0.00 sec)

# 可以新增数据
mysql1> insert into tb1(value) values("hello");
mysql2> insert into tb1(value) values("hello");

mysql1> select * from tb1;
+----+-------+
| id | value |
+----+-------+
|  1 | hello |
+----+-------+

mysql2> select * from tb1;
+----+-------+
| id | value |
+----+-------+
|  2 | hello |
+----+-------+

mysql1> update tb1 set value="啦啦啦" where id=1;
# 无法修改，因为 mysql2 客户还未 commit 事务
mysql1> update tb1 set value="啦啦啦" where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql2> commit;
mysql1> select * from tb1;
+----+-----------+
| id | value     |
+----+-----------+
|  1 | 啦啦啦    |
+----+-----------+

# 成功修改了 mysql2 commit 的事务中的数据
mysql1> update tb1 set value="幻读测试" where id=2;

# 产生了幻读：读到别的事务所插入的数据
mysql1> select * from tb1;
+----+--------------+
| id | value        |
+----+--------------+
|  1 | 啦啦啦       |
|  2 | 幻读测试     |
+----+--------------+

总结：相对上一级别，可以新增数据/更新数据，其它事务新增/删除数据是透明不可见的，在其它事务没有 commit 之前，不能修改其它事务所增加的数据，commit 后再去修改会产生幻读的问题。

03. READ COMMITTED（提交读，不可重复读）

# 设置隔离级别
mysql1> set session transaction isolation level read committed;
mysql2> set session transaction isolation level read committed;
# 开启事务
mysql1> start transaction;
mysql2> start transaction;

mysql1> insert into tb1(value) values("hello");

# 现在还没有
mysql2> select * from tb1;
Empty set (0.00 sec)

mysql1> commit;

# commit 后可以读到
mysql2> select * from tb1;
+----+-------+
| id | value |
+----+-------+
|  1 | hello |
+----+-------+

总结：当一个事务 commit 修改后，那么另一个事务就可以读到所做的修改了。

04. READ UNCOMMITTED（未提交读）

# 设置隔离级别
mysql1> set session transaction isolation level read uncommitted;
mysql2> set session transaction isolation level read uncommitted;

# 开启事务
mysql1> start transaction;
mysql2> start transaction;

# 此时还没有数据
mysql2> select * from tb1;
Empty set (0.00 sec)

# 新增一条数据
mysql1> insert into tb1(value) values("hello");

# 可以查到刚才新增的数据，注意：此时 mysql1 的事务还没有 commit
mysql2> select * from tb1;
+----+-------+
| id | value |
+----+-------+
|  1 | hello |
+----+-------+

总结：在一个事务内可以读到另一个事物还没有 commit 的修改。

Database