sql - What determines the locking order for a multi-table query?

Question

Does the SQL standard specify the locking order for a multi-table query?

For example, given:

SELECT department.id FROM permissions, terminals, departments WHERE department.id = ? AND terminal.id = ? AND permissions.parent = department.id AND permissions.child = terminals.id;

1. Does the SQL standard guarantee a locking order or is it determined by the (implementation-specific) execution plan?
2. Is there a way to guarantee a locking order?
3. If there is no way to guarantee locking order, how are we supposed to prevent deadlocks?

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.

Answer 1

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.