Postgresql Database Lock Problem

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 key value for all table insert. I think it also generates transaction related ID. For that reason it needs to update next sequence 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 UPDATE OF 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&#39;ve always wanted on the new @ymail and @rocketmail.
Hurry before someone else does!
http://mail.promotions.yahoo.com/newdomains/aa/