Thread

1. Dead locks

   Aristide Aragon <aristide@lionking.org> — 2001-05-06T23:19:06Z

   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?
   
   Cheers
   
   Aristide
   
   

2. Re: Dead locks

   Alfred Perlstein <bright@wintelcom.net> — 2001-05-07T00:48:19Z

   * 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
   
   

3. Re: Dead locks

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-07T01:06:44Z

   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
   
   

4. RE: Dead locks

   Christian Marschalek <cm@chello.at> — 2001-05-07T01:22:31Z

   > BTW, this scenario is also a good argument for running your 
   > application on a real OS, not Windoze ;-).
   
   No question 'bout that ;)