Innodb幻读、死锁产生和预防、事务调度器

linqiw
508 阅读13分钟

        这篇就聊下mysql的Innodb幻读的定义和死锁产生的例子和如何预防死锁，和事务死锁的事务调度器。死锁是一种不同的事务都无法继续进行的情况，因为每个事务都持有对方需要的锁。因为这两个事务都在等待资源变得可用，所以它们都不会释放它所持有的锁。当事务锁定多个表中的行（通过UPDATE 或 SELECT ... FOR UPDATE等语句）时，顺序相反，可能会发生死锁。当语句锁定索引记录和间隙的范围时，也会发生死锁，每个事务都会获取一些锁。要减少死锁的可能性，请使用事务而不是LOCK TABLES锁表语句；使插入或更新数据的事务足够小，以便它们不会长时间保持打开状态；当不同的事务更新多个表或大范围的行时，在每个事务中请使用相同的操作顺序（如SELECT ... FOR UPDATE)；在SELECT中使用的列上创建索引SELECT ... FOR UPDATE 和 UPDATE ... WHERE语句。死锁的可能性不受隔离级别的影响，因为隔离级别改变了读操作的行为，而死锁是由于写操作而产生的。当死锁检测被启用（默认）并且死锁确实发生时，InnoDB会检测到该情况并回滚其中一个事务。如果使用innodb_deadlock_detect配置选项禁用死锁检测，innodb将依赖innodb_lock_wait_timeout设置的超时时间在死锁情况下回滚事务。因此，即使应用程序逻辑正确，也必须处理重试事务的情况。要查看InnoDB用户事务中的最后一个死锁，使用SHOW ENGINE InnoDB STATUS命令。如果频繁死锁突出显示事务结构或应用程序错误处理的问题，请在启用innodb_print_all_deadlocks设置的情况下运行，以便将所有死锁的信息打印到mysqld错误日志中。死锁产生的四个条件： ①获取的资源具有互斥性，不能被同时获取到 ②请求与保持，发生堵塞时，对占有的资源不释放③循环等待，线程之间形成循环等待资源④不可剥夺，线程已获得的资源，在末使用完之前，不能被强行剥夺。

一、幻读

         事务中当同一个查询在不同时间生成不同的行集时，出现所谓的幻象问题。例如，如果SELECT执行两次，但第二次返回的行不是第一次返回的行，则该行是"幻影"行。

         假设child表的id列上有一个索引，并且您希望读取并锁定表中标识值大于100的所有行，以便稍后更新选定行中的某些列：

    • SELECT * FROM child WHERE id > 100 FOR UPDATE;

          查询从id大于100的第一条记录开始扫描索引。假设表包含id值为90和102的行。如果在扫描范围内的索引记录上设置的锁没有锁定间隙中的插入（在本例中，间隙在90和102之间），则另一个会话可以将id为101的新行插入表中。如果在同一个事务中执行同一个SELECT，在查询返回的结果集中看到一个id为101的新行（“幻影”）。如果我们将一组行视为一个数据项，那么新的幻像子项将违反事务的隔离原则， 事务运行，因此事务读取到期间未更改的数据在。

          为了防止出现幻象，InnoDB使用了一种称为next-key locking的算法，该算法将索引行锁定和间隙锁定结合起来。InnoDB执行行级锁定的方式是，当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁或独占锁。如果一个会话对索引中的记录R具有共享或独占锁，则另一个会话不能按索引顺序在R前面的间隙中插入新的索引记录。

          当InnoDB扫描索引时，它还可以锁定索引中最后一条记录之后的间隙。前面的例子就是这样：为了防止在id大于100的表中插入任何内容，InnoDB设置的锁在id值102后面的间隙上包含一个锁。

          您可以使用next key locking在应用程序中实现唯一性检查：如果您在共享模式下读取数据，并且没有看到要插入的行的重复项，那么您可以安全地插入行，并知道在读取期间，在行的后续行上设置的next-key lock防止任何人同时插入你的排。因此，next-key lock使您能够“锁定”表中不存在的内容。

          可以禁用间隙锁定。这可能会导致幻象问题，因为当禁用间隙锁定时，其他会话可以在间隙中插入新行，详细的可以看下我的另一篇juejin.cn/post/684490…                                        

