mysql-repeatable read可以避免幻读

最新推荐文章于 2024-07-22 14:59:56 发布

xts5701046

最新推荐文章于 2024-07-22 14:59:56 发布

阅读量3k

点赞数 4

分类专栏：极力推荐文章标签：最新读

极力推荐专栏收录该内容

5 篇文章 3 订阅

订阅专栏

事务隔离级别的语义：当前事务执行过程中，通过select，update，delete 操作，对其他事务的影响，反过来也是如此，通俗的说就是当前事务是否可以看到其他事务的操作结果。

1、如何查询当前数据库的隔离级别（我们只讨论mysql的事务隔离级别）

select @@tx_isolation;
SELECT @@session.tx_isolation; 
SELECT @@global.tx_isolation; 
//设置
set tx_isolation='read-committed';

2、不同隔离级别的影响

ANSI/ISO SQL标准定义了4中事务隔离级别：未提交读（read uncommitted），提交读（read committed），重复读（repeatable read），串行读（serializable）。

对于不同的事务，采用不同的隔离级别分别有不同的结果。不同的隔离级别有不同的现象。主要有下面3种现在：

1、脏读（dirty read）：一个事务可以读取另一个尚未提交事务的修改数据。

2、不可重复读（nonrepeatable read）：在同一个事务中，同一个查询在T1时间读取某一行，在T2时间重新读取这一行时候，这一行的数据已经发生修改，可能被更新了（update），也可能被删除了（delete）。

3、幻像读（phantom read）：在同一事务中，同一查询多次进行时候，由于其他插入操作（insert）的事务提交，导致每次返回不同的结果集。

不同的隔离级别有不同的现象，并有不同的锁定/并发机制，隔离级别越高，数据库的并发性就越差，4种事务隔离级别分别表现的现象如下表：

隔离级别	脏读	非重复读	幻像读
read uncommitted	允许	允许	允许
read committed		允许	允许
repeatable read			允许
serializable

mysql 默认的隔离级别是：REPEATABLE-READ

注意：

1、repeatable read 允许幻读，这是ANSI/ISO SQL标准的定义要求，运行幻读依然有非常大的隐患，mysql 在repeatable read 即可满足没有幻读的要求。

2、不可重复读和幻读的区别：不可重复读的重点是修改，幻读的重点是插入或者删除了新数据。两都会造成系统错误，但是避免的方法则区别比较大，对于前者, 只需要锁住满足条件的记录，对于后者, 要锁住满足条件及其相近的记录。

举例验证：

1、建表

CREATE TABLE `t_pai` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `name` varchar(10) NOT NULL,
   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
   PRIMARY KEY (`id`),
   KEY `idx_update_time` (`update_time`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、验证不存在幻读

client1 ：SET autocommit= 0;

client2：SET autocommit= 0;

client1：START TRANSACTION;

client2：

START TRANSACTION;

INSERT INTO t_pai (`name`) VALUE ("2");

COMMIT;

client1：SELECT * FROM t_pai;

结果：查询不到数据：

client1：

COMMIT;

SELECT * FROM t_pai;

结果：有数据。

深层次的原理分析：

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。

快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)
- select * from table where ?;
当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
- select * from table where ? lock in share mode;
- select * from table where ? for update;
- insert into table values (…);
- update table set ? where ?;
- delete from table where ?;
所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

MySQL/InnoDB定义的4种隔离级别：

Read Uncommited

可以读取未提交记录。此隔离级别，不会使用，忽略。
Read Committed (RC)

快照读忽略，本文不考虑。

针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。
Repeatable Read (RR)

快照读忽略，本文不考虑。

针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。
Serializable

从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。

Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。