SQL 死锁问题 [英] SQL Deadlock question

问题描述

在关系数据库中这两条语句是否有可能死锁?我正在尝试简化我的问题和示例——请假设这些选择，我认为通常只需要可共享的读锁定，现在需要排他的读锁:

Is it possible in relational databases for these two statements to deadlock? I'm trying to simplify my question and example -- please just assume that these selects, which I think would normally only require sharable read-locking, now require exclusive read locks:

Concurrent Connection 1:
SELECT {...}
FROM A 
JOIN B ON {...}

Concurrent Connection 2:
SELECT {...}
FROM B 
JOIN A ON {...}

也就是说，连接的顺序重要吗?SQL 中的单个语句是原子的吗?在第一个语句中是先锁定 A，然后锁定 B，然后在第二个语句中先锁定 B，然后再锁定 A?

That is, does the ordering of the joins matter? Are single statements in SQL atomic? Is A locked first and then B in the first statement and B locked first and then A in the second statement?

我认为不会 - 我的直觉告诉我，像这样的两个单独的语句不会死锁，无论多么复杂.我相信一个语句是作为一个整体进行分析的，并且需要锁定的资源使用某些确定性的全局顺序(即按字母顺序)锁定.但我需要的不仅仅是直觉 - 我想不出一种方法来证明它，也找不到它的记录.

I think not - My gut tells me that two single statements like this cannot deadlock, no matter how complex. I believe that a statement is analyzed as a whole and that the resources requiring locking are locked using some deterministic global order (i.e. alphabetically). But I need more than a gut feeling on this - I can't think of a way to prove it and I can't find it documented.

我对 MS SQL 2005 感兴趣，但我认为问题不是特定于实现的.

I'm interested in MS SQL 2005, but I don't think the question is implementation specific.

其次:因为它与 MS SQL 相关，我还想知道 Common Table Expressions 也有这个保证 - CTE 主要是一种语法优势(+递归)，由引擎合并到一个传统的单个语句中.

Secondarily: As it relates to MS SQL, I'd also want to know that Common Table Expressions also have this guarantee - that CTEs are mostly a syntactic benefit (+recursion), consolidated into a traditional single statement by the engine.

推荐答案

SELECT 不能与其他 SELECT 死锁，因为它们只获取共享锁.你说我们现在应该考虑这些 SELECT '需要独占读锁'，但是我们不可能考虑，因为 1) 没有 独占读锁 和 2) 读不要获取排他锁.

SELECTs cannot deadlock with other SELECT, because they only acquire shared locks. You say that we should consider that these SELECTs now 'require exclusive read locks', but this is not possible for us to consider because 1) there is no such thing as an exlusive read lock and 2) reads don't acquire exclusive locks.

但是您确实提出了一个更普遍的问题，简单语句是否会死锁.答案是肯定的、响亮的是.锁是在执行时获取的，而不是预先分析和排序，然后按某种顺序获取.引擎不可能预先知道所需的锁，因为它们依赖于磁盘上的实际数据，并且无法读取引擎需要的数据以...锁定数据.

But you do pose a more general question, whether simple statements can deadlock. The answer is a definite, resounding YES. Locks are acquired at execution, not analyzed upfront and sorted then acquired in some order. It would be impossible for the engine to know upfront the needed locks because they depend on the actual data in on-disk, and to read the data the engine needs to ... lock the data.

由于不同的索引访问顺序而导致的简单语句(SELECT 与 UPDATE 或 SELECT 与 DELETE)之间的死锁很常见，而且很容易调查、诊断和修复.但请注意，总是涉及写入操作，因为读取不能相互阻塞.对于此讨论，向 SELECT 添加 UPDLOCK 或 XLOCK 提示应视为写入.甚至不需要JOIN，二级索引很可能会引入导致死锁的访问顺序问题，参见读/写死锁.

Deadlocks between simple statements (SELECt vs. UPDATE or SELECT vs. DELETE) due to different index access order are quite common and very easy to investigate, diagnose and fix. But note that there is always a write operation involved, as reads cannot block each other. For this discussion, adding a UPDLOCK or XLOCK hint to a SELECT should be considered a write. You don't even need a JOIN, a secondary index may well introduce the access order problem leading to deadlock, see Read/Write Deadlock.

最后，写SELECT FROM A JOIN B 或写SELECT FROM B JOIN A 完全无关.查询优化器可以自由地重新排列它认为合适的访问顺序，查询的实际文本不会以任何方式强加执行顺序.

And finally, writing SELECT FROM A JOIN B or writing SELECT FROM B JOIN A is completely irrelevant. The query optimizer is free to rearrange the access order as it sees fit, the actual text of the query does not impose the order of execution in any way.

