MySQL事务隔离级别(官方文档)

linqiw
1,721 阅读11分钟

一、事务隔离级别

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

         InnoDB提供了SQL:1992标准描述的所有四个事务隔离级别：READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE。InnoDB的默认隔离级别是REPEATABLE READ。

         用户可以使用SET TRANSACTION语句更改单个会话或所有后续连接的事务隔离级别。要为所有连接设置服务器的默认隔离级别，请在命令行或配置文件选项中使用--transaction isolation选项。有关隔离级别和级别设置语法的详细信息，详细的会在下面的设置事务语句进行详细介绍。

         InnoDB使用不同的锁定策略支持这里描述的每个事务隔离级别。对于关键数据上的操作，在ACID遵从性很重要的情况下，可以强制实现与默认REPEATABLE READ级别的高度一致性。或者，可以使用READ COMMITTED甚至READ UNCOMMITTED来放松一致性规则，例如批量报告，在这种情况下，精确一致性和可重复的结果比最小化锁定开销更不重要。SERIALIZABLE执行的规则比REPEATABLE READ更为严格，主要用于特殊情况，例如XA事务一起使用，并用于解决并发和死锁问题。

          下面的列表描述了MySQL如何支持不同的事务级别。列表从最常用的级别到最不常用的级别。

    • REPEATABLE READ
      • 这是InnoDB的默认隔离级别。同一事务内的一致读取由第一次读取建立的快照（MVCC多版本，REPEATABLE READ级别下，开启一个事务，会创建一个快照）。这意味着，如果在同一事务中发出多个普通（非锁定，快照读）SELECT语句，则这些SELECT语句彼此也保持一致，即读到的数据都是相同的数据。
      • 对于锁定读取(当前读，SELECT with FOR UPDATE or FOR SHARE)，更新和删除语句，锁定取决于该语句是使用具有唯一搜索条件的唯一索引，还是使用范围类型搜索条件。
        • 对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不锁定其前面的间隙。
        • 对于其他搜索条件，InnoDB会锁定扫描的索引范围，使用gap locks或next-key locks来阻止其他会话插入到范围覆盖的间隙中，解决幻读的问题。有关gap locks或next-key locks可以看下我的另一篇juejin.cn/post/684490…
    • READ COMMITTED 
      • 即使在同一事务中，每个快照读操作都设置并读取自己的新快照。
      • 对于锁定读取当前读（SELECT with FOR UPDATE or FOR SHARE），UPDATE语句和DELETE语句，InnoDB只锁定索引记录，而不锁定它们之前的间隙，因此 允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。
      • 由于间隙锁定被禁用，可能会出现幻读问题，因为其他会话可以再间隙中插入新行。有关幻读的后续也会对其进行解释翻译。
      • READ COMMITTED隔离级别仅支持row-base的二进制日志记录（row模式的binlog日志，binlog日志模式有三种Statement、Row、Mixed）。如果使用READ COMMITTED和binlog_format=MIXED，服务器将自动使用row-base的日志记录。
      • 使用READ COMMITTED具有其他效果：
        • 对于UPDATE或DELETE语句，InnoDB只对其更新或删除的行持有锁。在MySQL（SERVER层）评估WHERE条件后，将释放非匹配的记录锁，违反了MySql的两阶段锁（加锁和事务提交后解锁）。这大大降低了死锁的可能性，但死锁仍然可能发生。
        • 对于UPDATE语句，如果一行已经被锁定，InnoDB执行“semi-consistent”读取，将最新提交的版本返回给MySQL（SERVER层），这样MySQL就可以确定该行是否与更新的WHERE条件匹配。如果行匹配（必须更新），MySQL会再次读取行，这次InnoDB要么锁定它，要么等待对它的锁定。
      • 请考虑以下示例，从下表开始： 

        • 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;

        • 在这种情况下，表没有索引，因此搜索和索引扫描使用隐藏的聚集索引（主键索引）进行记录锁定，而不是索引列
        • 假设一个会话使用以下语句执行更新： 

          • # Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3; 
        • 还假设第二个会话在第一个会话之后执行这些语句来执行更新： 

          • # Session B
UPDATE t SET b = 4 WHERE b = 2;

        • 当InnoDB执行每个更新时，它首先为每一行获取一个独占锁，然后决定是否修改它。如果InnoDB不修改行，它就会释放锁。否则，InnoDB将保留锁直到事务结束。这会影响以下事务处理。
        • 当使用默认的REPEATABLE READ隔离级别时，SessionA中的更新在它读取的每一行上获取一个x-lock，而不会释放其中的任何一个：

          • 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

        • SessionB中的更新在尝试获取任何锁时立即阻塞(因为SessionA中的更新保留了所有行上的锁)，并且在SessionA中更新提交或回滚之前不会继续:

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

        • 如果改为使用READ COMMITTED，则第一次更新将在其读取的每一行上获取一个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)
        • 对于第二次更新，InnoDB执行“semi-consistent”读取，引擎将读取的每一行的最新提交版本返回给MySQL，以便MySQL可以确定该行是否与更新的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使用索引，那么在获取和保留记录锁时只考虑索引列。在下面的示例中，第一次更新在b=2的每一行上获取并保留一个x锁。第二个更新在尝试获取相同记录上的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的隔离级别可以在启动时设置，也可以在运行时更改。在运行时，可以为所有会话全局设置，也可以为每个会话单独设置。
    • READ UNCOMMITTED 
      • SELECT语句是以非锁定方式执行的，但可能会使用行的早期版本。因此，使用这个隔离级别，这样的读取是不一致的。这也被称为脏读。否则，此隔离级别的工作方式与READ COMMITTED类似。  
    • SERIALIZABLE  
      • 这个级别类似于REPEATABLE READ，但InnoDB隐式地将所有纯SELECT语句转换为 SELECT ... FOR SHARE如果禁用autocommit(autocommit=0)，则用于共享。如果启用自动提交，则选择是其自己的事务。因此，它是只读的，如果作为一致（非锁定）读取执行，则可以序列化，并且不需要为其他事务阻塞。（若要在其他事务已修改选定行时强制阻止纯选择，请禁用“autocommit”（autocommit=0）。）  

