Thread: 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
Can you provide the layout of your table and all indexes that are present on said table?
Sounds like a incorrectly indexed table possibly.
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of shohorab hossain
Sent: Tuesday, November 17, 2009 1:26 PM
To: pgsql-admin@postgresql.org; pgsql-docs@postgresql.org; pgsql-general@postgresql.org
Subject: [ADMIN] 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
[ cc's trimmed a bit ]
shohorab hossain <shohorab23@yahoo.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
Next time this is happening join the pg_lock table to the pg_stat_activity table to see which query is holding the lock for a bazillion milliseconds, while it's happening. That query / statement may give you some clue what's wrong.
The statement that creates lock and waiting is in following. This is from pg_stats and pg_stat_activity view.
Here AD_Sequence is a table that maintains sequence number for all database objects. It automatically generates primary
keyvalue for all table insert. I think it also generates transaction related ID. For that reason it needs to update
nextsequence value for transaction ID.
Statment:
======================
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
UPDATEOF AD_Sequence
AD_Sequence Table Definition
=====================
adempiere=# \d ad_sequence
Table "adempiere.ad_sequence"
Column | Type | Modifiers
----------------+-----------------------------+------------------------------
ad_sequence_id | numeric(10,0) | not null
ad_client_id | numeric(10,0) | not null
ad_org_id | numeric(10,0) | not null
isactive | character(1) | default 'Y'::bpchar
created | timestamp without time zone | not null default now()
createdby | numeric(10,0) | not null
updated | timestamp without time zone | not null default now()
updatedby | numeric(10,0) | not null
name | character varying(60) | not null
description | character varying(255) |
vformat | character varying(40) |
isautosequence | character(1) | not null default 'Y'::bpchar
incrementno | numeric(10,0) | not null
startno | numeric(10,0) | not null
currentnext | numeric(10,0) | not null
currentnextsys | numeric(10,0) | not null
isaudited | character(1) | default 'N'::bpchar
istableid | character(1) | default 'N'::bpchar
prefix | character varying(255) |
suffix | character varying(255) |
startnewyear | character(1) | default 'N'::bpchar
datecolumn | character varying(60) |
decimalpattern | character varying(40) |
Indexes:
"ad_sequence_pkey" PRIMARY KEY, btree (ad_sequence_id)
"ad_sequence_name" UNIQUE, btree (ad_client_id, name)
Check constraints:
"ad_sequence_isactive_check" CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
"ad_sequence_isaudited_check" CHECK (isaudited = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
"ad_sequence_isautosequence_check" CHECK (isautosequence = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
"ad_sequence_istableid_check" CHECK (istableid = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
"ad_sequence_startnewyear_check" CHECK (startnewyear = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
Foreign-key constraints:
"sequenceclient" FOREIGN KEY (ad_client_id) REFERENCES ad_client(ad_client_id) DEFERRABLE INITIALLY DEFERRED
"sequenceorg" FOREIGN KEY (ad_org_id) REFERENCES ad_org(ad_org_id) DEFERRABLE INITIALLY DEFERRED
With Thanks & Regards:
---------------------
Shohorab Hossain
----- Original Message ----
From: Scott Marlowe <scott.marlowe@gmail.com>
To: shohorab hossain <shohorab23@yahoo.com>
Cc: pgsql-admin@postgresql.org; pgsql-docs@postgresql.org; pgsql-general@postgresql.org
Sent: Wednesday, November 18, 2009 2:55:25
Subject: Re: [GENERAL] Postgresql Database Lock Problem
Next time this is happening join the pg_lock table to the
pg_stat_activity table to see which query is holding the lock for a
bazillion milliseconds, while it's happening. That query / statement
may give you some clue what's wrong.
Get your preferred Email name!
Now you can @ymail.com and @rocketmail.com.
http://mail.promotions.yahoo.com/newdomains/aa/
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@sss.pgh.pa.us>
To: shohorab hossain <shohorab23@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, November 18, 2009 1:47:53
Subject: Re: [GENERAL] Postgresql Database Lock Problem
[ cc's trimmed a bit ]
shohorab hossain <shohorab23@yahoo.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/
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@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@postgresql.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@sss.pgh.pa.us>
To: shohorab hossain <shohorab23@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, November 18, 2009 1:47:53
Subject: Re: [GENERAL] Postgresql Database Lock Problem
[ cc's trimmed a bit ]
shohorab hossain <shohorab23@yahoo.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've always wanted on the new @ymail and @rocketmail.
Hurry before someone else does!
http://mail.promotions.yahoo.com/newdomains/aa/