Thread: Locking question?
While debugging an application, I just wanted to confirm from the list here:
Suppose I have a long running transaction which has a few updates and inserts running on some specific tables which means it has acquired Exclusive locks too during the transaction on specific table but if just before commit the client app crashes and the commit is never sent, will the Exclusive locks be automatically released?
---------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
Suppose I have a long running transaction which has a few updates and inserts running on some specific tables which means it has acquired Exclusive locks too during the transaction on specific table but if just before commit the client app crashes and the commit is never sent, will the Exclusive locks be automatically released?
---------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
Shoaib Mir wrote: > While debugging an application, I just wanted to confirm from the list here: > > Suppose I have a long running transaction which has a few updates and > inserts running on some specific tables which means it has acquired > Exclusive locks too during the transaction on specific table but if just > before commit the client app crashes and the commit is never sent, will the > Exclusive locks be automatically released? Yes (assuming the backend dies in the process, which may not happen if the app dies silently and while not waiting for anything from the server). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Thank you Alvaro :)
-----------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )
-----------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )
On 1/30/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
Shoaib Mir wrote:
> While debugging an application, I just wanted to confirm from the list here:
>
> Suppose I have a long running transaction which has a few updates and
> inserts running on some specific tables which means it has acquired
> Exclusive locks too during the transaction on specific table but if just
> before commit the client app crashes and the commit is never sent, will the
> Exclusive locks be automatically released?
Yes (assuming the backend dies in the process, which may not happen if
the app dies silently and while not waiting for anything from the
server).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
On 1/30/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:
Do you mean that the Ex-lock will be held indefinitely in the following situation"
i) Appln. acquires Exclusive lock.
ii) Appln. sleeps or is interacting with human.
ii) Appln. crashes.
Doesn't the backend kill itself if it detects that the other side of the communincation channel has gone down?
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
Shoaib Mir wrote:
> While debugging an application, I just wanted to confirm from the list here:
>
> Suppose I have a long running transaction which has a few updates and
> inserts running on some specific tables which means it has acquired
> Exclusive locks too during the transaction on specific table but if just
> before commit the client app crashes and the commit is never sent, will the
> Exclusive locks be automatically released?
Yes (assuming the backend dies in the process, which may not happen if
the app dies silently and while not waiting for anything from the
server).
Do you mean that the Ex-lock will be held indefinitely in the following situation"
i) Appln. acquires Exclusive lock.
ii) Appln. sleeps or is interacting with human.
ii) Appln. crashes.
Doesn't the backend kill itself if it detects that the other side of the communincation channel has gone down?
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
There is such timeout from the database server for the idle connections but yes you can always use firewall settings in order to do that and kill idle connections.
--
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)
--
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)
On 2/6/07, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
On 1/30/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:Shoaib Mir wrote:
> While debugging an application, I just wanted to confirm from the list here:
>
> Suppose I have a long running transaction which has a few updates and
> inserts running on some specific tables which means it has acquired
> Exclusive locks too during the transaction on specific table but if just
> before commit the client app crashes and the commit is never sent, will the
> Exclusive locks be automatically released?
Yes (assuming the backend dies in the process, which may not happen if
the app dies silently and while not waiting for anything from the
server).
Do you mean that the Ex-lock will be held indefinitely in the following situation"
i) Appln. acquires Exclusive lock.
ii) Appln. sleeps or is interacting with human.
ii) Appln. crashes.
Doesn't the backend kill itself if it detects that the other side of the communincation channel has gone down?
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
Well... if the application crashes then normally the TCP connection would drop as well. The problem is that in many environments it can take a *long* time for the backend to realize that the client went away. The tcp_keepalives_* settings are intended to try and reduce that time to a more reasonable value, but bear in mind that they depend on the TCP stack provided by the OS/environment, so it's not guaranteed to work perfectly. On Feb 6, 2007, at 3:21 AM, Shoaib Mir wrote: > There is such timeout from the database server for the idle > connections but yes you can always use firewall settings in order > to do that and kill idle connections. > > -- > Shoaib Mir > EnterpriseDB ( www.enterprisedb.com) > > On 2/6/07, Gurjeet Singh <singh.gurjeet@gmail.com> wrote: On > 1/30/07, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Shoaib Mir wrote: > > While debugging an application, I just wanted to confirm from the > list here: > > > > Suppose I have a long running transaction which has a few updates > and > > inserts running on some specific tables which means it has acquired > > Exclusive locks too during the transaction on specific table but > if just > > before commit the client app crashes and the commit is never > sent, will the > > Exclusive locks be automatically released? > > Yes (assuming the backend dies in the process, which may not > happen if > the app dies silently and while not waiting for anything from the > server). > > > Do you mean that the Ex-lock will be held indefinitely in the > following situation" > > i) Appln. acquires Exclusive lock. > ii) Appln. sleeps or is interacting with human. > ii) Appln. crashes. > > Doesn't the backend kill itself if it detects that the other side > of the communincation channel has gone down? > > > -- > gurjeet[.singh]@EnterpriseDB.com > singh.gurjeet@{ gmail | hotmail | yahoo }.com > -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)