MySql Innodb事务隔离级别

最新推荐文章于 2024-10-05 12:48:15 发布

Fibona

最新推荐文章于 2024-10-05 12:48:15 发布

阅读量1.1k

点赞数

分类专栏： mysql 文章标签：大数据

本文链接：https://blog.csdn.net/fibona/article/details/125713839

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

谈Mysql事务，必须要谈事务隔离级别，否则就是耍流氓，当然，你想怎么耍流氓我不负责。

本文档来源于MySql官方文档翻译及整理，可以参考官方文档原文，也许本人的英文翻译出现偏差，用词不当，敬请谅解。因为以前也看了对应的文档，没有形成文字，虽然知道大体意思，但是不好做为正式语言传给同行，听了几次其它人的对隔离级别的了解，我总认为与我看到的原文不太一致，所以今天抽空用自己的话形成下面的文档，供以后学习使用。

参考原文:

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

Mysql Innodb的四种事务隔离级别如下：

1、REPEATABLE READ

2、READ COMMITTED

3、READ UNCOMMITTED

4、SERIALIZABLE

下面进行具体介绍

Repeatable Read 级别:

根据是否使用索引进行对应的记录锁定或者范围锁定

如果是唯一索引并且用的是唯一查询条件，则只锁定查询条件对应的唯一记录，不会采用Gap 锁

如果是其它查询条件，则锁定查询到的索引记录，使用间隙锁，或者Next-Key-Lock锁定指定的区间阻塞其它会话进行插入处理

Read Committed 级别:

Innodb 只锁定索引对应的记录，不会增加对应的Gap Lock,因此在锁定的记录旁边允许自由的插入新的记录。但是会在外键的约束检查及唯一性键检查时使用。

因为Read Commit级别禁用了Gap Lock，所以会出现幻读，会读取其它事务新插入的记录，其它事务会插入新记录到间隙中。

只有基于Row-Based Binary logger才会支持Read Committed隔离级别。

特别地:

在Read Committed 级别下，对于Update ,Delete语句，Innodb只会持有对应的更新或者删除行的锁，对于未符合条件的记录，在where 条件计算时，就会移除对应的行级锁。这样主要是为了减少锁的并发，减少死锁，当然只是减少。

对于Update语句，如果某一行已经被锁定，Innodb将会执行半一致性（semi-consistent

）读，即将最新提交的版本返回给MySQL，以便MySQL可以确定该行是否匹配更新的WHERE条件。如果行匹配（必须更新），MySQL会再次读取该行，这次InnoDB要么锁定它，要么等待锁定。

1、验证Repeatable Read下，没有索引，采用隐式的索引，会锁定所有的记录

示例:

创建一个表不带索引:

drop table if exists t;

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;

INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);

COMMIT;

设置隔离级别为repeatable read,并且执行事务

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 

set sql_safe_updates=0;

set autocommit = 0;

START TRANSACTION;

UPDATE t SET b = 5 WHERE b = 3;

然后开启另一个会话，执行：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

set sql_safe_updates=0;

set autocommit = 0;

执行条件更新：

UPDATE t SET b = 4 WHERE b = 2

在 Repeatable Read隔离级别下面，第二个会话，会执行等待，待超时时会提示获取锁失败

---TRANSACTION 364387, ACTIVE 490 sec

1 lock struct(s), heap size 1136, 2 row lock(s)

MySQL thread id 1255801, OS thread handle 140523396491008, query id 7379164 123.127.x.x db1

Trx read view will not see trx with id >= 364387, sees < 364386

---TRANSACTION 364386, ACTIVE 557 sec

2 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2

MySQL thread id 1253989, OS thread handle 140523377051392, query id 7379761

117.15.x.x  db1 starting

show engine innodb status

因为表没有索引，所以会在第一条语句执行时，锁定所有的行数，直接事务提交，所以导致第二个会话，锁超时后，提示获取锁失败。

即在第一个语句执行完成后，未Commit情况下会:

x-lock(1,2); retain x-lock

x-lock(2,3); update(2,3) to (2,5); retain x-lock

x-lock(3,2); retain x-lock

x-lock(4,3); update(4,3) to (4,5); retain x-lock

x-lock(5,2); retain x-lock

2、验证Read Committed级别下，对没有匹配的记录会移除索引

验证在Read Committed隔离级别下，对于Update ,Delete语句，Innodb只会持有对应的更新或者删除行的锁，对于未符合条件的记录，在where 条件计算时，就会移除对应的行级锁。

1、开启一个会话：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

set sql_safe_updates=0;

set autocommit = 0;  



START TRANSACTION;

UPDATE t SET b = 5 WHERE b = 3;

开启另一个会话,执行条件更新：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

set sql_safe_updates=0;

set autocommit = 0;  

UPDATE t SET b = 4 WHERE b = 2

通过上面的判断，第二个语句正确执行了，没有进行等待对应的锁

