Re: transaction timeout

Dr NoName wrote:

>>What's the client doing that takes locks strong
>>enough to "lock up
>>the entire database"? Why does the client hang?
>>
>>
>
>yeah, good question. I thought postgres uses
>better-than-row-level locking? Could the total
>deadlock be caused by a combination of an open
>transaction and VACUUM FULL that runs every sunday?
>
>
>
Sure. Like this:

Client A accesses table T, and "hangs."
Client B attempts to get an ACCESS EXCLUSIVE lock on table T in
preparation for VACUUM FULL.
Client C connects to the database and waits for client B to get and
release his lock on table T.
Client D connects to the database and waits for client B to get and
release his lock on table T.
Client E connects to the database and waits for client B to get and
release his lock on table T.
etc...

until all your free connection slots are used up.

This happened to me once, except that client B was trying to rename
table T and create a new table T.

(You might think that clients C, D, and E should bypass client B (since
their access does not conflict with A's access.) However, if that was
allowed, then a VACUUM FULL on a busy table would wait forever because
client C would slip in before A finished, and client D before C
finished, etc., leading to a situation called "lock starvation." This
can really only be prevented by granting locks on a
first-come-first-serve basis.)

In your case, don't run VACUUM FULL via a cron job (i.e., when you're
not there). If you need to run it regularly, you're almost certainly
not reserving enough space in the free space map. VACUUM takes no locks
that conflict with selecting, inserting, updating, or deleting, so that
should be perfectly safe.

Regards,

Paul Tillotson