Обсуждение: ExclusiveLock without a relation in pg_locks
Would connections to a database require crating an extra ExclusiveLock? We have some connections to the database that happen to be “idle in transaction” and their pids have a granted “Exclusive Lock” in pg_locks. I cannot discern the tables where the ExclusiveLock is being held because the relation field is blank.
Moreover, there are other connections to the database coming from the same ip address as that of the connection with the ExclusiveLock. Some of the pids of these other connections seem to have different kinds of locks (AccessShareL0ck) so I am not quite sure why the pids with the ExclusiveLock’s are necessary.
How could I find out the tables that are being locked when I see an “ExclusiveLock” in pg_locks.
On Thu, Feb 23, 2006 at 08:54:36AM -0500, Carlos Oliva wrote: > Would connections to a database require crating an extra ExclusiveLock? We > have some connections to the database that happen to be "idle in > transaction" and their pids have a granted "Exclusive Lock" in pg_locks. I > cannot discern the tables where the ExclusiveLock is being held because the > relation field is blank. AIUI each backend has an exclusive lock on its own transaction. If you're idle in transaction you've acquired a lock on your transaction so other people can wait on you if necessary. That's why there's nothing in the relation field, because it's not a table lock. > Moreover, there are other connections to the database coming from the same > ip address as that of the connection with the ExclusiveLock. Some of the > pids of these other connections seem to have different kinds of locks > (AccessShareL0ck) so I am not quite sure why the pids with the > ExclusiveLock's are necessary. AccessShareLock is the normal lock you acquire when selecting data. It's doesn't really do much other than say "I'm using this table, don't delete it". See the documentation for all the details. > How could I find out the tables that are being locked when I see an > "ExclusiveLock" in pg_locks. It's rare to see exclusive locks on tables except for things like VACUUM FULL and CLUSTER and other such admin commands... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
On Thu, Feb 23, 2006 at 08:54:36AM -0500, Carlos Oliva wrote: > Would connections to a database require crating an extra ExclusiveLock? We > have some connections to the database that happen to be "idle in > transaction" and their pids have a granted "Exclusive Lock" in pg_locks. I > cannot discern the tables where the ExclusiveLock is being held because the > relation field is blank. http://www.postgresql.org/docs/8.1/interactive/view-pg-locks.html "Every transaction holds an exclusive lock on its transaction ID for its entire duration. If one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID. That will succeed only when the other transaction terminates and releases its locks." If the relation column is null then you're probably seeing these transaction ID locks. > How could I find out the tables that are being locked when I see an > "ExclusiveLock" in pg_locks. An easy way to convert a relation's oid to its name is to cast it to regclass: SELECT relation::regclass AS relname, * FROM pg_locks; -- Michael Fuhr
Thank you very much for your answer. I think that I am seeing those self transaction id locks as "ExclusiveLocks" Would you expect to see an "ExclusiveLock" with a query of type Select (not Select Update or Update or Insert)? -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michael Fuhr Sent: Thursday, February 23, 2006 10:05 AM To: Carlos Oliva Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ExclusiveLock without a relation in pg_locks On Thu, Feb 23, 2006 at 08:54:36AM -0500, Carlos Oliva wrote: > Would connections to a database require crating an extra ExclusiveLock? We > have some connections to the database that happen to be "idle in > transaction" and their pids have a granted "Exclusive Lock" in pg_locks. I > cannot discern the tables where the ExclusiveLock is being held because the > relation field is blank. http://www.postgresql.org/docs/8.1/interactive/view-pg-locks.html "Every transaction holds an exclusive lock on its transaction ID for its entire duration. If one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID. That will succeed only when the other transaction terminates and releases its locks." If the relation column is null then you're probably seeing these transaction ID locks. > How could I find out the tables that are being locked when I see an > "ExclusiveLock" in pg_locks. An easy way to convert a relation's oid to its name is to cast it to regclass: SELECT relation::regclass AS relname, * FROM pg_locks; -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
Thank you very much for your answer. I think that I am seeing those self transaction id locks as "ExclusiveLock" Would you expect to see an "ExclusiveLock" with a query of type Select (not Select Update or Update or Insert)? -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martijn van Oosterhout Sent: Thursday, February 23, 2006 10:04 AM To: Carlos Oliva Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ExclusiveLock without a relation in pg_locks On Thu, Feb 23, 2006 at 08:54:36AM -0500, Carlos Oliva wrote: > Would connections to a database require crating an extra ExclusiveLock? We > have some connections to the database that happen to be "idle in > transaction" and their pids have a granted "Exclusive Lock" in pg_locks. I > cannot discern the tables where the ExclusiveLock is being held because the > relation field is blank. AIUI each backend has an exclusive lock on its own transaction. If you're idle in transaction you've acquired a lock on your transaction so other people can wait on you if necessary. That's why there's nothing in the relation field, because it's not a table lock. > Moreover, there are other connections to the database coming from the same > ip address as that of the connection with the ExclusiveLock. Some of the > pids of these other connections seem to have different kinds of locks > (AccessShareL0ck) so I am not quite sure why the pids with the > ExclusiveLock's are necessary. AccessShareLock is the normal lock you acquire when selecting data. It's doesn't really do much other than say "I'm using this table, don't delete it". See the documentation for all the details. > How could I find out the tables that are being locked when I see an > "ExclusiveLock" in pg_locks. It's rare to see exclusive locks on tables except for things like VACUUM FULL and CLUSTER and other such admin commands... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
On Thu, Feb 23, 2006 at 11:08:07AM -0500, Carlos Oliva wrote: > Thank you very much for your answer. I think that I am seeing those self > transaction id locks as "ExclusiveLocks" > > Would you expect to see an "ExclusiveLock" with a query of type Select (not > Select Update or Update or Insert)? Not in general, unless perhaps the select called a function that acquired such a lock. The Concurrency Control chapter in the documentation has a section on lock types and the commands that acquire them: http://www.postgresql.org/docs/8.1/interactive/explicit-locking.html Are you just curious or are you seeing such a situation? -- Michael Fuhr
Yes. I am seeing that situation often in our database. The query field of pg_stat_activity is SELECT ..., not SLECT UPDATE or UPDATE or INSERT or DELETE. I was expecting the query to say something like SLECT UPDATE or something like that. Also the query seems to have just columns in the select statement; not functions. I will look further into these queries in case that they are using functions. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michael Fuhr Sent: Thursday, February 23, 2006 1:09 PM To: Carlos Oliva Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ExclusiveLock without a relation in pg_locks On Thu, Feb 23, 2006 at 11:08:07AM -0500, Carlos Oliva wrote: > Thank you very much for your answer. I think that I am seeing those self > transaction id locks as "ExclusiveLocks" > > Would you expect to see an "ExclusiveLock" with a query of type Select (not > Select Update or Update or Insert)? Not in general, unless perhaps the select called a function that acquired such a lock. The Concurrency Control chapter in the documentation has a section on lock types and the commands that acquire them: http://www.postgresql.org/docs/8.1/interactive/explicit-locking.html Are you just curious or are you seeing such a situation? -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
On Thu, Feb 23, 2006 at 01:23:36PM -0500, Carlos Oliva wrote: > Yes. I am seeing that situation often in our database. > > The query field of pg_stat_activity is SELECT ..., not SLECT UPDATE or > UPDATE or INSERT or DELETE. I was expecting the query to say something like > SLECT UPDATE or something like that. Also the query seems to have just > columns in the select statement; not functions. > > I will look further into these queries in case that they are using > functions. Are the ExclusiveLock locks for relations or for transaction IDs? Also, once a lock is acquired it's held until the transaction completes, so if the transaction ever acquired that lock then the transaction would still be holding it. If you can't figure out what's happening then it might be useful to see the output of SELECT relation::regclass, * FROM pg_locks; A self-contained test case might also be useful. If you show what commands you're running and what pg_locks output you don't understand, then somebody might be able to explain what's happening. -- Michael Fuhr
The ExclusiveLock seems to be granted on the transaction id instead of
tables. So I am guessing that, for a connection, the first lock is granted
to the transaction id and later other locks are granted on specific tables.
I am running the following from the console:
psql -d emrprod -c "select
pg_stat_activity.datname,pg_class.relname,pg_locks.
transaction, pg_locks.mode,
pg_locks.granted,pg_stat_activity.usename,substr(pg_
stat_activity.current_query,1,30) as "query", pg_stat_activity.query_start,
age(
now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid from
pg_s
tat_activity,pg_locks left outer join pg_class on (pg_locks.relation =
pg_class.
oid) where pg_locks.pid=pg_stat_activity.procpid order by
query_start;"|grep -v IDLE
Typical outputs are the following:
1) First example
emrprod | | 9507777 | ExclusiveLock | t | emruser |
SELECT PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 |
00:00:03.001737 |
6193
emrprod | mr0011 | | AccessShareLock | t | emruser |
SELECT PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 |
00:00:03.001737 |
6193
emrprod | sy0001a | | AccessShareLock | t | emruser |
SELEC
T PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 | 00:00:03.001737
|
6193
emrprod | mr0050 | | AccessShareLock | t | emruser |
SELEC
T PrtNbr, BilGurID, BilGu | 2006-02-23 14:02:28.369815-05 | 00:00:03.001737
|
2) Second Example
emrprod | | 9509136 | ExclusiveLock | t | emruser |
SELEC
T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588
|
9667
emrprod | sy0001 | | AccessShareLock | t | emruser |
SELEC
T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588
|
9667
emrprod | sy0001a | | AccessShareLock | t | emruser |
SELEC
T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588
|
9667
emrprod | sy0004 | | AccessShareLock | t | emruser |
SELEC
T SYTmpWks, SYTmpDir, SYT | 2006-02-23 14:04:49.498836-05 | 00:00:00.558588
|
9667
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michael Fuhr
Sent: Thursday, February 23, 2006 1:36 PM
To: Carlos Oliva
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] ExclusiveLock without a relation in pg_locks
On Thu, Feb 23, 2006 at 01:23:36PM -0500, Carlos Oliva wrote:
> Yes. I am seeing that situation often in our database.
>
> The query field of pg_stat_activity is SELECT ..., not SLECT UPDATE or
> UPDATE or INSERT or DELETE. I was expecting the query to say something
like
> SLECT UPDATE or something like that. Also the query seems to have just
> columns in the select statement; not functions.
>
> I will look further into these queries in case that they are using
> functions.
Are the ExclusiveLock locks for relations or for transaction IDs?
Also, once a lock is acquired it's held until the transaction
completes, so if the transaction ever acquired that lock then the
transaction would still be holding it.
If you can't figure out what's happening then it might be useful
to see the output of
SELECT relation::regclass, * FROM pg_locks;
A self-contained test case might also be useful. If you show what
commands you're running and what pg_locks output you don't understand,
then somebody might be able to explain what's happening.
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On Thu, Feb 23, 2006 at 02:10:22PM -0500, Carlos Oliva wrote: > The ExclusiveLock seems to be granted on the transaction id instead of > tables. So I am guessing that, for a connection, the first lock is granted > to the transaction id and later other locks are granted on specific tables. Right. Your output shows that the relation locks are AccessShareLock, which is what the documentation says an ordinary SELECT acquires. Other operations acquire stronger locks, depending on what they need to prevent other transactions from doing until this transaction completes. Most of the time you don't need to worry about any of this unless a transaction appears to be blocked (stuck); then you can query pg_locks and look for locks where the granted column is false and diagnose from there. -- Michael Fuhr