即：第一个语句执行完，未进行Commit前，innodb会将未匹配的锁进行释放:

x-lock(1,2); unlock(1,2)

x-lock(2,3); update(2,3) to (2,5); retain x-lock

x-lock(3,2); unlock(3,2)

x-lock(4,3); update(4,3) to (4,5); retain x-lock

x-lock(5,2); unlock(5,2)

3、验证半一致性读(semi-consistent)

第一个会话不变，因为执行完事务未提交

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

set sql_safe_updates=0;

set autocommit = 0;  



START TRANSACTION;

UPDATE t SET b = 5 WHERE b = 3;

然后第二个话，执行第一个语句的反向的条件更新

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

set sql_safe_updates=0;

set autocommit = 0; 

UPDATE t SET b = 3 WHERE b = 5；

这时候会阻塞，直到事务超时:

原因为是第一个语句执行完成未提交时数据库的状态如下:

x-lock(1,2); update(1,2) to (1,4); retain x-lock

x-lock(2,3); unlock(2,3)

x-lock(3,2); update(3,2) to (3,4); retain x-lock

x-lock(4,3); unlock(4,3)

x-lock(5,2); update(5,2) to (5,4); retain x-lock

具体事务信息如下:

LOCK WAIT 3 lock struct(s), heap size 1136, 9 row lock(s), undo log entries 4

MySQL thread id 1255801, OS thread handle 140523396491008, query id 7382755 123.127.93.146 db1 updating

UPDATE t SET b = 3  WHERE b = b

------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1081 page no 4 n bits 72 index GEN_CLUST_INDEX of table `db1`.`t` trx id 364416 lock_mode X locks rec but not gap waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

4、测试在Read Committed隔离级别，有索引，会锁定条件对应的索引

创建表：

CREATE TABLE t2 (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;

INSERT INTO t2 VALUES (1,2,3),(2,2,4);

COMMIT;

开启第一个会话，执行：

START TRANSACTION;

UPDATE t2 SET b = 3 WHERE b = 2 AND c = 3;

开启第二个会话执行:

UPDATE t2 SET b = 4 WHERE b = 2 AND c = 4;

结果如下：

锁状态如下:

---TRANSACTION 364416, ACTIVE 2205 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1136, 10 row lock(s), undo log entries 3

MySQL thread id 1255801, OS thread handle 140523396491008, query id 7386336 123.127.x.x db1 Searching rows for update

UPDATE t2 SET b = 4 WHERE b = 2 AND c = 4

------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1082 page no 5 n bits 72 index b of table `db1`.`t2` trx id 364416 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

 0: len 4; hex 80000002; asc     ;;

 1: len 6; hex 00000003880f; asc       ;;

虽然条件出来的结果不一致，但是都是索引b=2,所以导致了索引b=2对应的记录进行了锁定，

5、验证在Repeatable Read隔离级别下，有索引，会锁定索引值对应的行

开启第一个会话，执行：


SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

UPDATE t2 SET b = 3 WHERE b = 2 AND c = 3;

开启第二个会话,执行:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

UPDATE t2 SET b = 4 WHERE b = 2 AND c = 4;

结果如下

数据库中的锁信息如下:

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 1255801, OS thread handle 140523396491008, query id 7387428 123.127.x.x db1 Searching rows for update

UPDATE t2 SET b = 4 WHERE b = 2 AND c = 4

------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1082 page no 5 n bits 72 index b of table `db1`.`t2` trx id 364440 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

 0: len 4; hex 80000002; asc     ;;

 1: len 6; hex 00000003880f; asc       ;;

同样，虽然条件出来的结果不一致，但是都是索引b=2,所以导致了索引b=2对应的记录进行了锁定，

Read Uncommitted 级别

该级别下，所有的读(Select)都执行在一个非阻塞模式下，但是可能会使用数据的早期版本，所以这样的读取结果是不一致的，也就是常说的脏读。其它方式(是指Update,Delete)类似于Read Committed,在此不做常规验证。

6、验证索引锁定如下

开启第一个会话，执行：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION;

UPDATE t2 SET b = 3 WHERE b = 2 AND c = 3;

第二个会话开启执行，

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE t2 SET b = 4 WHERE b = 2 AND c = 4;

执行结果同4，5，有索引的情况

SERIALIZABLE 隔离级别

该级别同Repeatable Read。

但是会做如下处理，主要是指读，即Select语句，所谓的串行化，是指在该级别下可以不需要使用事务，读就像在事务中执行一样，是一致性执行的。

如果禁用自动提交，则InnoDB隐式地将所有普通SELECT语句转换SELECT xxx for Share ，用于共享锁的获取。如果启用了自动提交，则SELECT为其自己的事务。

因此，已知它是只读的，如果作为一致（非锁定）读取执行，则可以串行化，并且不需要为其他事务进行阻塞。

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)