4

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?

valodzka
  • 5,535
  • 4
  • 39
  • 50
  • SELECT does not lock a table, unless you ask explicit for a lock. Did you check pg_locks to see what is going on? http://www.postgresql.org/docs/8.4/interactive/view-pg-locks.html – Frank Heikens Jun 28 '10 at 06:28
  • 1
    SELECT locks table (http://www.postgresql.org/docs/8.1/static/explicit-locking.html), and in my case it conficts with acces exclusive requested by drop. ACCESS SHARE Conflicts with the ACCESS EXCLUSIVE lock mode only. The commands SELECT and ANALYZE acquire a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode. – valodzka Jun 28 '10 at 09:21
  • It conflicts because of ACCESS EXCLUSIVE, that's your problem. The SELECT itself is not a problem. – Frank Heikens Jun 28 '10 at 11:08
  • DROP itself also isn't problem. It confilicts because of ACCESS SHARE of SELECT :) – valodzka Jun 29 '10 at 09:29

2 Answers2

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.

Donnie
  • 45,732
  • 10
  • 64
  • 86
  • 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
0

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.?

nos
  • 223,662
  • 58
  • 417
  • 506