Order of locks in query (postgresql)

Question

Database has table X and tables An, Bn, Cn, Dn that inherits from X.

Process 1 queries periodically data from X.

Process 2 updates data in child tables. For example, to update tables An and Bn it creates new tables Am and Bm, loads data into them, locks in access exclusive An, Bn, drops An and Bn and alters Am and Bm to inherit X.

The problem is that when process 1 execute query (for example select * from X) it locks tables An, Bn, Cn, Dn in shared mode, and order of locking is unknown. If process 1 locks Bn, then process 2 locks An we have deadlock.

Are there any info about order of locking tables in queries in postgresql (without explicit locking)? Or may be other solutions are possible?

Answer 1

I know you said without explicit locking, but honestly your best bet here is explicit locking. As the first statement in both batches have a lock command that locks the tables that you will use. The most important part about this is that both lock commands must lock the tables in the same order, otherwise you will run into deadlocks again anyways.

After this make sure both batches run as fast as possible since you're taking table level locks ... you don't want to hold them any longer than you must.

Answer 2

Are there any info about order of locking tables in queries in postgresql (without explicit locking)? Or may be other solutions are possible?

Normally postgresql' mvcc implementation would shield you against many types of deadlocks. See http://www.postgresql.org/files/developer/transactions.pdf for more details.

Though, one common solution is to just handle the deadlocks, that is, if your query fails due to a deadlock, try again.?