Postgresql Database Lock Problem

Problem: Database Lock
----------------------------------

Dear
all
I
am working as a database administrator in a company. Our Database
system is Postgresql-8.3.5 and Application server is Jboss used for
our Adempiere ERP system. This is a web based ERP system. All
servers are running on RHEL.

Now
our system is going to on-line and users are entering old data. So
transactions are occurring very frequently.

Now
I am facing the problem is the application server just hangs at busy
hour and it does not accept any new connection. When I restart the
server (Adempiere, Jboss) it works fine for a few hours and problem
occurs again. When this problem occurs the database server shows the
following log

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

and
the lock table informations are as following:
adempiere=#
select * from pg_locks where granted = 'y' and mode =
'ExclusiveLock';
locktype |
database | relation | page | tuple | virtualxid | transactionid |
classid | objid | objsubid | virtualtransaction | pid | mode | granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------+---------
transactionid | | | | | | 18386552 |
| | | 39/1733 | 19196 | ExclusiveLock | t
virtualxid | | | | | 24/1586 | |
| | | 24/1586 | 19181 | ExclusiveLock | t
transactionid | | | | | | 18386856 |
| | | 24/1586 | 19181 | ExclusiveLock | t
virtualxid | | | | | 39/1733 | |
| | | 39/1733 | 19196 | ExclusiveLock | t
transactionid | | | | | | 18386574 |
| | | 39/1733 | 19196 | ExclusiveLock | t
transactionid | | | | | | 18386563 |
| | | 39/1733 | 19196 | ExclusiveLock | t
transactionid | | | | | | 18386869 |
| | | 24/1586 | 19181 | ExclusiveLock | t
virtualxid | | | | | 50/20 | |
| | | 50/20 | 19217 | ExclusiveLock | t
transactionid | | | | | | 18386846 |
| | | 24/1586 | 19181 | ExclusiveLock | t
tuple |
250427 | 251989 | 209 | 7 | | | | | | 24/1586 | 19181 | ExclusiveLock |
t
(10
rows)

adempiere=#
select * from pg_locks where granted = 'f';
locktype |
database | relation | page | tuple | virtualxid | transactionid |
classid | objid | objsubid | virtualtransaction | pid | mode |
granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------+---------
transactionid | | | | | | 18386574 |
| | | 24/1586 | 19181 | ShareLock | f
(1
row)

*** Here
you can see that process 19196 have ExclusiveLock on transaction
18386574 and process 19181 is waiting for ShareLock to the same
transaction.

When
I monitor the Application server sessions from Jboss console normally
I can see one of three stats R-Ready, K-Keep Alive and S-Service.
When the application server hangs all sessions goes to Service mode.

Please give me your appropriate and valuable solution in this regard. I am eagerly looking forward for your quick reply.
Thanks in advance: --------------------------- Shohorab Hossain

Send instant messages to your online friends http://uk.messenger.yahoo.com