为什么会发生死锁？

Question

我使用一个小事务，它包含两个简单的查询: select和update：

SELECT * FROM XYZ WHERE ABC = DEF

和

UPDATE XYZ SET ABC = 123
WHERE ABC = DEF

通常情况下，事务由两个线程启动，并根据隔离级别发生死锁(RepeatableRead、序列化)。两个事务尝试读取和更新完全相同的行。我想知道为什么会发生这种事。导致死锁的查询顺序是什么？我读过一些关于锁(共享的、独占的)和每个隔离级别的锁持续多长时间的文章，但我仍然不完全理解……

我甚至准备了一个简单的测试，它总是导致死锁。我已经在SSMS和SQL Server Profiler中查看了测试结果。我开始了第一个查询，然后立即开始第二个查询。

第一个查询：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT ...
WAITFOR DELAY '00:00:04'
UPDATE ...
COMMIT

第二个查询：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT ...
UPDATE ...
COMMIT

现在我不能向你展示详细的日志，但它看起来或多或少是这样的(我很可能在某个地方错过了Lock:deadlock等)：

(1) SQL:BatchStarting: First query
(2) SQL:BatchStarting: Second query
(3) Lock:timeout for second query
(4) Lock:timeout for first query
(5) Deadlock graph

如果我很好地理解锁，在(1)中，第一个查询获取一个共享锁(以执行SELECT)，然后进入睡眠状态，并保持共享锁直到事务结束。在(2)中，第二个查询也接受共享锁(SELECT)，但当同一行上有共享锁时，不能接受排他锁(UPDATE)，这会导致Lock:timeout。但是我不能解释为什么第二次查询会发生超时。可能我对整个过程不太了解。有谁能给出一个好的解释吗？

我没有注意到使用ReadCommitted的死锁，但我担心它们可能会发生。你有什么建议的解决方案？

Answer 1

当两个或多个任务因每个任务都锁定其他任务试图锁定的资源而永久阻止彼此时，就会发生死锁。

http://msdn.microsoft.com/en-us/library/ms177433.aspx

Answer 2

“但我无法解释为什么会发生第二次查询超时。”

因为第一个查询持有共享锁。然后，第一个查询中的更新也会尝试获取排它锁，这会让他进入睡眠状态。因此，第一个和第二个查询都处于休眠状态，等待对方醒来-这是一个导致超时的死锁:-)

在mysql中，它工作得更好-立即检测到死锁，并回滚其中一个事务(您不需要等待超时：-)。

此外，在mysql中，您可以执行以下操作来防止死锁

select ... for update

这将从事务的开始就放置一个写锁(即排它锁)，这样您就可以避免死锁的情况！也许你可以在你的数据库引擎中做一些类似的事情。

Answer 3

对于MSSQL，有一种防止死锁的机制。这里需要的是WITH NOLOCK提示。

在99.99%的SELECT语句中，它是可用的，不需要将SELECT与UPDATE捆绑在一起。也不需要将SELECT放入事务中。唯一的例外是不允许脏读。

将查询更改为此表单将解决您的所有问题：

SELECT ...
FROM yourtable WITH (NOLOCK)
WHERE ...

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
UPDATE ...
COMMIT