【转载】mysql 四种隔离级别分析

时间：2023-03-08 15:46:05

sql标准中，有四种隔离级别，各个离级别都有各自的规则，隔离级别越低，允许并发越大，消耗的资源越少，但是越不安全，下面就mysql数据库来分别介绍一下（每个存储引擎实施的隔离级别会有稍微的不同）
mysql 动态修改隔离级别的命令
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
}

1:READ UNCOMMITTED
在这种隔离级别中，事务能看到其他事务未提交的结果，像这种读到uncommitted的数据称为dirty read
如：
mysql--root@localhost【转载】mysql 四种隔离级别分析none) 06:47:49>>show variables like 'tx%';
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| tx_isolation | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)
session A session B
start TRANSACTION; start TRANSACTION;
select * from t4 where i>45; select * from t4 where i>45;
+------+------+ +------+------+ 
| i | j | | i | j | 
+------+------+ +------+------+ 
| 55 | 2 | | 55 | 2 | 
| 46 | 2 | | 46 | 2 | 
| 47 | 3 | | 47 | 3 | 
| 48 | 2 | | 48 | 2 |

insert into t4 values (49,4),(49,4); 
Query OK, 2 rows affected (0.00 sec) 
Records: 2 Duplicates: 0 Warnings: 0

select * from t4 where i>45;
+------+------+ 
| i | j | 
+------+------+ 
| 55 | 2 | 
| 46 | 2 | 
| 47 | 3 | 
| 48 | 2 | 
| 49 | 4 | 
| 49 | 4 | 
+------+------+ 
6 rows in set (0.00 sec)

select * from t4 where i>45; 
+------+------+ 
| i | j | 
+------+------+ 
| 55 | 2 | 
| 46 | 2 | 
| 47 | 3 | 
| 48 | 2 | 
| 49 | 4 | 
| 49 | 4 | 
+------+------+ 
6 rows in set (0.00 sec)

commit；
很明显，在session A 中，未提交事务修改的数据，在session B中也能显示，这样就是未提交读隔离级别
2：READ COMMITTED
在这种隔离级别下，事务只能看到其他事务commit的数据，但是在这种级别下，会出现所谓的”不可重复读“问题，即查询前后，得到的数据不一样
如：
mysql--root@localhost【转载】mysql 四种隔离级别分析none) 07:02:03>>show variables like 'tx%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

session A session B

select * from t4 where i=55; select * from t4 where i=55;
+------+------+ +------+------+ 
| i | j | | i | j | 
+------+------+ +------+------+ 
| 55 | 2 | | 55 | 2 | 
+------+------+ +------+------+ 
1 row in set (0.00 sec) 1 row in set (0.00 sec)

update t4 set j=4 where i=55;
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1 Changed: 1 Warnings: 0

select * from t4 where i=55; select * from t4 where i=55;
+------+------+ +------+------+ 
| i | j | | i | j | 
+------+------+ +------+------+ 
| 55 | 4 | | 55 | 2 | 
+------+------+ +------+------+ 
1 row in set (0.00 sec) 1 row in set (0.00 sec) 
在session B中是看不到未提交事务修改的数据

commit;

select * from t4 where i=55; select * from t4 where i=55;
+------+------+ +------+------+ 
| i | j | | i | j | 
+------+------+ +------+------+ 
| 55 | 4 | | 55 | 4 | 
+------+------+ +------+------+ 
1 row in set (0.00 sec) 1 row in set (0.00 sec)
当事务提交后，session B 就能看到修改的数据，这样也就发生了上述所谓的“不可重复读”问题

3：REPEATABLE READ
理论上讲，该级别解决了READ COMMITTED级别的“不可重复读”问题，但是还是会出现所谓的“幽灵读”问题，何谓“幽灵读”，当你通过一区间查询时候，其他事务在这期间插入一条数据并提交，这样你在次查询时，会发现突然多出一行，这就是所谓的“幽灵行”，但是对于innodb跟
falcon，他们通过MVCC解决了这个问题，故对于innodb 跟 falcon，实现了名副其实的可重复读 
如：
show variables like 'tx%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
1）
session A session B
start TRANSACTION; start TRANSACTION;
select * from t4 where i=55; select * from t4 where i=55;
+------+------+ +------+------+ 
| i | j | | i | j | 
+------+------+ +------+------+ 
| 55 | 4 | | 55 | 4 | 
+------+------+ +------+------+ 
1 row in set (0.00 sec) 1 row in set (0.00 sec)

update t4 set j=6 where i=55; 
commit；

select * from t4 where i=55; select * from t4 where i=55; 
+------+------+ +------+------+ 
| i | j | | i | j | 
+------+------+ +------+------+ 
| 55 | 6 | | 55 | 4 | 
+------+------+ +------+------+ 
1 row in set (0.00 sec) 1 row in set (0.00 sec)

像这次就没有出现上一级别中不可能重复读问题
2）
session A session B
start TRANSACTION; start TRANSACTION;
select * from t4 where i>47 and i<55; select * from t4 where i>47 and i<55;
+------+------+ +------+------+ 
| i | j | | i | j | 
+------+------+ +------+------+ 
| 48 | 2 | | 48 | 2 | 
+------+------+ +------+------+ 
1 row in set (0.00 sec) 1 row in set (0.00 sec)

insert into t4 values (50,1); 
commit;

select * from t4 where i>47 and i<55; select * from t4 where i>47 and i<55;
+------+------+ +------+------+ 
| i | j | | i | j | 
+------+------+ +------+------+ 
| 48 | 2 | | 48 | 2 | 
| 50 | 1 | +------+------+ 
+------+------+ 1 row in set (0.00 sec) 
2 rows in set (0.00 sec)

commit;
select * from t4 where i>47 and i<55;
+------+------+ 
| i | j | 
+------+------+ 
| 48 | 2 | 
| 50 | 1 | 
+------+------+ 
2 rows in set (0.00 sec) 
像这样，没有出现幽灵读问题，就是真正的可持续读了，

4：SERIALIZABLE
*别，这一级别是强制事务顺序执行，这样就解决了以上可持续读的问题，但是会出现大量的锁等待与死锁问题

1）
session A SESSION B
start TRANSACTION; start TRANSACTION;
insert into t4 values (52,4);

select * from t4 where i>48 and i<55;
等待中。。。。
commit 
+------+------+ 
| i | j | 
+------+------+ 
| 50 | 1 | 
| 51 | 4 | 
| 52 | 4 | 
+------+------+ 
3 rows in set (6.05 se
2） 
session A SESSION B 
start TRANSACTION; start TRANSACTION;

select * from t4 where i>50 and i<55; select * from t4 where i>48 and i<55;
+------+------+ +------+------+ 
| i | j | | i | j | 
+------+------+ +------+------+ 
| 51 | 4 | | 50 | 1 | 
| 52 | 4 | | 51 | 4 | 
+------+------+ | 52 | 4 | 
2 rows in set (0.00 sec) +------+------+ 
3 rows in set (0.00 sec)

insert into t4 values (53,4); 
等待中 如执行insert 则出现死锁，
session B ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
如 执行commit； session A 中insert 执行
这就是在innodb下，四种隔离级别的具体分析了。很明显，根据自己的需要选择何时的隔离级别，对于提升系统性能也有莫大的帮助

转自：mysql中四种隔离级别的具体分析
http://bbs.linuxtone.org/thread-4781-1-1.html

相关文章