Order of locks in query (postgresql)?
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.
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.
I simplified a bit: process 1 isn't batch job, it is realy a lot of procesess which executes a lot of different queries. It will be very annoying to use explicit locking for every query. If postgresql order was predictable it would be lot simplier to change process 2 lock order.
– valodzka Jun 27 '10 at 21:07 This is why you shouldn't oversimplify your questions. Still, the options remain the same: either do explicit locking, or handle the deadlocks on failure (since postgres detects deadlocks and kills one of the procsses involved so the other can finish this is an option). – Donnie Jun 27 '10 at 21:11.
Normally postgresql' mvcc implementation would shield you against many types of deadlocks. See postgresql.org/files/developer/transacti... 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.?
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?
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.
Related Questions
Your 1997 Rav4 has power locks but the passenger side power lock won't lock when you use the power locks you have to lock it manually can anyone tell you how to fix it or what is wrong with it?
Postgresql full text search in postgresql - japanese, chinese, arabic?
How to build a sql query to return avg(price), min(price), max(price) joined with avg(order), min(order), max(order)?
Is it possible to issue a select query in mysql without taking any read locks?
Select query Locks the table, why does this happen?
How should I modify a SQL query for PostgreSQL?