测试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 READSET SESSION TRANSACTION LEVEL READ COMMITTED;

REPEATABLE READ测试

//终端1start 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 |+----+-------+------+//终端2insert 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 |+----+-------+------+//回到终端1select * 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 |+----+-------+------+//回到终端2select * 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 |+----+-------+------+//终端1commit;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;//终端1start 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 |+----+-------+------+//终端2insert 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;//终端1start transaction;select * from lock_table;+----+-------+------+| id | name  | age  |+----+-------+------+|  1 | Owen  |   20 ||  2 | Alice |   19 ||  3 | Mary  |   22 ||  4 | Hally |   22 ||  5 | Gown  |   32 |+----+-------+------+//终端2insert into lock_table values(0,"hahahah",32);//终端1select * 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也可见