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@xxxxxxxxx>
To: shohorab hossain <shohorab23@xxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxx; pgsql-docs@xxxxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx
Sent: Wednesday, November 18, 2009 2:55:25
Subject: Re: 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/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
