Обсуждение: Postgresql Database Lock Problem

Поиск
Список
Период
Сортировка

Postgresql Database Lock Problem

От
shohorab hossain
Дата:
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

Re: [GENERAL] Postgresql Database Lock Problem

От
Scott Marlowe
Дата:
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.

Re: [GENERAL] Postgresql Database Lock Problem

От
shohorab hossain
Дата:
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/