隔离级别分类
事务的隔离级别分为以下4类:
- 读未提交
- 读已提交
- 可重复度
- 串行化
事务的并发问题
- 脏读:一个事务读到了另一个事务还未提交的update数据,导致多次查询的结果不一样
- 不可重复读:一个事务读到了另一个事务已经提交的update数据,导致多次查询结果不一致
- 幻读:一个事务读到了另一个事务已经提交的insert数据,导致多次查询的结果不一样
幻读详解:幻读是只在重复度的隔离级别下,A事务执行过程中,B事务执行,并插入新的数据,提交事务。如果此时A事务直接进行select查询,则查询不到事务B插入的新数据。但是如果A事务更新数据,并在where条件中包含事务B插入的数据,则再次进行select查询时,会查询到B事务提交的数据。相关演示如下:
-- 会话A
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from goods;
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | 花生 | 5.00 |
| 2 | 土豆 | 1.80 |
| 3 | 牛肉 | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)
-- 切到会话B
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from goods;
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | 花生 | 5.00 |
| 2 | 土豆 | 1.80 |
| 3 | 牛肉 | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)
mysql> insert into `goods`(`id`, `name`, `price`) values (4, 'banana', 2.90);
Query OK, 1 row affected (0.00 sec)
mysql> select * from goods;
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | 花生 | 5.00 |
| 2 | 土豆 | 1.80 |
| 3 | 牛肉 | 48.00 |
| 4 | banana | 2.90 |
+----+--------+-------+
4 rows in set (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
-- 会话B结束
-- 切到会话A,再次查询还是3条数据
mysql> select * from goods;
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | 花生 | 5.00 |
| 2 | 土豆 | 1.80 |
| 3 | 牛肉 | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)
-- 执行Update语句,但是我们看到4条数据变更
mysql> update goods set price=price + 1.0;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
-- 再次查询,我们发现有4条数据了
mysql> select * from goods;
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | 花生 | 6.00 |
| 2 | 土豆 | 2.80 |
| 3 | 牛肉 | 49.00 |
| 4 | banana | 3.90 |
+----+--------+-------+
4 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
详解隔离级别
读未提交
读未提交(read uncommitted):事务A读到事务B还未提交的update数据,导致多次查询的结果不一致。
演示
我们打开两个MySQL连接(两个不同session),这里为了区分分别叫Session1简称S1,Session2简称S2。
首先在S1中执行:
select * from goods;
结果如下:
mysql> select * from goods;
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | 花生 | 5.00 |
| 2 | 土豆 | 1.80 |
| 3 | 牛肉 | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)
步骤2
这时我们给花生涨价1块钱,在S2中执行:
start transaction;
update goods set price=price + 1.0 where id = 1;
select * from goods;
结果如下:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update goods set price=price + 1.0 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from goods;
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | 花生 | 6.00 |
| 2 | 土豆 | 1.80 |
| 3 | 牛肉 | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)
步骤3
切换至S1, 执行:
select * from goods;
结果如下:
mysql> select * from goods;
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | 花生 | 5.00 |
| 2 | 土豆 | 1.80 |
| 3 | 牛肉 | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)
步骤4
我们再S1看到花生的价格仍然是5块。原因是S1现在是MySQL的默认隔离级别,我们执行如下SQL:
select @@tx_isolation;
结果如下,默认事务隔离级别是可重复读
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
这时我们手动设置事务的隔离级别为读未提交,执行如下SQL:
#设置事务隔离级别为读未提交
set session transaction isolation level read uncommitted;
select * from goods;
结果如下:
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from goods;
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | 花生 | 6.00 |
| 2 | 土豆 | 1.80 |
| 3 | 牛肉 | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)
可以看到,我们花生已经涨价了。
步骤5
这时,我们回滚S2的事务,执行SQL:
rollback;
步骤6
我们再在S1中执行查询语句:
select * from goods;
结果如下:
mysql> select * from goods;
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | 花生 | 5.00 |
| 2 | 土豆 | 1.80 |
| 3 | 牛肉 | 48.00 |
+----+--------+-------+
3 rows in set (0.00 sec)
在S2事务回滚后,花生的价格又降到了5块。
也就是说 事务B还没有执行commit提交操作,事务A就已经读到了它的数据,如果事务B由于某种原因并未提交(uncommitted)而是执行回滚(rollback)操作,事务A读到的数据便不可信,也就是发生了脏读
读已提交
读已提交(read committed):在事务B提交前与提交后,事务A两次的查询结果是不一致的,也就是发生了不可重复读。
可重复度
可重复度(reaptable read):即使事务B已经提交数据,数据已经变更,但是在事务A中,两次读到的结果仍然是一致的。
串行化
串行化(serializable):必须上一个事务执行完毕,下一个事务的操作才可以开始,这就自然可以避免幻读。但是如果存在serializable隔离级别事务没有commit或rollback或超时,其它事务更新、插入、删除等写操作都会被阻塞(加了表级写锁)。