Thread: Dead locks
Hello This question isn't about deadlocks, instead, it's about something I've been wondering because of a program I'm doing thatuses locks. My progrm uses begin and commit (or rollback) and locks a table in exclusive mode. What'd happen if the program unexpectedlydied? How would the database recover from a lock without a commit or rollback? Would the database release thelock automatically, would it be in deadlock or would I have to release it by hand, and if so how? Cheers Aristide
* Aristide Aragon <aristide@lionking.org> [010506 17:40] wrote: > Hello > This question isn't about deadlocks, instead, it's about something > I've been wondering because of a program I'm doing that uses locks. > 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? Would the database release the lock automatically, would > it be in deadlock or would I have to release it by hand, and if so > how? Please wrap lines at 70 characters. The database _should_ detect that the client has died because the database connection is usually a stream socket which notifies end points of disconnect/timeout. Once it detects that it _should_ be able to abort the current transaction and as part of that drop any locks held in that transaction. It _should_ but I'm not sure it does. -- -Alfred Perlstein - [alfred@freebsd.org] http://www.egr.unlv.edu/~slumos/on-netbsd.html
Aristide Aragon <aristide@lionking.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
> BTW, this scenario is also a good argument for running your > application on a real OS, not Windoze ;-). No question 'bout that ;)