InnoDB官方文档--14.7.4 幻影行|14.7.5 InnoDB的死锁

发表于 2019-09-02 更新于 2024-03-14 分类于技术

14.7.4 幻影行

所谓的幻读就是同一个事务在不同的时间执行相同的查询产生不同的结果行.例如,如果一个SELECT执行2次,但是第二次返回的行和第一次的不一样,这些行就被称为”幻影行”.

假设在child表的id列上有索引并且你想要对表中id值大于100的所有行进行加锁和读取,为了在之后更新选中列中的数据:

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

查询从id大于100的第一个记录开始扫描.假设表包含90和102.如果扫描范围之内的索引记录没有对插入的间隙加锁,另一个会话可以插入一个id为101的新行到表中.如果你在相同的事务中执行相同的SELECT,你将会在查询结果中看到一个id为101的新行(一个”幻影”).如果我们把这一系列的行视为一个数据项的话,新的幻影数据将违反事务的隔离准则即:在事务期间读取的数据不会发生改变.

为了避免幻读,InnoDB使用了一个叫做next-key锁的算法,它将索引行锁定与间隙锁定相结合.InnoDB以这样的方式执行行级锁定:当它搜索或扫描表索引时,它会在遇到的索引记录上设置共享锁或独占锁.因此,行级锁其实是索引记录锁.此外,索引记录上的next-key锁也会影响索引记录前的间隙.也就是说,next-key锁是索引记录锁加上索引记录锁前间隙锁.如果一个会话在索引记录R上有一个共享或者独占锁,其他会话就不能在索引记录R之前的间隙插入一个新的索引记录了.

当InnoDB扫描索引时,也会对最后一个索引记录后的间隙加锁.在前面的例子中就发生了这种情况:为了避免任何比100大的id插入到表中,InnoDB设置的锁也包括了id为102之后的间隙锁.

你可以使用next-key锁在你的应用中实现唯一性检查:如果你在共享模式下读取你的数据并且你要插入的行中没有重复键,那你就可以安全的插入行数据并且知道在你读取期间设置的next-key锁会组织任何人在同时插入和你重复键的行数据.因此,next-key锁能够使你锁定某些表中不存在的数据.

14.7.5 InnoDB的死锁

死锁是不同的事务因为互相持有其他事务需要的锁而不能继续处理的情况.因为每个事务都会等待需要的资源释放同时也不会释放已经获取的资源.

当事务锁定多个表中的行时(通过例如UPDATE或SELECT…FOR UPDATE语句),可能会发生死锁,但是顺序相反.当这类语句锁定索引记录和间隙时也会发生死锁,由于时间问题每个事务都获得了一些锁而没有其他的锁.

为了减少死锁发生的可能性,最好使用事务而不是LOCK TABLES语句;保持插入或更新数据的事务足够小,使其不会长时间保持连接状态;当不同的事务更新多个表或大量行的时候,对每个事务使用相同的操作顺序(例如SELECT…FOR UPDATE);对SELECT…FOR UPDATE和UPDATE…WHERE语句用到的行创建索引.产生死锁的概率不会受事务隔离级别的影响,因为隔离级别改变的是读取操作的行为,而死锁的发生是因为读操作.

当死锁检测启用(默认启用)并且确实发生死锁的时候,InnoDB会检测到这种情况并且回滚其中的一个事务.如果死锁检测使用 innodb_deadlock_detect配置选项禁用,InnoDB根据 innodb_lock_wait_timeout设置在死锁时回滚事务.因此,即使你的应用逻辑正确,你也必须处理事务要重试的情况.查看InnoDB用户事务的最后一个死锁,使用 SHOW ENGINE INNODB STATUS命令.如果在事务结构或应用程序错误处理中频繁发生死锁,使用 innodb_print_all_deadlocks参数运行MySQL来启用关于MySQL错误日志中所有和死锁相关的信息的打印.

14.7.5.1 一个InnoDB死锁的例子

下面的例子演示了当进行加锁请求的时候一个错误是如何造成死锁的.例子包括2个客户端,A和B.

首先,客户端A创建一个表包含一行数据然后开启一个事务.在事务中,A使用select的共享模式(share mode)对行数据加了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 LOCK IN SHARE MODE;
+------+
| 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也尝试从表中删除该行:

1
2
3

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锁的A也不能获取到X锁.结果是InnoDB为其中一个客户端产生了一个错误并且释放它的锁.这个客户端会返回下面的错误:

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

在这个时候,另一个客户端的加锁请求就可以被授予并且从表中删除行了.

14.7.5.2 死锁的发现和回滚

当死锁检测启用的时候(默认),InnoDB会自动检测事务的死锁并且回滚一个或者多个事务来打破死锁.InnoDB会尝试选择一个小的事务来进行回滚,事务的大小由插入,更新或删除的行的数量来决定.

