Skip site navigation (1) Skip section navigation (2)

Postgresql Database Lock Problem

From: shohorab hossain <shohorab23(at)yahoo(dot)com>
To: pgsql-admin(at)postgresql(dot)org, pgsql-docs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Postgresql Database Lock Problem
Date: 2009-11-17 19:25:51
Message-ID: 866707.42028.qm@web51708.mail.re2.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-docspgsql-general
    
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 

Responses

pgsql-docs by date

Next:From: Plugge, Joe R.Date: 2009-11-17 19:31:49
Subject: Re: Postgresql Database Lock Problem
Previous:From: Erik RijkersDate: 2009-11-15 21:50:15
Subject: extract('dow', ...) mention

pgsql-admin by date

Next:From: Plugge, Joe R.Date: 2009-11-17 19:31:49
Subject: Re: Postgresql Database Lock Problem
Previous:From: Tena SakaiDate: 2009-11-17 17:38:03
Subject: Re: Failed to restore/recreate database (one more piece of info)

pgsql-general by date

Next:From: Plugge, Joe R.Date: 2009-11-17 19:31:49
Subject: Re: Postgresql Database Lock Problem
Previous:From: Tom LaneDate: 2009-11-17 19:19:47
Subject: Re: build array of composites in SPI

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group