什么决定了多表查询的锁定顺序？ [英] What determines the locking order for a multi-table query?
问题描述
SQL标准是否为多表查询指定锁定顺序？
Does the SQL standard specify the locking order for a multi-table query?
例如，给定：
从权限，终端机，部门中选择department.id WHERE department.id =吗？ AND terminal.id =？ ANDPermissions.parent = department.id ANDPermissions.child = terminals.id;
- 执行SQL标准保证锁定命令还是由（特定于实现的）执行计划确定？
- 有没有办法保证锁定命令？
- 如果没有办法保证锁定顺序，我们应该如何防止死锁？
UPDATE ：在不解释您的理由之前，请勿投票结束该问题。就我而言，这是一个编程问题，这使Stackoverflow成为话题。如果您认为这个问题需要进一步完善，请解释，我们非常乐意为您解答。
UPDATE: Please do not vote to close this issue without explaining your reasoning. As far as I'm concerned, this is a programming question, which makes it very much on-topic for Stackoverflow. If you believe the question needs to be further refined, please explain and I will be more than happy to answer you.
推荐答案
根据 https://stackoverflow.com/a/112256/14731 的锁定顺序由特定于实现的执行顺序确定。答案进一步说，没有确定的方法可以防止死锁。在命令式编程中，我们可以通过以相同的顺序获取锁来防止死锁，但是似乎在声明性系统中，当检测到死锁时，我们必须通过重试操作来解决死锁。
According to https://stackoverflow.com/a/112256/14731 lock order is determined by the implementation-specific execution order. The answer further goes on to say that there isn't a deterministic way to prevent deadlocks. Whereas in imperative programming we can prevent deadlocks by acquiring locks in the same order, it seems that in declarative systems we have to work around them by retrying the operation when a deadlock is detected.
此外，我认为，由于数据库执行计划在其生命周期内会发生变化，因此从技术上讲，不可能防止死锁。
Furthermore, I argue that since database execution plans change over their lifetime it is technically impossible to prevent deadlocks.
这篇关于什么决定了多表查询的锁定顺序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
