为什么会出现死锁？
我使用一个由两个简单查询组成的小事务：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。但我无法解释为什么第二个查询会发生超时。可能我不太了解整个过程。有人能给一个好的解释吗？
我没有注意到使用 ReadCommited 的死锁，但我担心它们可能会发生。 您推荐什么解决方案？
I use a small transaction which consists of two simple queries: select and update:
SELECT * FROM XYZ WHERE ABC = DEF
and
UPDATE XYZ SET ABC = 123
WHERE ABC = DEF
It is quite often situation when the transaction is started by two threads, and depending on Isolation Level deadlock occurs (RepeatableRead, Serialization). Both transactions try to read and update exactly the same row.
I'm wondering why it is happening. What is the order of queries which leads to deadlock? I've read a bit about lock (shared, exclusive) and how long locks last for each isolation level, but I still don't fully understand...
I've even prepared a simple test which always result in deadlock. I've looked at results of the test in SSMS and SQL Server Profiler. I started first query and then immediately the second.
First query:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT ...
WAITFOR DELAY '00:00:04'
UPDATE ...
COMMIT
Second query:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT ...
UPDATE ...
COMMIT
Now I'm not able to show you detailed logs, but it looks less or more like this (I've very likely missed Lock:deadlock etc. somewhere):
(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
If I understand locks well, in (1) first query takes a shared lock (to execute SELECT), then goes to sleep and keeps the shared lock until the end of transaction. In (2) second query also takes shared lock (SELECT) but cannot take exclusive lock (UPDATE) while there are shared locks on the same row, which results in Lock:timeout. But I can't explain why timeout for second query occurs. Probably I don't understand the whole process well. Can anybody give a good explanation?
I haven't noticed deadlocks using ReadCommitted but I'm afraid they may occur.
What solution do you recommend?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
当两个或多个任务由于每个任务都锁定了其他任务试图锁定的资源而永久相互阻止时，就会发生死锁
http://msdn.microsoft.com/en-us/library/ms177433.aspx
A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock
http://msdn.microsoft.com/en-us/library/ms177433.aspx
“但我无法解释为什么第二个查询会发生超时。”
因为第一个查询持有共享锁。然后第一个查询中的更新也尝试获取排他锁，这让他休眠了。因此，第一个和第二个查询都在睡眠，等待另一个查询醒来 - 这是一个死锁，导致超时:-)
在 mysql 中，它工作得更好 - 立即检测到死锁，并且其中一个事务被回滚（你无需等待超时:-))。
另外，在mysql中，您可以执行以下操作来防止死锁：
这将从事务开始时放置写锁（即排它锁），这样就可以避免死锁情况！也许您可以在数据库引擎中执行类似的操作。
"But I can't explain why timeout for second query occurs."
Because the first query holds shared lock. Then the update in the first query also tries to get the exclusive lock, which makes him sleep. So the first and second query are both sleeping waiting for the other to wake up - and this is a deadlock which results in timeout :-)
In mysql it works better - the deadlock is detected immediatelly and one of the transactions is rolled back (you need not to wait for timeout :-)).
Also, in mysql, you can do the following to prevent deadlock:
which will put a write-lock (i.e. exclusive lock) just from the beginning of the transaction, and this way you avoid the deadlock situation! Perhaps you can do something similar in your database engine.
对于MSSQL，有一种防止死锁的机制。这里您需要的是
WITH NOLOCK提示。在 99.99% 的 SELECT 语句的情况下它是可用的，并且不需要将 SELECT 与 UPDATE 捆绑在一起。也不需要将 SELECT 放入事务中。唯一的例外是不允许脏读。
将您的查询更改为此表单将解决您的所有问题：
For MSSQL there is a mechanism to prevent deadlocks. What you need here is called the
WITH NOLOCKhint.In 99.99% of the cases of
SELECTstatements it's usable and there is no need to bundle the SELECT with the UPDATE. There is also no need to put a SELECT into a transaction. The only exception is when dirty reads are not allowed.Changing your queries to this form would solve all your issues:
自从我上次处理这个问题以来已经很长时间了，但我相信 select 语句创建了一个读锁，它只能防止数据被更改——因此多个查询可以在同一个查询上持有并共享一个读锁数据。共享读锁是为了读一致性，也就是说，如果您在事务中多次读取同一行，那么读一致性应该意味着您应该始终得到相同的结果。
更新语句需要独占锁，因此更新语句必须等待读锁被释放。
两个事务都不会释放锁，因此事务失败。
不同的数据库实现有不同的策略来处理这个问题，Sybase和MS-SQL服务器使用带超时的锁升级（从读到写锁升级）——Oracle我相信（在某些时候）实现了读一致性虽然使用了回滚日志，但MySQL还有不同的策略。
It has been a long time since I last dealt with this, but I believe that the select statement creates a read-lock, which only prevents the data to be changed -- hence multiple queries can hold and share a read-lock on the same data. The shared-read-lock is for read consistency, that is if you multiple times in your transaction reads the same row, then read-consistency should mean that you should always get the same result.
The update statement requires an exclusive lock, and hence the update statement have to wait for the read-lock to be released.
None of the two transactions will release the locks, so the transactions fails.
Different databases implementations have different strategies for how to deal with this, with Sybase and MS-SQL-servers using lock escalation with timeout (escalate from read-to-write-lock) -- Oracle I believe (at some point) implemented read consistency though use of the roll-back-log, where MySQL have yet a different strategy.