What determines the locking order for a query with multiple tables?

Question

Does the SQL standard indicate the locking order for a query with multiple tables?

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;

• Is the SQL standard a guarantee of the locking order or is it determined by its execution plan?
• Is there a way to guarantee the lock order?
• If there is no way to guarantee the lock order, how should we prevent deadlocks?

UPDATE . Please do not vote to close this problem without explaining your reasoning. As far as I know, this is a programming issue that makes it very relevant for Stackoverflow. If you think that the question needs further clarification, explain, and I will be more than happy to answer you.

Answer 1

According to https://stackoverflow.com/a/167354/ ... the order of blocking is determined by the order of execution specific to the implementation. It goes on to say that there is no deterministic way to prevent deadlocks. While in imperative programming we can prevent deadlocks by acquiring locks in the same order, it seems that in declarative systems we should bypass them by repeating the operation when a deadlock is detected.

In addition, I argue that because the database execution plans change over their lifetime, it is technically impossible to prevent deadlocks.

Answer 2

I can give you an answer for DB2, but I think it should be similar to other databases. First of all, it all depends on the locksize parameter of your tables. This parameter determines what is blocked. You can have locksize = table, page or row. Thus, depending on the locking of each table, the database locks the object (table, page or row) that is used to retrieve data for the cursor. Thus, the order of the created locks will be determined by access, which depends on the optimizer.