二、设置事务语句

  • 语法

    • SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
    ISOLATION LEVEL level
  | access_mode
}

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

access_mode: {
     READ WRITE
   | READ ONLY
}

    • 此语句指定事务特性。它接受一个或多个由逗号分隔的特征值的列表。每个特征值设置事务隔离级别或访问模式。隔离级别用于对InnoDB表的操作。访问模式指定事务是以读/写模式还是只读模式运行。 
    • 此外，SET TRANSACTION可以包含一个可选的全局或会话关键字来指示语句的范围。
      • 事务隔离级别 
        • 要设置事务隔离级别，请使用ISOLATION LEVEL level子句。不允许在同一SET TRANSACTION语句中指定多个隔离级别子句。   
        • 默认隔离级别为REPEATABLE READ。其他允许的值包括READ COMMITTED、READ UNCOMMITTED和SERIALIZABLE。有关这些隔离级别的信息可以看上面的介绍。
      • 事务访问模式 
        • 要设置事务访问模式，请使用READ WRITE or READ ONLY语句。不允许在同一SET TRANSACTION语句中指定多个访问模式子句。
        • 默认情况下，事务以 read/write模式进行，允许事务中使用的表进行读写操作。此模式可以使用具有READ WRITE访问模式的SET TRANSACTION显式指定。
        • 如果事务访问模式设置为read-only模式，则禁止更改表。这可能使存储引擎能够在不允许写入时进行性能改进。
        • 在只读模式下，仍然可以使用DML语句更改使用TEMPORARY关键字创建的表。与永久表一样，不允许使用DDL语句进行更改。
        • 也可以使用START transaction语句为单个事务指定READ WRITE 和 READ ONLY。
      • 事务特征值范围    
        • 你可以为当前会话或仅为下一个事务全局设置事务特征：
          • 使用GLOBAL关键字：
            • 该语句适用于所有后续会话。
            • 现有会话不受影响。
          • 使用SESSION关键字：
            • 该语句适用于当前会话中执行的所有后续事务。
            • 该语句允许在事务中使用，但不影响当前正在进行的事务。
            • 如果在事务之间执行，则该语句将重写设置命名特征的下一个事务值的任何前一个语句。
          • 没有任何SESSION或GLOBAL关键字：
            • 该语句仅适用于会话中执行的下一个事务。
            • 后续事务将恢复为使用会话的特征值。
            • 在以下事务中不允许使用该语句：

              • mysql> START TRANSACTION;
Query OK, 0 rows affected (0.02 sec)

mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR 1568 (25001): Transaction characteristics can't be changed
while a transaction is in progress  
          • 更改全局事务特性需要CONNECTION_ADMIN权限（或已弃用的SUPER权限，超级权限在8.0废弃）。任何会话都可以自由更改其会话特征（即使在事务的中间）或下一个事务的特征（在该事务开始之前）。
          • 要在服务器启动时设置全局隔离级别，请在命令行或选项文件中使用--transaction isolation=level选项。此选项的level值使用破折号而不是空格，因此允许的值为READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ或SERIALIZABLE。
          • 类似地，要在服务器启动时设置全局事务访问模式，请使用--transaction read-only选项。默认值为OFF（read/write模式），但对于 read only模式，可以将该值设置为ON.
          • 例如，要将隔离级别设置为可重复读取，并将访问模式设置为读写，请在选项文件的[mysqld]部分中使用以下行： 

            • [mysqld]
transaction-isolation = REPEATABLE-READ
transaction-read-only = OFF
               
          • 如前所述，在运行时，可以使用set transaction语句间接设置全局、会话和下一个事务范围级别的特性。也可以使用set语句直接设置它们，为事务隔离和事务只读系统变量赋值： 
            • SET TRANSACTION允许在不同的作用域级别设置允许可选的GLOBAL和SESSION关键字。  
            • 用于将值分配给transaction_isolation和transaction_read_only系统变量的SET语句具有用于在不同范围级别设置这些变量的语法。
            • 下表显示了由每个集合事务和变量分配语法设置的特征范围级别。 
            • 以下表设置事务特征的事务语法
              •   
            • 以下表事务特性的设置语法


            • 可以在运行时检查事务特性的全局值和会话值： 

              • SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;

                 


avatar
文章
阅读
粉丝