二、死锁

  1. 死锁例子
  • 下面的示例演示了当锁请求将导致死锁时如何发生错误。这个例子涉及两个客户端A和B.
  • 首先，客户机A创建一个包含一行的表，然后开启一个事务。在事务中，A通过在共享模式下选择行来获取对该行的S锁：

    • mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

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

mysql> SELECT * FROM t WHERE i = 1 FOR SHARE;
+------+
| i    |
+------+
|    1 |
+------+
  • 接下来，客户端B开始一个事务并尝试从表中删除该行

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

mysql> DELETE FROM t WHERE i = 1;
  • 删除操作需要x锁。无法授予该锁，因为它与客户端A持有的S锁不兼容，因此请求将进入行的锁请求队列和客户端B阻塞。
  • 最后，客户端A也尝试从表中删除行：

    • mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

  • 此处发送死锁，因为客户端A需要x锁来删除行。但是，无法授予该锁请求，因为客户端B已经有一个x锁请求，并且正在等待客户A释放其s锁。也不能因为B请求x锁而将A持有的S锁升级为X锁。结果，InnoDB为其中一个客户机生成一个错误并释放其锁。客户端返回错误：

    ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

  • 此时，可以授予另一个客户机的锁请求，并从表中删除行。

   2.死锁的检测和回滚 

  • 当启用死锁检测（默认）时，InnoDB会自动检测事务死锁并回滚一个或多个事务以打破死锁。InnoDB尝试选择要回滚的小事务，其中事务的大小由插入、更新或删除的行数决定。
  • 如果innodb_table_locks=1（默认值）和autocommit=0，InnoDB就会知道表锁，并且上面的MySQL层知道行级锁。否则，如果涉及MySQL LOCK TABLES语句设置的表锁或非InnoDB的存储引擎设置的锁，InnoDB将无法检测到死锁。通过设置innodb_lock_wait_timeout系统变量的值来解决这些情况。 
  • 当InnoDB执行事务的完全回滚时，该事务设置的所有锁都会被释放。但是，如果一个错误只回滚一个SQL语句，那么该语句设置的一些锁可能会被保留。之所以会发生这种情况，是因为InnoDB以这样的行锁存储格式，这样它就无法在之后知道哪个锁是由哪个语句设置的。
  • 在事务中如果SELECT调用存储的函数，而函数中的语句失败，则该语句将回滚。此外，如果在此之后执行回滚，则整个事务将回滚。
  • 如果InnoDB Monitor输出的最新检测到的死锁部分包含一条消息：“TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION”，这表示等待列表中的事务数已达到200的限制。超过200个事务的等待列表将被视为死锁，尝试检查等待列表的事务将回滚。如果锁定线程必须查看等待列表中事务拥有的超过1000000个锁，则也可能发生相同的错误。
  • 有关数据库操作以避免死锁的技术，在下面会如何最小化和处理死锁 进行介绍。      
  •  在高并发性系统中，当多个线程等待同一个锁时，死锁检测可能会导致速度减慢，即RT变高。有时，禁用死锁检测和在发生死锁时依赖innodb_lock_wait_timeout设置进行事务回滚可能更有效。可以使用innodb_Deadlock_detect配置选项禁用死锁检测。

   3.如何最小化和处理死锁

  • 此部分以“死锁检测和回滚”中有关死锁的概念信息为基础。它解释了如何组织数据库操作以最小化死锁和应用程序中所需的后续错误处理。 
  • 死锁是事务性数据库中的一个典型问题，但除非死锁频繁到您根本无法运行某些事务，否则它们并不危险。通常，您必须编写应用程序，以便在事务由于死锁而回滚时，它们始终准备重新发出该事务。 
  • InnoDB使用自动行级锁定。即使是只插入或删除一行的事务，也会出现死锁。这是因为这些操作并不是真正的“原子”操作；它们会自动对插入或删除的行（可能是多个）索引记录设置锁。 
  • 可以使用以下技术处理死锁并降低死锁发生的可能性：
    • 任何时候，使用SHOW ENGINE INNODB STATUS命令，以确定最近死锁的原因。这可以帮助您优化应用程序以避免死锁。
    • 如果频繁的死锁警告引起关注，通过启用innodb_print_all_deadlocks配置选项来收集更广泛的调试信息。MySQL错误日志中记录了每个死锁的信息，而不仅仅是最新的死锁。完成调试后禁用此选项。
    • 如果事务由于死锁而失败，请随时准备重新发出该事务。死锁并不危险。再试一次。
    • 保持事务小并且持续时间短，以使它们不易发生冲突。即把事务最小化，无关的语句或代码不放在事务内。
    • 在进行一组相关更改后立即提交事务，以减少它们发生冲突的可能性。特别是，不要让未提交事务的交互式mysql会话长时间处于打开状态。
    • 如果使用锁定读取(SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE)，尝试使用较低的隔离级别，如READ COMMITTED。
    • 当在一个事务中修改多个表或同一个表中的不同行集时，请每次以一致的顺序执行这些操作。然后事务形成定义良好的队列，而不是死锁。例如，将数据库操作组织到应用程序中的函数中，或者调用存储的函数，而不是在不同的地方编写多个类似的INSERT、UPDATE和DELETE语句序列。     
    • 将索引添加到表中。然后查询需要扫描更少的索引记录，从而设置更少的锁。使用EXPLAIN SELECT确定MySQL服务器认为哪些索引最适合您的查询。
    • 少用锁。如果您能够允许SELECT从旧快照返回数据，请不要向其中添加UPDATE或SHARE子句。这里使用READ COMMITTED隔离级别是很好的，因为同一事务中的每个一致读取都从自己的新快照中读取。 
    • 如果没有其他帮助，请使用表级锁序列化事务。将锁表与事务表（如InnoDB TABLES）一起使用的正确方法是，以SET autocommit=0（不是START transaction）后跟锁表开始事务，并且在显式提交事务之前不要调用UNLOCK TABLES。例如，如果需要写入表t1并读取表t2，可以执行以下操作：

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

    • 表级锁防止对表的并发更新，以降低繁忙系统的响应速度为代价避免死锁。 
    • 序列化事务的另一种方法是创建一个只包含一行的辅助“信号量”表。在访问其他表之前，让每个事务更新该行。这样，所有的操作都以一种连续的方式发生。注意，InnoDB即时死锁检测算法在这种情况下也可以工作，因为序列化锁是行级锁。对于MySQL表级锁，必须使用timeout方法来解决死锁问题。  

