测试环境
- 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也可见