Mysql InnoDB 的隔离级别的实现

末蓝、 2023-06-18 10:53 24阅读 0赞

# InnoDB 的 隔离级别的实现 #

InnoDB使用不同的锁定策略支持此处描述的每个事务隔离级别。

事务隔离是数据库处理的基础之一。隔离是缩写ACID中的I；隔离级别是一种设置，用于在多个事务同时进行更改和执行查询时微调性能与结果的可靠性，一致性和可重复性之间的平衡。

您可以在默认的REPEATABLE READ级别上实现高度的一致性，以实现对重要数据的操作（其中ACID遵从性很重要）。

或者，在批量报告之类的情况下，精确的一致性和可重复的结果不如最小化锁定开销重要，可以使用READ COMMITTED甚至READ UNCOMMITTED放松一致性规则。

SERIALIZABLE强制执行比REPEATABLE READ更严格的规则，并且主要用于特殊情况下，例如XA事务以及对并发和死锁问题进行故障排除。

不同隔离级别事务下面的锁

[https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html][https_dev.mysql.com_doc_refman_5.6_en_innodb-transaction-isolation-levels.html]

## REPEATABLE READ ##

1.  这是InnoDB引擎的默认隔离级别。在单个事务里面会保证一致性读取，会在第一次读的时候对数据打快照。这意味着，如果您在同一事务中发出多个普通（非锁定）SELECT语句，则这些SELECT语句彼此之间也是一致的。
2.  对于锁定读取（使用FOR UPDATE或LOCK IN SHARE MODE进行锁定的SELECT），UPDATE和DELETE语句，锁定取决于该语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。

*  对于把唯一索引作为条件的，InnoDB只会锁住被索引搜索到的数据。
 *  对于其他搜索条件，InnoDB使用Gap Locks或Next-Key Locks 来锁定扫描的索引范围，以阻止其他会话插入该范围覆盖的间隔。

## READ COMMITTED ##

# #

*  即使在同一事务中，每个一致的读取都将设置并读取其自己的新快照。而不是整个事务的快照。
 *  对于锁定读取（使用FOR UPDATE或LOCK IN SHARE MODE进行锁定的读取），UPDATE语句和DELETE语句，InnoDB仅锁定索引记录，而不锁定它们之间的间隙，因此允许在锁定记录旁边自由插入新记录。 Gap locking 仅用于外键约束检查和重复键检查。由于禁用了Gap locking ，因此可能会产生幻影问题，因为其他会话可以在间隙中插入新行。

\------------------------------ 例子 演示 ----------------------------------------------

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;

在这个情况下面，这个表没有索引，，因此搜索和索引扫描使用隐藏的聚集索引进行记录锁定

\------------------------------------------------------------------------------------

假设一个会话使用以下语句执行UPDATE：

\# Session A

START TRANSACTION; 
    UPDATE t SET b = 5 WHERE b = 3;

还假设第二个会话通过在第一个会话的语句之后执行以下语句来执行UPDATE：

\# Session B

UPDATE t SET b = 4 WHERE b = 2;

当InnoDB执行每个UPDATE时，它首先为其读取的每一行获取一个排他锁，然后确定是否对其进行修改。如果InnoDB不修改该行，它将释放锁定。否则，InnoDB将保留该锁直到事务结束。这会影响事务处理，如下所示。

当使用默认的REPEATABLE READ隔离级别时，第一个UPDATE在它读取的每一行上获取一个x锁，并且不释放其中的任何一个：（这里的读取到的每一行的都上锁）

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

第二个UPDATE在尝试获取任何锁时立即阻塞（因为第一个更新已在所有行上保留了锁），并且直到第一个UPDATE提交或回滚后才继续进行：

x-lock(1,2); block and wait for first UPDATE to commit or roll back

如果改用READ COMMITTED，则第一个UPDATE在它读取的每一行上获取一个x锁，并释放未修改的行的x锁：

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)

对于第二个UPDATE，InnoDB进行“半一致”读取，将它读取的每一行的最新提交版本返回给MySQL，以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配：

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

但是，**如果WHERE条件包含索引列，并且InnoDB使用索引，则在获取和保留记录锁时仅考虑索引列。**

在下面的示例中，第一个UPDATE在b = 2的每一行上获取并保留一个x锁。第二个UPDATE在尝试获取同一记录上的x锁时会阻塞，因为它也使用在b列上定义的索引

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
    INSERT INTO t VALUES (1,2,3),(2,2,4);
    COMMIT;
    
    # Session A
    START TRANSACTION;
    UPDATE t SET b = 3 WHERE b = 2 AND c = 3;
    
    # Session B
    UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

**    使用READ COMMITTED具有其他效果：**

*  对于UPDATE或DELETE语句，InnoDB仅对其更新或删除的行持有锁。MySQL评估WHERE条件后，将释放不匹配行的记录锁。这大大降低了死锁的可能性，但是仍然可以发生。
 *  对于UPDATE语句，如果某行已被锁定，则InnoDB将执行“半一致”读取，将最新的提交版本返回给MySQL，以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配。如果该行匹配（必须更新），则MySQL会再次读取该行，这一次InnoDB将其锁定或等待对其进行锁定。

## READ UNCOMMITTED ##

SELECT语句以非锁定方式执行，但是可能会使用行的早期版本。因此，使用此隔离级别，此类读取不一致。这也称为脏读。

## SERIALIZABLE ##

# #

这个级别类似与可重复读，如果自动提交被关闭的下，但是InnoDB会隐式的为Selete语句添加上共享锁。如果开启了自动提交，那么这个select就是一个事务。因此，它被认为是只读的，如果作为一致（非锁定）读取执行并且不需要阻塞其他事务，则可以序列化（如果其他事务已修改所选行，则要强制普通SELECT阻止，请禁用自动提交。）

## ReadCommited 与 RepeatableRead区别 ##

1. 对于没有锁的Select，RC 会为每一个语句建立起自己的快照，而在RR里面，同一个事务里面的同一个语句共同拥有一份快照。

2. 对于待有for update 的select 、 update、 delete。RC只会锁住当前查询到的行，所以可以新增行不会被锁定，只会进行外键约束检查和重复键检查。而在RRwhere语句是唯一索引的情况下才会值锁定对应的行，如果是普通的不带有索引的数据行，会用gap锁和next-key锁。

[https_dev.mysql.com_doc_refman_5.6_en_innodb-transaction-isolation-levels.html]: https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html