Postgres Pro
Downloads
Home   >   mailing lists

Thread: Meaning of transaction pg_locks?

Meaning of transaction pg_locks?

From
Philip Warner
Date:
Hi,

Can anyone explain the way to debug this kind of situation and/or
explain the meaning of these locks?

Partial output of "select * from pg_locks":
          |           |  1192675195 | 62860 | ShareLock           | f          |           |  1192675195 | 62814 |
ExclusiveLock      | t          |           |  1192675195 | 62838 | ShareLock           | f          |           |
1192675195| 63525 | ShareLock           | f
 

where 1192675195 is the 'transaction' field.

I am not at all clear what the processes are waiting for, or if there is
a way to reduce such contention.

-- 
----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 03 5330 3171          |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
http://www.rhyme.com.au <http://www.rhyme.com.au/>         
|                /           \|                                |    --________--
GPG key available upon request.  |  /                                |/

Re: Meaning of transaction pg_locks?

From
Philip Warner
Date:
Sorry, should RTFM more closely:
   "If a transaction is waiting for a row-level lock, it will usually
appear in the view as waiting for the transaction ID   of the current holder of that row lock."

so I need to look at the row locks on the blocker.


Philip Warner wrote:
> Hi,
>
> Can anyone explain the way to debug this kind of situation and/or
> explain the meaning of these locks?
>
> Partial output of "select * from pg_locks":
>
>            |           |  1192675195 | 62860 | ShareLock           | f
>            |           |  1192675195 | 62814 | ExclusiveLock       | t
>            |           |  1192675195 | 62838 | ShareLock           | f
>            |           |  1192675195 | 63525 | ShareLock           | f
>
> where 1192675195 is the 'transaction' field.
>
> I am not at all clear what the processes are waiting for, or if there is
> a way to reduce such contention.
>
>   


-- 
----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 03 5330 3171          |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
http://www.rhyme.com.au <http://www.rhyme.com.au/>         
|                /           \|                                |    --________--
GPG key available upon request.  |  /                                |/

Re: Meaning of transaction pg_locks?

From
Tom Lane
Date:
Philip Warner <pjw@rhyme.com.au> writes:
> Partial output of "select * from pg_locks":

>            |           |  1192675195 | 62860 | ShareLock           | f
>            |           |  1192675195 | 62814 | ExclusiveLock       | t
>            |           |  1192675195 | 62838 | ShareLock           | f
>            |           |  1192675195 | 63525 | ShareLock           | f

> where 1192675195 is the 'transaction' field.

> I am not at all clear what the processes are waiting for,

Neither are we, because you left out all the columns that might tell
that ...
        regards, tom lane

Re: Meaning of transaction pg_locks?

From
Philip Warner
Date:
Tom Lane wrote:
> Neither are we, because you left out all the columns that might tell
> that ...
>   

The columns are actually blank....it's the other rows I left out with
the row-level locks:
925282231 | 925280527 |             | 62814 | RowExclusiveLock    | t925282208 | 925280527 |             | 62814 |
RowExclusiveLock   | t          |           |  1192675195 | 62814 | ExclusiveLock       | t925282207 | 925280527 |
      | 62814 | RowExclusiveLock    | t925282025 | 925280527 |             | 62814 | AccessShareLock     | t925282025 |
925280527|             | 62814 | RowExclusiveLock    | t925282206 | 925280527 |             | 62814 | RowExclusiveLock
 | t925282212 | 925280527 |             | 62814 | RowExclusiveLock    | t925282210 | 925280527 |             | 62814 |
RowExclusiveLock   | t925282209 | 925280527 |             | 62814 | RowExclusiveLock    | t925281338 | 925280527 |
      | 62814 | AccessShareLock     | t925281338 | 925280527 |             | 62814 | RowExclusiveLock    | t925282211 |
925280527|             | 62814 | RowExclusiveLock    | t925282213 | 925280527 |             | 62814 | RowExclusiveLock
 | t
 

So I assume the processes waiting on the TX were waiting for one or more
of those rows.

Now I just need to figure out why the rows were locked for such a long
time (the row level locks are mostly on one table and various indexes of
that table).

Re: Meaning of transaction pg_locks?

From
"Robert Haas"
Date:
It might be helpful to look at pg_stat_activity.

...Robert

On Tue, Nov 11, 2008 at 10:08 PM, Philip Warner <pjw@rhyme.com.au> wrote:
> Tom Lane wrote:
>> Neither are we, because you left out all the columns that might tell
>> that ...
>>
>
> The columns are actually blank....it's the other rows I left out with
> the row-level locks:
>
>  925282231 | 925280527 |             | 62814 | RowExclusiveLock    | t
>  925282208 | 925280527 |             | 62814 | RowExclusiveLock    | t
>           |           |  1192675195 | 62814 | ExclusiveLock       | t
>  925282207 | 925280527 |             | 62814 | RowExclusiveLock    | t
>  925282025 | 925280527 |             | 62814 | AccessShareLock     | t
>  925282025 | 925280527 |             | 62814 | RowExclusiveLock    | t
>  925282206 | 925280527 |             | 62814 | RowExclusiveLock    | t
>  925282212 | 925280527 |             | 62814 | RowExclusiveLock    | t
>  925282210 | 925280527 |             | 62814 | RowExclusiveLock    | t
>  925282209 | 925280527 |             | 62814 | RowExclusiveLock    | t
>  925281338 | 925280527 |             | 62814 | AccessShareLock     | t
>  925281338 | 925280527 |             | 62814 | RowExclusiveLock    | t
>  925282211 | 925280527 |             | 62814 | RowExclusiveLock    | t
>  925282213 | 925280527 |             | 62814 | RowExclusiveLock    | t
>
> So I assume the processes waiting on the TX were waiting for one or more
> of those rows.
>
> Now I just need to figure out why the rows were locked for such a long
> time (the row level locks are mostly on one table and various indexes of
> that table).
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>