更新

那么我们如何构建一个通用的READ COMMITTED 策略多实体"数据库不会死锁吗?

How then can we construct a general strategy toward a READ COMMITTED "multiple entity" database that doesn't deadlock?

恐怕没有千篇一律的食谱.解决方案将取决于具体情况.最终，在数据库应用程序中，死锁已成为现实.我知道这听起来可能很荒谬，就像我们登上了月球，但我们无法编写正确的数据库应用程序"，但是有一些强大的因素在起作用，几乎可以保证应用程序最终会遇到死锁.幸运死锁是最容易处理错误的方法，只需再次读取状态、应用逻辑、重写新状态.话虽如此，有一些好的做法可以显着降低死锁的频率，直至它们几乎消失:

I'm afraid there is no cookie-cutter recipe. The solution will depend from case to case. Ultimately, in database applications deadlocks are a fact of life. I understand this may sound absurd, as in 'we landed on the Moon but we can't write a correct database application', but there are strong factors at play which pretty much guarantee that applications will eventually encounter deadlocks. Lucky deadlocks are the easiest to deal with errors, simple read again the state, apply the logic, re-write the new state. Now that being said, there are some good practices that can dramatically reduce the frequency of deadlocks, down to the point they are all but vanished:

• 尝试为写入设置一致的访问模式.有明确定义的规则，说明诸如交易应始终按以下顺序排列:Customers -> OrderHeaders -> OrderLines".请注意，必须在 事务 中遵守该命令.基本上，对架构中的所有 表进行排名，并指定所有更新必须按排名顺序发生.这最终归结为编写代码的个人贡献者的代码纪律，因为它必须确保它在事务中按照正确的顺序进行更新.
• 减少写入的持续时间.通常的智慧是这样的:在事务开始时进行所有读取(读取现有状态)，然后处理逻辑并计算新值，然后在事务结束时写入所有更新.避免像读->写->逻辑->读->写"这样的模式，而是做读->读->逻辑->写->写".当然，真正的技巧在于如何处理实际的、真实的、个别的情况，而显然必须必须在交易中进行写入.这里必须特别说明一种特定类型的事务:那些由队列驱动的事务，根据定义，它们通过从队列中出队(= 写入)来启动它们的活动.这些应用程序总是出了名的难以编写并且容易出错(特别是死锁)，幸运的是有办法做到这一点，请参阅 使用表作为队列.
• 减少读取量.表扫描是 最常见的死锁原因.正确的索引不仅可以消除死锁，还可以提高过程中的性能.
• 快照隔离.就避免僵局而言，这是最接近免费午餐的方法.我特意把它放在最后，因为它可能掩盖其他问题(比如不正确的索引)而不是修复它们.

• Try to have a consistent access pattern for Writes. Have clearly defined rules stating things such as 'a transaction shall always tables in this order: Customers -> OrderHeaders -> OrderLines.' Note that the order has to be obeyed inside a transaction. Basically, rank all tables in your schema and specify that all updates must occur in ranking order. This eventually boils down to code discipline of the individual contributor writing the code, as it has to ensure it writes is update sin the proper order inside a transaction.
• Reduce the duration of writes. The usual wisdom goes as this: at the beginning of the transaction do all the reads (read the existing state), then process the logic and compute new values, then write all updates at the end of transaction. Avoid a pattern like 'read->write->logic->read->write', instead do 'read->read->logic->write->write'. Of course, the true craftsmanship consist in how to deal with actual, real, individual cases when apparently one must have to do writes mid-transaction. A special note here must be said about a specific type of transaction: those driven by a queue, which by very definition start their activity by dequeueing (= a write) from the queue. These applications were always notoriously difficult to write and prone to errors (specially deadlocks), luckily there are ways to do it, see Using tables as Queues.
• Reduce the amount of reads. Table scans are the most prevalent cause of deadlocks. Proper indexing will not only eliminate the deadlocks, but may also boost performance in the process.
• Snapshot isolation. This is the closest thing you'll get to a free lunch in regard to avoiding deadlocks. I intentionally put it last, because it may mask other problems (like improper indexing) instead of fixing them.

试图用 LockCustomerByXXX 方法解决这个问题恐怕行不通.悲观锁定无法扩展.乐观并发更新是的方法如果你想有任何体面的表现.

Trying to solve this problem with a LockCustomerByXXX approach I'm afraid doesn't work. Pessimistic locking doesn't scale. Optimistic concurrency updates are the way to go if you want to have any sort of decent performance.

这篇关于SQL 死锁问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！