InnoDB在innodb_table_locks = 1(默认值)和 autocommit = 0的时候知道表锁的存在,并且MySQL层面知道行级锁.换句话说,InnoDB在表被MySQL的LOCK TABLE加锁或者InnoDB以外的引擎设置锁的时候是不能检测死锁的.通过设置innodb_lock_wait_timeout系统变量来改善这种情况.

当InnoDB执行一个完整的事务回滚的时候,这个事务设置的所有锁都会被释放.然而,如果由于错误而回滚单个的SQL语句,则该语句设置的锁有可能会被保留.发生这种情况是因为InnoDB用这样一种格式来存储行级锁:它无法知道哪些锁是被哪些语句设置的.

如果SELECT语句在事务中调用存储的函数,并且函数中的语句失败了,这个语句会回滚.此外,如果在这之后执行了ROLLBACK那么整个事务会被回滚.

如果InnoDB监控器输出的 LATEST DETECTED DEADLOCK部分包括这样的信息 “TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION,”这表示等待列表中的事务已经达到了200.超过200个事务的等待列表会被视为死锁并且等待列表中的事务会尝试回滚.如果加锁线程必须查看等待列表中有超过1,000,000个锁的事务时也会产生相同的错误.

禁用死锁检测

在高并发系统上,当许多线程等待同一个锁时,死锁检测会导致速度减慢.有时,在发生死锁时,禁用死锁检测并依赖innodb_lock_wait_timeout设置进行事务回滚可能更有效.可以使用innodb_deadlock_detect配置选项禁用死锁检测.

14.7.5.3 如何最小化和处理死锁

这一节的内容以上一节死锁的知识为基础.介绍了如何组织数据库操作来最小化死锁和应用程序中所需要的错误处理.

死锁在支持事务的数据库中是一个经典问题,但是其不危险除非其频繁到你无法执行某些事务.通常你必须在应用程序中准备处理死锁回滚而重新提交事务.

InnoDB使用自动行级锁.即使是插入或者删除单行的事务也可能会遇到死锁.这是因为这些操作通常不够”原子”;它们会自动对被插入或删除的行的索引记录设置锁.

你可以使用以下技术处理死锁并降低其发生的可能性:

在任何时候,使用 SHOW ENGINE INNODB STATUS命令来确定最近的死锁发生的原因.这可以帮助你调整应用程序以避免死锁.
如果频繁的死锁警告引起了关注,通过启用innodb_print_all_deadlocks配置选项来收集更多的调试信息.每个死锁(不只是最后一个)的信息都记录在MySQL的错误日志里.在你完成debug之后禁用这个选项.
如果因为死锁(而导致回滚),总是准备好重新提交事务.死锁不危险.只是重新提交一遍而已.
保持事务短小并且执行时间短使其不易发生冲突.
在进行一组相关更改后立即提交事务,以使它们不易发生冲突.特别是,不要使用未提交的事务使交互式mysql会话长时间保持打开状态.
如果你使用加锁读(SELECT...FOR UPDATE或SELECT... LOCK IN),尝试使用更低的事务隔离级别例如READ COMMITED.
当在一个事务中修改多个表或者相同表中不同的行的时候,每次都用相同的顺序执行.事务就会形成良好的队列而不会死锁.例如,将数据库操作组织到应用程序中的函数中,或调用存储的函数,而不是在不同的位置编写多个类似的INSERT,UPDATE和DELETE语句.
在你的表中添加仔细选择的索引.然后,你的查询需要扫描更少的索引记录,从而设置更少的锁.使用EXPLAIN SELECT确定MySQL服务器认为哪些索引最适合您的查询.
使用更少的锁.如果你允许SELECT从旧快照返回数据,就不要在其中添加FOR UPDATE或LOCK IN SHARE MODE子句.在这里可以使用READ COMMITTED隔离级别,因为同一事务中的每个一致读取都从其自己的新快照读取.
如果没有其他帮助,请使用表级锁定序列化你的事务.将LOCK TABLES与事务表(如InnoDB表)一起使用的正确方法是使用SET autocommit = 0(不是START TRANSACTION)开始事务,然后LOCK TABLES,并且在您明确提交事务之前不要调用UNLOCK TABLES.
1
2
3
4
5
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
表级锁可防止对表的并发更新,从而避免死锁,但代价是对繁忙系统的响应性较低.
序列化事务的另一种方法是创建一个只包含一行的辅助“信号量”表.让每个事务在访问其他表之前更新该行.这样,所有事务都以串行方式发生.请注意,InnoDB即时死锁检测算法在这种情况下也适用,因为序列化锁是一个行级锁.使用MySQL表级锁定时,必须使用超时方法来解决死锁.