Mysql_事务隔离级别

事务隔离级别

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

InnoDB报价由SQL描述的所有四个事务隔离级别：1992标准： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。默认隔离级别InnoDB是 REPEATABLE READ。

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

InnoDB支持使用不同的锁定策略在此描述的每个事务隔离级别 。您可以在默认数据REPEATABLE READ级别上实现高度一致性 ，以便对重要的ACID合规性数据进行操作 。或者，在批量报告之类的情况下，精确的一致性和可重复的结果与最小化锁定开销相比，不那么重要，您READ COMMITTED甚至可以放宽一致性规则，甚至 可以放宽一致性规则 READ UNCOMMITTED。 SERIALIZABLE实施比甚至更严格的规则REPEATABLE READ，并且主要用于特殊情况下（例如XA）事务以及用于解决并发和死锁问题 。

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

• REPEATABLE READ

  这是的默认隔离级别 InnoDB。 同一事务中的一致读取将读取第一次读取建立的 快照。这意味着，如果您SELECT 在同一事务中发出多个普通（非锁定）语句，则这些 SELECT语句彼此之间也是一致的。请参见 第“一致的非锁定读取”。

  对于锁定读取 （SELECT使用FOR UPDATE或LOCK IN SHARE MODE）， UPDATE和 DELETE语句，锁定取决于该语句使用的是具有唯一搜索条件的唯一索引还是范围类型搜索条件。

  • 对于具有唯一搜索条件的唯一索引， InnoDB仅锁定找到的索引记录，而不锁定其 前的间隙。
  • 对于其他搜索条件，InnoDB 使用间隙锁定或 下一键锁定 来锁定扫描的索引范围， 以阻止其他会话插入该范围所覆盖的间隙。有关间隙锁定和下一键锁定的信息，请参见 “ InnoDB锁定”。

• READ COMMITTED

  即使在同一事务中，每个一致的读取都将设置并读取其自己的新快照。有关一致读取的信息，请参见 “一致非锁定读取”。

  对于锁定读取（SELECT 使用FOR UPDATE或LOCK IN SHARE MODE），UPDATE 语句和DELETE 语句，InnoDB仅锁定索引记录，而不锁定它们之间的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。

  由于禁用了间隙锁定，因此可能会产生幻影问题，因为其他会话可以在间隙中插入新行。有关幻像的信息，请参见 “幻像行”。

  READ COMMITTED隔离级别 仅支持基于行的二进制日志记录 。如果READ COMMITTED与配合 使用binlog_format=MIXED，服务器将自动使用基于行的日志记录。

  使用READ COMMITTED具有其他效果：

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

  在这种情况下，表没有索引，因此搜索和索引扫描使用隐藏的聚集索引进行记录锁定（请参见“聚集索引和二级索引”），而不是使用索引列。

  假设一个会话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执行each时 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相反， 如果使用if ，则第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可以确定该行是否符合以下 WHERE条件 UPDATE：

  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 隔离级别的效果与启用不赞成使用的 innodb_locks_unsafe_for_binlog 配置选项相同，但以下情况除外：

  • 启用 innodb_locks_unsafe_for_binlog 是全局设置，会影响所有会话，而隔离级别可以针对所有会话全局设置，也可以针对每个会话单独设置。
  • innodb_locks_unsafe_for_binlog 只能在服务器启动时设置隔离级别，而隔离级别可以在启动时设置或在运行时更改。

  READ COMMITTED因此提供了比更好更灵活的控制 innodb_locks_unsafe_for_binlog。

• READ UNCOMMITTED

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

• SERIALIZABLE

  此级别类似于REPEATABLE READ，但是InnoDB将所有普通SELECT 语句隐式转换为SELECT ... LOCK IN SHARE MODEif autocommit禁用。如果 autocommit启用，则 SELECT是其自身的事务。因此，它被认为是只读的，并且如果以一致的（非锁定）读取方式执行并且不需要阻塞其他事务就可以序列化。（SELECT如果其他事务已修改所选行，则要强制平原 阻止，请禁用 autocommit。）

更多内容欢迎关注我的个人公众号“韩哥有话说”，100G人工智能学习资料，大量后端学习资料等你来拿。