Re: Postgresql Database Lock Problem

Now I am using the following parameter to temporary resolve the locking problem

statment_timeout = 20s

With Thanks & Regards:
---------------------
Shohorab Hossain

----- Original Message ----
From: shohorab hossain <shohorab23(at)yahoo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Sent: Wednesday, November 18, 2009 14:41:09
Subject: Re: [GENERAL] Postgresql Database Lock Problem

At first I would like to thank you all for your quick response.

Now my question is why two processes ID 19181 and 19196 are trying to access the same transaction 18386574 at the same time.

Here I have got Process ID, Transaction ID and SQL statement from pg_locks and pg_stat_activity view.
Is it possible to find from which Application form/window the query is being running.

With Thanks & Regards:
---------------------
Shohorab Hossain

----- Original Message ----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: shohorab hossain <shohorab23(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Sent: Wednesday, November 18, 2009 1:47:53
Subject: Re: [GENERAL] Postgresql Database Lock Problem

[ cc's trimmed a bit ]

shohorab hossain <shohorab23(at)yahoo(dot)com> writes:
> LOG: process 19181 still waiting for ShareLock on transaction 18025221
> after 1002.251 ms
> STATEMENT: SELECT CurrentNext, CurrentNextSys, IncrementNo, Prefix, Suffix,
> DecimalPattern, AD_Sequence_ID FROM AD_Sequence WHERE Name = $1 AND
> AD_Client_ID = $2 AND IsActive='Y' AND IsTableID='N' AND
> IsAutoSequence='Y' ORDER BY AD_Client_ID DESC FOR UPDATE OF
> AD_Sequence

> LOG: process 19181 acquired ShareLock on transaction 18025221 after
> 1298870.572 ms
> STATEMENT: SELECT CurrentNext, CurrentNextSys, IncrementNo, Prefix, Suffix,
> DecimalPattern, AD_Sequence_ID FROM AD_Sequence WHERE Name = $1 AND
> AD_Client_ID = $2 AND IsActive='Y' AND IsTableID='N' AND
> IsAutoSequence='Y' ORDER BY AD_Client_ID DESC FOR UPDATE OF
> AD_Sequence

It looks to me like the database is doing exactly what you are telling
it to, ie, waiting until it can get an update lock on the selected
row(s) of AD_Sequence. What you need to look into is what the other
transaction was doing that caused it to sit on uncommitted changes to
those rows for 1300 seconds. Most likely this is an application bug or
bad application design (like failing to commit changes reasonably
promptly, or maybe not understanding the semantics of FOR UPDATE in
the first place).

regards, tom lane

Get your preferred Email name!
Now you can @ymail.com and @rocketmail.com.
http://mail.promotions.yahoo.com/newdomains/aa/

New Email addresses available on Yahoo!
Get the Email name you&#39;ve always wanted on the new @ymail and @rocketmail.
Hurry before someone else does!
http://mail.promotions.yahoo.com/newdomains/aa/