Re: Dead locks

Aristide Aragon <aristide(at)lionking(dot)org> writes:
> My progrm uses begin and commit (or rollback) and locks a table in
> exclusive mode. What'd happen if the program unexpectedly died? How
> would the database recover from a lock without a commit or rollback?

When the backend detects loss of client connection, it will
automatically roll back any open transaction (thereby releasing the
lock) and exit.

So, as long as your program dies in a way that causes the connection to
be closed promptly, there's no problem. You could get in trouble if,
for example, your program goes into an infinite loop instead of dying
outright, or if your entire client machine goes down (power loss, OS
crash, etc) so that there's no TCP stack left to send the connection
close message. In the latter case it could take up to an hour or two
before the server's TCP stack times out and decides the connection
is gone.

Questions to ask yourself:

1. Can I make this transaction any shorter, so as to reduce the window
for trouble?

2. Do I *really* need an exclusive lock? Postgres offers a plethora of
lock types, and well-designed MVCC applications frequently do not need
any table-level locks at all.

BTW, this scenario is also a good argument for running your application
on a real OS, not Windoze ;-).

regards, tom lane