测试Mysql的事务隔离隔离级别

测试环境

• Ubuntu+Mysql5.7+innodb引擎

首先回顾一下四种隔离级别

隔离级别	脏读	不可重复读	幻读	加锁读
Read Uncommitted	Yes	Yes	Yes	No
Read Committed	No	Yes	Yes	No
Repeatable Read	No	No	Yes	No
Serializable	No	No	No	Yes

//设置数据库隔离级别
//默认是REPEATABLE READ
SET SESSION TRANSACTION LEVEL READ COMMITTED;

REPEATABLE READ测试

//终端1
start transaction;
select * from lock_table;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Owen  |   20 |
|  2 | Alice |   19 |
|  3 | Mary  |   21 |
|  4 | Hally |   12 |
|  5 | Gown  |   32 |
|  7 | qqqqq |   15 |
+----+-------+------+


//终端2
insert into lock_table values(0,'wwww',17);
select * from lock_table;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Owen  |   20 |
|  2 | Alice |   19 |
|  3 | Mary  |   21 |
|  4 | Hally |   12 |
|  5 | Gown  |   32 |
|  7 | qqqqq |   15 |
|  8 | wwww  |   17 |
+----+-------+------+


//回到终端1
select * from lock_table;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Owen  |   20 |
|  2 | Alice |   19 |
|  3 | Mary  |   21 |
|  4 | Hally |   12 |
|  5 | Gown  |   32 |
|  7 | qqqqq |   15 |
+----+-------+------+
insert into lock_table values(0,'eeeee',19);
select * from lock_table;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Owen  |   20 |
|  2 | Alice |   19 |
|  3 | Mary  |   21 |
|  4 | Hally |   12 |
|  5 | Gown  |   32 |
|  7 | qqqqq |   15 |
|  9 | eeeee |   19 |
+----+-------+------+


//回到终端2
select * from lock_table;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Owen  |   20 |
|  2 | Alice |   19 |
|  3 | Mary  |   21 |
|  4 | Hally |   12 |
|  5 | Gown  |   32 |
|  7 | qqqqq |   15 |
|  8 | wwww  |   17 |
+----+-------+------+


//终端1
commit;
select * from lock_table;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Owen  |   20 |
|  2 | Alice |   19 |
|  3 | Mary  |   21 |
|  4 | Hally |   12 |
|  5 | Gown  |   32 |
|  7 | qqqqq |   15 |
|  8 | wwww  |   17 |
|  9 | eeeee |   19 |
+----+-------+------+

总结一下

• 进入事务的线程(?), 看不到其他线程做出的修改

• 在事务中做的修改在提交前，其他线程也看不见

• 在上述例子中并没有幻读问题，因为Mysql使用了MVCC来解决，但是终端1若插入(8,”aaaa”,12)会失败，因为id为8的数据已经进入数据库了。

---

SERIALIZABLE测试

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

//终端1
start transaction;
select * from lock_table;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Owen  |   20 |
|  2 | Alice |   19 |
|  3 | Mary  |   21 |
|  4 | Hally |   12 |
|  5 | Gown  |   32 |
|  7 | qqqqq |   15 |
|  8 | wwww  |   17 |
|  9 | eeeee |   19 |
| 10 | rrrrr |   12 |
+----+-------+------+
update lock_table set age = 21 where age = 12;
select * from lock_table;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Owen  |   20 |
|  2 | Alice |   19 |
|  3 | Mary  |   21 |
|  4 | Hally |   21 |
|  5 | Gown  |   32 |
|  7 | qqqqq |   15 |
|  8 | wwww  |   17 |
|  9 | eeeee |   19 |
| 10 | rrrrr |   21 |
+----+-------+------+

//终端2
insert into lock_table values(0,"aaaa",12);
>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

总结一下

• 进入事务的线程(?), 会给行加锁，其他线程无法更新或插入，但可以正常读取。

• 在事务中做的修改在提交前，其他线程也看不见

• 因为别的线程不能读取和更新，所以没有幻读问题，但并发性很差

---

READ COMMITTED测试

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

//终端1
start transaction;
select * from lock_table;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Owen  |   20 |
|  2 | Alice |   19 |
|  3 | Mary  |   22 |
|  4 | Hally |   22 |
|  5 | Gown  |   32 |
+----+-------+------+


//终端2
insert into lock_table values(0,"hahahah",32);


//终端1
select * from lock_table;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | Owen    |  20 |
|  2 | Alice   |  19 |
|  3 | Mary    |  22 |
|  4 | Hally   |  22 |
|  5 | Gown    |  32 |
|  6 | hahahah |  32 |
+----+---------+-----+

总结一下

• 进入事务的线程(?), 可以读取到已经提交事务做出读修改

• 在事务中做的修改在提交前，其他线程看不见

• 有不可重复读的问题，虽然我不确定这算不算问题，也可以说具有可见性。

---

READ UNCOMMITTED测试

很遗憾，据我的测试并没有出现脏读的问题。
现象和READ COMMITTED一样。
期待的结果是：线程1在事务中做出的修改在未提交前，线程2也可见