MySQL Deadlocks in InnoDB

Deadlocks in InnoDB
死锁是指不同的事务无法进行处理的情况，因为每个事务都持有对方需要的锁。因为这两个事务都在等待资源可用， 他们都没有释放过它所持有的锁。

当事务锁定多个表中的行（通过update或select … for update等语句），但顺序相反时会发生死锁。当这样的语句锁定索引条目和间隙范围时，每个事务由于时间问题获得一些锁定，而不获得其他锁定也会发生死锁。

为了减少死锁的可能性，请使用事务处理，而不是锁定表语句；保持插入或更新数据的事务处理足够小，以免长时间保持开放，当不同的事务更新多个表或大范围的行时，请在每个事务中使用相同的操作顺序（例如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设置将所有死锁的信息打印到mysqld错误日志。

一个InnoDB死锁示例
下面的例子将演示当一个锁请求将造成死锁时可能出现的错误。这个例子将调用两个客户端A和B。

首先客户端A创建一个表并插入一行记录，然后开始一个事务。在这个事务中通过请求共享模式锁的查询语句来对行获得共享锁：

mysql> create table t(i int) engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.02 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 |
±-----+
1 row in set (0.00 sec)

客户端B开始一个事务并试图从表中删除这行记录：

mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> delete from t where i=1;
删除操作请求一个X锁。因它它与客户端A所持有的S锁不兼容，因此请求将会在锁请求队列中进行大排队并且客户端B会被阻塞

最后，客户端A也试图删除这行记录：

mysql> delete from t where i=1;
客户端B出现错误信息：

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 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
在这时，客户端A可以被授予锁请求并从表中删除记录

mysql> delete from t where i=1;
Query OK, 1 row affected (0.00 sec)
死锁检测与回滚
当死锁检测被启用时（默认值），InnoDB会自动检测事务的死锁并回滚其中的一个事务或多个事务来打破死锁。InnoDB尝试选择小事务来进行回滚，而事务的大小是由插入，更新或删除的行记录数来判断的。

如果innodb_table_locks=1（默认）和autocommit=0，而且MySQL层知道行级锁，那么InnoDB就知道表锁。否则，InnoDB无法检测到由MySQL locktables语句设置的表锁或由InnoDB以外的存储引擎设置的锁。通过设置innodb_lock_wait_timeout系统的值来解决这些情况。

当InnoDB执行完一个事务的回滚，由该事务所设置的锁都会被释放。但是，如果由于错误只导致一个SQL语句被回滚，则该语句设置的一些锁可能会被保留。这是因为InnoDB以一种格式存储行锁，这样之后就不能知道哪个语句设置了哪个锁。

如果一个事务中的SELECT调用一个存储函数，并且函数中的语句执行失败，这个语句被回滚。之后，如果执行回滚，那么整个事务将会被回滚。

如果InnoDB监控输出的LATEST DETECTED DEADLOCK部分包含一条"TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACKFOLLOWING TRANSACTION"信息，这指示等待列表中的事务数据已经超过了200的限制。当等待列表超过200个事务时就会当作死锁并且试图检查等待列表的事务将被回滚。如果锁定线程必须查看超过1000000个锁是由等待列表中事务所持有也会出现同样的错误。

禁用死锁检测
在高并发系统中，当多线程等待同一个锁时，死锁检测可能会导致速度减慢。这时禁用死锁检测并依赖innodb_lock_wait_timeout在出现死锁时进行事务的回滚可能更有效。可以通过innodb_deadlock_detect配置选项来禁用死锁检测。

如何减少和处理死锁
下面介绍如何组织数据库操作来减少死锁以及处理死锁。

死锁是事务型数据库中一个非常经典的问题，但它们并不危险，除非频繁出现死锁且不能运行特定的事务。正常来说如果事务因为死锁被回滚那么你必须编写你的应用程序来准备重新执行事务。

InnoDB使用自动行级锁。那么可能在只有插入或删除单行记录时也会出现死锁。这是因为这些操作不是真正原子的，它们自动在被插入或删除的行记录所对应的索引记录在设锁。

您可以通过以下技术来处理死锁，并减少其发生的可能性：
.的任何时候，可以执行show engine innodb status命令来判断最近出现死锁的情况。这可以帮助你优化程序来避免死锁。

.如果经常出现死锁警告引起关注，请通过启用innodb_print_all_deadlocks配置选项来收集更广泛的调试信息。关于每个死锁的信息，而不仅仅是被记录在MySQL错误日志中最新的一个,调试后禁用此选项。

.如果事务由于死锁而失败，请随时准备重新执行它。死锁并不危险。再试一次。

.保持事务持续时间小和短，使它们不容易发生碰撞。

.在进行一组相关更改后立即提交事务，使它们不容易发生碰撞。特别是，不要让一个交互式mysql会话长期打开一个未已提交的事务。

.如果使用锁定读取(select … for update 或select … lock in share mode)，那么尝试使用低隔离级别比如read committed。

.当修改事务中的多个表或同一表中的不同行集时，每次都按照一致的顺序执行这些操作。然后事务形成定义良好的队列且不会死锁。例如，将数据库操作组织成应用程序中的函数，或调用存储例程，而不是在不同的地方编码多个插入、更新和删除语句。

.向表中添加选择良好的索引。然后，您的查询需要扫描更少的索引记录，从而设置更少的锁。使用explain select来判断MySQL服务器认为哪些索引最适合您的查询。

.使用更少的锁定。如果您可以允许select语句从旧快照返回数据，请不要向语句中添加for update或lock in share mode子句。使用read committed读提交的隔离级别是不错的选择， 因为同一事务中的每个一致性读取都是从它自己的新快照中读取的。

.如果没有其他帮助，请使用表级锁序列化你的事务。对事务表使用lock tables，如InnoDB表,使用set autocommit=0开始事务（而不是start transaction），接着使用lock tables，并且在显式提交事务之前不要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表级锁时，必须使用超时方法来解决死锁。