mysql隔离级别面试 mysql innodb 隔离级别

转载

mob64ca1419e0cc 2024-01-08 19:46:28

文章标签 mysql隔离级别面试 mysql innodb隔离级别数据隔离级别不可重复读 文章分类 MySQL 数据库

SQL标准中的事务四种隔离级别

隔离级别

脏读(Dirty Read)

不可重复读(NonRepeatable Read)

幻读(Phantom Read)

未提交读(Read uncommitted)

可能

已提交读(Read committed)

不可能

可能

可重复读(Repeatable read)

不可能

可能

可串行化(Serializable )

不可能

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

未提交读(Read uncommitted)

数据库一般都不会用，而且任何操作都不会加锁

mysql> select * from tx_test;
+----+-------+
| id | value |
+----+-------+
| 1 | a |
+----+-------+
1 row in set (0.00 sec)

由于MySQL的InnoDB默认是使用的RR级别，所以我们先要将该session开启成RU级别

SET session transaction isolation level read uncommitted;
____________________________________________________________________________________________
事务A 事务B
____________________________________________________________________________________________
begin;
begin;
____________________________________________________________________________________________
select * from tx_test;
______________
id value
______________
1 a
______________
____________________________________________________________________________________________
insert into tx_test(value) values('b');
____________________________________________________________________________________________
select * from tx_test;
______________
id value
______________
1 a
______________
2 b
______________
读到事务A还未commit的行，出现脏读
_____________________________________________________________________________________________
commit;
commit;
_____________________________________________________________________________________________

已提交读(Read committed)

在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。

SET session transaction isolation level read committed;
SET SESSION binlog_format = 'ROW';(或者是MIXED)
______________________________________________________________________________________________________________________________________
事务A 事务B
______________________________________________________________________________________________________________________________________
begin; begin;
______________________________________________________________________________________________________________________________________
update tx_test set value='aa' where id=1; update tx_test set value='aaa' where id=1;
______________________________________________________________________________________________________________________________________
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
_______________________________________________________________________________________________________________________________________
commit;
________________________________________________________________________________________________________________________________________

为了防止并发过程中的修改冲突，事务A中MySQL给id=1的数据行加锁，并一直不commit(释放锁)，那么事务B也就一直拿不到该行锁，wait直到超时。

可重复读(Repeatable read)

这是MySQL中InnoDB默认的隔离级别。我们姑且分“读”和“写”两个模块来讲解。

读

读就是可重读，可重读这个概念是一事务的多个实例在并发读取数据时，会看到同样的数据行。

RC模式下的展现(不可重读)

__________________________________________________________________________________________________________
事务A 事务B
__________________________________________________________________________________________________________
begin;
begin;
__________________________________________________________________________________________________________
select * from tx_test;
______________
id value
______________
1 aa
______________
2 b
______________
____________________________________________________________________________________________________________
update tx_test set value='bb' where id=2;
____________________________________________________________________________________________________________
commit;
____________________________________________________________________________________________________________
select * from tx_test;
______________
id value
______________
1 aa
______________
2 bb
______________

读到了事务B修改的数据，和第一次查询的结果不一样，

是不可重读的。

_____________________________________________________________________________________________________________
commit
______________________________________________________________________________________________________________

事务B修改id=2的数据提交之后，事务A同样的查询，后一次和前一次的结果不一样，这就是不可重读(重新读取产生的结果不一样)。这就很可能带来一些问题，那么我们来看看在RR级别中MySQL的表现：

事务A 事务B事务C
______________________________________________________________________________________________________________________________________________________________________
begin;
begin;begin;
_______________________________________________________________________________________________________________________________________________________________________
select * from tx_test;
______________
id value
______________
1 aa
______________
2 bb
______________
________________________________________________________________________________________________________________________________________________________________________
update tx_test set value='aaa' where id=1; insert into tx_test(value) values('c');
_________________________________________________________________________________________________________________________________________________________________________
___
commit; commit;
__________________________________________________________________________________________________________________________________________________________________________
select * from tx_test;
______________
id value
______________
1 aa
______________
2 bb
______________

没有读到事务B修改的数据，和第一次sql读取的一样，是可重复读的。

没有读到事务C新添加的数据。

______________________________________________________________________________________________________________________________________________________________________________
commit
______________________________________________________________________________________________________________________________________________________________________________

我们注意到，事务A先做了一次读取，事务B中间修改了id=1的数据，并commit之后，事务A第二次读到的数据和第一次完全相同。所以说它是可重读的。

不可重复读和幻读的区别

不可重复读重点在于update和delete，而幻读的重点在于insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

上文说的，是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC(多版本并发控制)来避免这两种问题。

可串行化(Serializable )

读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。