三、事务调度器

  • InnoDB使用竞争感知事务调度（CATS）算法对等待锁的事务进行优先级排序。当多个事务正在等待同一对象上的锁时，CATS算法确定哪个事务首先获取锁。
  • CATS算法通过分配一个调度权重来确定等待事务的优先级，调度权重是根据事务阻塞的事务数计算的。例如，如果两个事务正在等待同一对象上的锁，则阻塞大多数事务的事务将被分配更大的调度权重。如果权重相等，则优先处理等待时间最长的事务。
    • 注意：在MySQL 8.0.20之前，InnoDB还使用先进先出（FIFO）算法来调度事务，而CATS算法只在严重的锁争用情况下使用。MySQL 8.0.20中的CATS算法增强使得FIFO算法变得多余，允许删除它。先前由FIFO算法执行的事务调度是从MySQL 8.0.20开始由CATS算法执行的。在某些情况下，此更改可能会影响事务被授予锁的顺序。    
  • 可以通过查询INFORMATION_SCHEMA.INNODB_TRX表信息中TRX_SCHEDULE_WEIGHT列来查看事务调度权重。等待事务是处于锁等待事务执行状态的事务，如TRX_STATE列所报告的。不等待锁的事务报告空TRX_SCHEDULE_WEIGHT值。
  • INNODB_METRICS计数器用于监视代码级事务调度事件。该表维护了一组计数器，用户可以通过这些计数器，来监控Innodb内部运行是否健康。有关使用INNODB_METRICS计数器的信息后面会有单独的一篇进行分析。
    • lock_rec_release_attempts 
      • 尝试释放记录锁的次数。一次尝试可能导致释放零个或多个记录锁，因为单个结构中可能有零个或多个记录锁。  
    •  lock_rec_grant_attempts 
      • 尝试授予记录锁的次数。一次尝试可能会导致授予零个或多个记录锁。  
    •  lock_schedule_refreshes
      • 分析等待图以更新计划事务权重的次数。  

























avatar
文章
阅读
粉丝