Re: [ADMIN] Postgresql Database Lock Problem
От | Plugge, Joe R. |
---|---|
Тема | Re: [ADMIN] Postgresql Database Lock Problem |
Дата | |
Msg-id | BD69807DAE0CE44CA00A8338D0FDD0835375C8AD@oma00cexmbx03.corp.westworlds.com обсуждение исходный текст |
Ответ на | Postgresql Database Lock Problem (shohorab hossain <shohorab23@yahoo.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Следующее
От: "Naoko Reeves"Дата:
Сообщение: does encrypt function support higher than basic ascii?