Re: 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