Обсуждение: Meaning of transaction pg_locks?
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. | / |/
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. | / |/
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
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).
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 >