详解MySQL中的死锁情况以及对死锁的处理方法

数据库  /  管理员 发布于 6年前   161

当多个事务同时持有和请求同一资源上的锁而产生循环依赖的时候就产生了死锁。死锁发生在事务试图以不同的顺序锁定资源。以StockPrice表上的两个事务为例：

事务1

START TRANSACTION;UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';COMMIT;

事务 #2

START TRANSACTION;UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';UPDATE StockPrice SET;COMMIT;

如果不走运的话，每个事务都可以执行完第一个语句，并在过程中锁住资源。然后每个事务都试图去执行第二行语句，当时却发现它被锁住了。两个事务将永远的等待对方完成，除非有其他原因打断死锁。

为了解决这个问题，数据库实现了各种死锁探查和超时机制。像InnoDB这样复杂的存储引擎会提示循环依赖并且立即返回错误。否则死锁将会导致查询非常缓慢。其他一些不好的做法是等待超时后放弃。当前InnoDB处理死锁的方式是回滚持有最少排他行级锁的事务。（几乎最简单的回滚的参考指标）

锁的行为是顺序是存储引擎决定的。因此，一些存储引擎可能会在特定的操作顺序下发生死锁，其他的可能没有。死锁有两种：一些是因为实际数据冲突而无法避免，一些是因为存储引擎的工作方式产生。

只有部分或者完全回滚其中的一个事务才可能打破死锁。死锁是事务系统中客观存在的事实，你的应该在设计上必须应该考虑处理死锁。一些业务系统可以从头重试事务。

如何处理死锁
死锁是事务型数据库典型的问题，但是除非它们频繁出现以至于你更本不能运行某个事务，它们一般是不危险的。正常地，你必须编写你的应用程序使得它们总是准备如果因为死锁而 回滚一个事务就重新发出一个事务。

InnoDB使用自动行级锁定。即使在只插入或删除单个行的事务的情况下，你可以遇到死锁。这是因为这些操作不是真正的“极小的”，它们自动对插入或删除的行的（可能是数个）索引记录设置锁定。

你可以用下列技术对付死锁减少它们发生的可能性：

用Use SHOW INNODB STATUS来确定最后一个死锁的原因。这样可以帮助你调节应用程序来避免死锁。

总是准备着重新发出事务，如果它因为死锁而失败了。死锁不危险，再试一次。

经常提交你的事务。小事务更少地倾向于冲突。

如果你正使用锁定读，（SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE），试着用更低的隔离级别，比如READ COMMITTED。

以固定的顺序访问你的表和行。则事务形成良好定义的查询并且没有死锁。

添加精心选定的索引到你的表。则你的查询需要扫描更少的索引记录并且因此设置更少的锁定。使用EXPLAIN SELECT来确定对于你的查询,MySQL认为哪个索引是最适当的。

使用更少的锁定。如果你可以接受允许一个SELECT从一个旧的快照返回数据，不要给它添加FOR UPDATE或LOCK IN SHARE MODE子句。这里使用READ COMMITTED隔离级别是比较好的，因为每个在同一事务里的持续读从它自己新鲜的快照里读取。

如果没有别的有帮助的了，用表级锁定系列化你的事务。用LOCK TABLES对事务型表（如InnoDB)的正确方法是设置AUTOCOMMIT = 0 并且不调用UNLOCK TABLES直到你明确地提交了事务。例如，如果你需要写表t1并从表t读，你可以按如下做：

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

表级锁定使得你的事务很好地排队，并且死锁被避免了。

领一个系列化事务的方法是创建一个辅助的“semaphore” 表，它只包含一个单行。让每个事务在访问其它表之前更新那个行。以这种方式，所有事务以序列的方式发生。注意，InnoDB即时死锁检测算法也能在这种情况下起租用，因为系列化锁定是行级锁定。超时方法，用MySQL表级锁定，必须被用来解决死锁。

在应用程序中使用LOCK TABLES命令，如果AUTOCOMMIT=1,MySQL不设定InnoDB表锁定。