Thread
-
Meaning of transaction pg_locks?
Philip Warner <pjw@rhyme.com.au> — 2008-11-12T02:38:30Z
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?
Philip Warner <pjw@rhyme.com.au> — 2008-11-12T02:51:12Z
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?
Tom Lane <tgl@sss.pgh.pa.us> — 2008-11-12T02:53:03Z
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?
Philip Warner <pjw@rhyme.com.au> — 2008-11-12T03:08:45Z
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). -
Re: Meaning of transaction pg_locks?
Robert Haas <robertmhaas@gmail.com> — 2008-11-12T04:36:34Z
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 >