欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL事务隔离级别与相关示例(脏读、不可重复读、幻读)

程序员文章站 2022-06-25 08:43:40
MySQL8中隔离级别的变量跟之前的版本不一样,之前是tx_isolation,MySQL8改成了transaction_isolation。查看当前隔离级别的命令是 其它参考: "MySQL 四种事务隔离级的说明" 未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中 ......

mysql8中隔离级别的变量跟之前的版本不一样,之前是tx_isolation,mysql8改成了transaction_isolation。查看当前隔离级别的命令是

mysql> select @@global.transaction_isolation,@@transaction_isolation;

+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| repeatable-read                | repeatable-read         |
+--------------------------------+-------------------------+

其它参考:mysql 四种事务隔离级的说明

  • 未提交读(read uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据
  • 提交读(read committed):只能读取到已经提交的数据。oracle等多数数据库默认都是该级别 (不重复读)
  • 可重复读(repeated read):可重复读。在同一个事务内的查询都是事务开始时刻一致的,innodb默认级别。在sql标准中,该隔离级别消除了不可重复读,但是还存在幻象读
  • 串行读(serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

以下内容参考了*:事务隔离

创建测试表users并插入测试数据

mysql> create table users (id int(11) not null, name varchar(20), age int(11), primary key(id)) engine=innodb;

mysql> insert into users values (1, 'joe', 20), (2, 'jill', 25);

mysql> select * from users;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | joe  |   20 |
|  2 | jill |   25 |
+----+------+------+

脏读(dirty reads)

示例1:隔离级别是未提交读(read uncommitted),导致脏读(dirty read)。在我们的例子中,事务2修改了一行,但是没有提交,事务1读了这个没有提交的数据。现在如果事务2回滚了刚才的修改或者做了另外的修改的话,事务1中查到的数据就是不正确的了。在这个例子中,事务2回滚后就没有id是1,age是21的数据行了。

-- 设置隔离级别为未提交读
set session transaction isolation level read uncommitted;

             session a                              session b

           start transaction;                       start transaction;
time
|          /* query 1 */
|          select age from users where id = 1;
|          /* will read 20 */
|                                                   /* query 2 */
v                                                   update users set age = 21 where id = 1;
                                                    /* no commit here */

           /* query 1 */
           select age from users where id = 1;
           /* will read 21 */
                                                    rollback; /* lock-based dirty read */

不可重复读(non-repeatable reads)

示例2:隔离级别是读已提交(read committed),导致不可重复读。在这个例子中,事务2提交成功,因此他对id为1的行的修改就对其他事务可见了。但是事务1在此前已经从这行读到了另外一个“age”的值。在可串行化(serializable)和可重复读的隔离级别,数据库在第二次select请求的时候应该返回事务2更新之前的值。在提交读和未提交读,返回的是更新之后的值,这个现象就是不可重复读。

-- 设置隔离级别为提交读
set session transaction isolation level read committed;

             session a                              session b

           start transaction;                       start transaction;
time
|          /* query 1 */
|          select * from users where id = 1;
|          /* will read age=20 */
|                                                   /* query 2 */
v                                                   update users set age = 21 where id = 1;
                                                    commit; /* in multiversion concurrency 
                                                        control, or lock-based read committed */

           /* query 1 */
           select * from users where id = 1;
           /* will read age=21 */
           commit; /* lock-based repeatable read */

有两种策略可以避免不可重复读。一个是要求事务2延迟到事务1提交或者回滚之后再执行。这种方式实现了t1, t2 的串行化调度。串行化调度可以支持可重复读。

另一种策略是多版本并发控制。为了得到更好的并发性能,允许事务2先提交。但因为事务1在事务2之前开始,事务1必须在其开始执行时间点的数据库的快照上面操作。当事务1最终提交时候,数据库会检查其结果是否等价于t1, t2串行调度。如果等价,则允许事务1提交,如果不等价,事务1需要回滚并抛出个串行化失败的错误。

使用基于锁的并发控制,在可重复读的隔离级别中,id=1的行会被锁住,在事务1提交或回滚前一直阻塞语句2的执行。在提交读的级别,语句1第二次执行,age已经被修改了。

在多版本并发控制机制下,可序列化(serializable)级别,两次select语句读到的数据都是事务1开始的快照,因此返回同样的数据。但是,如果事务1试图update这行数据,事务1会被要求回滚并抛出一个串行化失败的错误。

在提交读隔离级别,每个语句读到的是语句执行前的快照,因此读到更新前后不同的值。在这种级别不会有串行化的错误(因为这种级别不要求串行化),事务1也不要求重试。

幻影读(phantom reads)

以下内容参考了:

幻读错误的理解:说幻读是 事务a 执行两次 select 操作得到不同的数据集,即 select 1 得到 10 条记录,select 2 得到 11 条记录。这其实并不是幻读,这是不可重复读的一种,只会在 r-u r-c 级别下出现,而在 mysql 默认的 rr 隔离级别是不会出现的。

幻读,并不是说两次读取获取的结果集不同,幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些:select 某记录是否存在,不存在,准备插入此记录,但执行 insert 时发现此记录已存在,无法插入,此时就发生了幻读。

-- 设置隔离级别为可重复读
set session transaction isolation level repeatable read;

             session a                                  session b

           start transaction;                           start transaction;
time
|          /* query 1 */
|          select * from users where id = 3;
|          /* empty set */
|                                                       /* query 2 */
v                                                       insert into users values (3, 'woody', 28);
                                                        commit;

           /* query 3 */
           insert into users values (3, 'woody', 28);
           /* error 1062 (23000): duplicate entry '3' 
              for key 'primary' */
           
           /* query 4 */
           select * from users where id = 3;
           /* empty set */
           commit;

会话a :主事务,检测表中是否有 id 为 3 的记录,没有则插入,这是我们期望的正常业务逻辑。

会话b :干扰事务,目的在于扰乱 会话a 的正常的事务执行。

在 rr 隔离级别下,query 1、query 2 是会正常执行的,query 3 则会报错主键冲突,对于 会话a 的业务来说是执行失败的,这里 会话a 就是发生了幻读,因为 会话a 在 query 1 中读取的数据状态并不能支撑后续的业务操作,会话a:“见鬼了,我刚才读到的结果应该可以支持我这样操作才对啊,为什么现在不可以”。会话a 不敢相信的又执行了 query 4,发现和 query 1 读取的结果是一样的(rr下的 mmvc机制)。此时,幻读无疑已经发生,t1 无论读取多少次,都查不到 id = 3 的记录,但它的确无法插入这条他通过读取来认定不存在的记录(此数据已被会话b插入),对于 会话a 来说,它幻读了。

其实 rr 也是可以避免幻读的,通过对 select 操作手动加 行x锁(select ... for update 这也正是 serializable 隔离级别下会隐式为你做的事情),同时还需要知道,即便当前记录不存在,比如 id = 3 是不存在的,当前事务也会获得一把记录锁(因为innodb的行锁锁定的是索引,故记录实体存在与否没关系,存在就加 行x锁,不存在就加 next-key lock间隙x锁),其他事务则无法插入此索引的记录,故杜绝了幻读。

在 serializable 隔离级别下,step1 执行时是会隐式的添加 行(x)锁 / gap(x)锁的,从而 query2 会被阻塞,query3 会正常执行,待 t1 提交后,t2 才能继续执行(主键冲突执行失败),对于 t1 来说业务是正确的,成功的阻塞扼杀了扰乱业务的t2,对于t1来说他前期读取的结果是可以支撑其后续业务的。

所以 mysql 的幻读并非什么读取两次返回结果集不同,而是事务在插入事先检测不存在的记录时,惊奇的发现这些数据已经存在了,之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思,第一次select是读取,第二次的 insert 其实也属于隐式的读取,只不过是在 mysql 的机制中读取的,插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达 读-读,幻读则是说 读-写,用写来证实读的是鬼影。

rr级别下防止幻读

rr级别下只要对 select 操作也手动加行(x)锁即可类似 serializable 级别(它会对 select 隐式加锁),即大家熟知的:

# 这里需要用 x锁, 用 for share 拿到 s锁 后我们没办法做 写操作
select `id` from `users` where `id` = 3 for update;

如果 id = 3 的记录存在则会被加行(x)锁,如果不存在,则会加 next-lock key / gap 锁(范围行锁),即记录存在与否,mysql 都会对记录应该对应的索引加锁,其他事务是无法再获得做操作的。

这里我们就展示下 id = 3 的记录不存在的场景,for update 也会对此 “记录” 加锁,要明白,innodb 的行锁(gap锁是范围行锁,一样的)锁定的是记录所对应的索引,且聚簇索引同记录是直接关系在一起的。

-- 设置隔离级别为可重复读
set session transaction isolation level repeatable read;

             session a                                  session b

           start transaction;                           start transaction;
time
|          /* query 1 */
|          select * from users where id = 3 for update;
|          /* empty set */
|                                                       /* query 2 */
v                                                       insert into users values (3, 'woody', 28);
                                                        /* 被阻塞,error 1205 (hy000): lock wait timeout exceeded; 
                                                           try restarting transaction */

           /* query 3 */
           insert into users values (3, 'woody', 28);
           /* query ok, 1 row affected */
           
           commit;
           /* query ok, 0 rows affected */

serializable级别杜绝幻读

在此级别下,我们便不需要对 select 操作显式加锁,innodb会自动加锁,事务安全,但性能很低。

-- 设置隔离级别为可串行化
set session transaction isolation level serializable;

             session a                                  session b

           start transaction;                           start transaction;
time
|          /* query 1 */
|          select * from users where id = 4;
|          /* empty set */
|                                                       /* query 2 */
v                                                       insert into users values (4, 'bill', 29);
                                                        /* 被阻塞,error 1205 (hy000): lock wait 
                                                           timeout exceeded; try restarting transaction */

           /* query 3 */
           insert into users values (4, 'bill', 29);
           /* query ok, 1 row affected */
           
           commit;
           /* query ok, 0 rows affected */

step1: 会话a 查询 id = 4 的记录,innodb 会隐式的对齐加 x锁
step2: 会话b 插入 id = 4 的记录,被阻塞
step3: 会话a 插入 id = 4 的记录,成功执行(会话b 依然被阻塞中)
step4: 会话a 成功提交(会话b 此时唤醒但主键冲突执行错误)
会话a事务符合业务需求成功执行,会话b干扰会话a失败。

总结

rr 级别作为 mysql 事务默认隔离级别,是事务安全与性能的折中,可能也符合二八定律(20%的事务存在幻读的可能,80%的事务没有幻读的风险),我们在正确认识幻读后,便可以根据场景灵活的防止幻读的发生。

serializable 级别则是悲观的认为幻读时刻都会发生,故会自动的隐式的对事务所需资源加排它锁,其他事务访问此资源会被阻塞等待,故事务是安全的,但需要认真考虑性能。

innodb的行锁锁定的是索引,而不是记录本身,这一点也需要有清晰的认识,故某索引相同的记录都会被加锁,会造成索引竞争,这就需要我们严格设计业务sql,尽可能的使用主键或唯一索引对记录加锁。索引映射的记录如果存在,加行锁,如果不存在,则会加 next-key lock / gap 锁 / 间隙锁,故innodb可以实现事务对某记录的预先占用,如果记录存在,它就是本事务的,如果记录不存在,那它也将是本是无的,只要本是无还在,其他事务就别想占有它。

另外可以参考:以后别再说你不懂mysql中的「幻读」了