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

事务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表锁定。