Thread: 8.1.2 locking issues

We are having locking issues in 8.1.2 on HP 11.23 ia64.  I'm
trying to better understand how to conclusively identify who is
waiting on who and why.

We have a series of "select for updates" on our 'sessions' table.
One of those queries is stuck waiting for a "transactionid"
locktype ShareLock.  How can I tell what it's actually waiting
for?

TIA.
Ed

Ed L. wrote:
>
> We are having locking issues in 8.1.2 on HP 11.23 ia64.  I'm
> trying to better understand how to conclusively identify who is
> waiting on who and why.
>
> We have a series of "select for updates" on our 'sessions' table.
> One of those queries is stuck waiting for a "transactionid"
> locktype ShareLock.  How can I tell what it's actually waiting
> for?

There should be tuple locks on pg_locks for the transaction that holds
the transactionid that your transaction is waiting on.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Hi Ed,

In order to get which entities are responsible for the lock that you have try using the following query.

select loc.pid , cls.relname,loc.granted as lock_status from pg_locks  loc , pg_class cls where loc.locktype like '%sharelock%' and cls.oid=loc.relation and loc.pid in(pids of the process  from the error that are blocking each other);


Regards
Talha Khan

I have a few questions on pgsql locking terms and such...

I created the following view to make viewing the locks
a little easier:

-- CREATE OR REPLACE VIEW locksview AS
-- SELECT l.*, r.*, a.*, now() - a.query_start as query_age,
--        substring(replace(current_query, '\n', ' '), 1, 30) as sql30,
--        case when granted then 'granted' else 'WAITING' end as status,
--        case l.mode when 'AccessShareLock' then 1
--                    when 'RowShareLock' then 2
--                    when 'RowExclusiveLock' then 3
--                    when 'ShareUpdateExclusiveLock' then 4
--                    when 'ShareLock' then 5
--                    when 'ShareRowExclusiveLock' then 6
--                    when 'ExclusiveLock' then 7
--                    when 'AccessExclusiveLock' then 8
--                    else 100 end as exclusivity
-- FROM pg_locks l LEFT OUTER JOIN pg_class r ON r.oid = l.relation
--                 LEFT OUTER JOIN pg_stat_activity a ON l.pid = a.procpid;

I then started two transactions that try to update the
same row, and then ran the following query on the view
above:

SELECT now(), client_addr, pid, query_age, status, locktype, mode,
       relname, page, tuple as tup, current_query
FROM locksview
WHERE now() - query_start > '3 minute'::INTERVAL
ORDER BY query_age DESC, exclusivity DESC;

              now              | client_addr | pid  |    query_age    | status  |   locktype    |       mode       |
relname| page | tup |               current_query                 

-------------------------------+-------------+------+-----------------+---------+---------------+------------------+---------+------+-----+--------------------------------------------
 2006-11-09 20:37:25.680662-08 | 10.0.1.82   | 6484 | 00:01:25.830264 | granted | transactionid | ExclusiveLock    |
    |      |     | <IDLE> in transaction 
 2006-11-09 20:37:25.680662-08 | 10.0.1.82   | 6484 | 00:01:25.830264 | granted | relation      | RowExclusiveLock |
foo    |      |     | <IDLE> in transaction 
 2006-11-09 20:37:25.680662-08 | 10.0.1.82   | 6484 | 00:01:25.830264 | granted | relation      | AccessShareLock  |
foo    |      |     | <IDLE> in transaction 
 2006-11-09 20:37:25.680662-08 | 10.0.1.82   | 6508 | 00:01:18.862623 | granted | tuple         | ExclusiveLock    |
foo    |    0 |   7 | update foo set msg = 'maybe' where id = 3; 
 2006-11-09 20:37:25.680662-08 | 10.0.1.82   | 6508 | 00:01:18.862623 | granted | transactionid | ExclusiveLock    |
    |      |     | update foo set msg = 'maybe' where id = 3; 
 2006-11-09 20:37:25.680662-08 | 10.0.1.82   | 6508 | 00:01:18.862623 | WAITING | transactionid | ShareLock        |
    |      |     | update foo set msg = 'maybe' where id = 3; 
 2006-11-09 20:37:25.680662-08 | 10.0.1.82   | 6508 | 00:01:18.862623 | granted | relation      | RowExclusiveLock |
foo    |      |     | update foo set msg = 'maybe' where id = 3; 
 2006-11-09 20:37:25.680662-08 | 10.0.1.82   | 6508 | 00:01:18.862623 | granted | relation      | AccessShareLock  |
foo    |      |     | update foo set msg = 'maybe' where id = 3; 
(8 rows)


In this case, pid 6484 updated the row first but did not
commit, then 6508 tried to update the same row and naturally
blocked.  I understand at a very basic level why this
would block, so that's not the spirit of my questions.  I
also understand each transaction holds its acquired locks
until the end of its transaction.  But I'd like to better
understand the pg_locks view and pgsql locking terms.

First, I don't see rhyme or reason in the transactionid locks.
Can someone explain why 6508 has a transactionid ExclusiveLock,
but now is waiting on a transactionid ShareLock?  That seems
unintuitive.  It would seem that if you hold a more exclusive
lock, getting a less exclusive lock would not be a problem.  Is
there rhyme or reason for this?  From the docs, I'd guess the
ExclusiveLock is given to block "select for updates" that might
want to grab that row after 6508 already got in line, and that
it is grabbing the ShareLock for... what?

Second, 6508, the *blocked* process, also holds a tuple
ExclusiveLock on the page and tuple of foo on which it is
actually blocked.  Again, is there rhyme or reason I'm missing?

Third, what is it that causes values to show up in the page and
tuple columns?

Finally, both processes, the blocked and the blocking, hold
relation RowExclusiveLocks, which again, hardly seems intuitive.
It would seem that the blocked process would not have that lock
until it actually had access to update the row in question.
The docs do not say RowExclusiveLock conflicts with
RowExclusiveLock, but I guess I'm expecting to to that conflict
since one process is blocking on the other for updating the same
row.

TIA.
Ed

"Ed L." <pgsql@bluepolka.net> writes:
> Can someone explain why 6508 has a transactionid ExclusiveLock,
> but now is waiting on a transactionid ShareLock?  That seems
> unintuitive.  It would seem that if you hold a more exclusive
> lock, getting a less exclusive lock would not be a problem.

They're not on the same object.  Every transaction starts by taking
out an exclusive lock on its own XID.  (This will never block, because
at that instant there is no reason for anyone else to have any lock
on that XID.)  Subsequently, if there is a need for any transaction
to wait for the completion of some specific other transaction, it
implements this by trying to acquire share lock on that other
transaction's XID.  The reason for using share lock is that if several
transactions want to wait for the same other transaction, there is no
reason for them to block each other: once the other transaction commits,
we might as well release them all at the same time.  So this is a bit
of an abuse of the lock type scheme --- we use ExclusiveLock and
ShareLock here because they have the right blocking semantics, not
because there's any notion that locking someone else's XID is meaningful
in itself.

The larger point here is that all this occurs when someone wants
to update or lock a specific table row that some other
transaction-in-progress already updated or locked.  The simple and
logically clean way to handle that would be to take out lock manager
locks on each individual row modified by any transaction.  But that
sucks performance-wise, not least because a transaction that changes
any large number of rows would quickly exhaust the lock manager's
limited shared memory.  By transposing block-for-a-row-lock into
block-for-a-transaction-ID-lock, we can reduce the number of actively
locked objects to something that's practical.

And if you want every last gory detail, see the comments for
heap_lock_tuple():

 * NOTES: because the shared-memory lock table is of finite size, but users
 * could reasonably want to lock large numbers of tuples, we do not rely on
 * the standard lock manager to store tuple-level locks over the long term.
 * Instead, a tuple is marked as locked by setting the current transaction's
 * XID as its XMAX, and setting additional infomask bits to distinguish this
 * usage from the more normal case of having deleted the tuple.  When
 * multiple transactions concurrently share-lock a tuple, the first locker's
 * XID is replaced in XMAX with a MultiTransactionId representing the set of
 * XIDs currently holding share-locks.
 *
 * When it is necessary to wait for a tuple-level lock to be released, the
 * basic delay is provided by XactLockTableWait or MultiXactIdWait on the
 * contents of the tuple's XMAX.  However, that mechanism will release all
 * waiters concurrently, so there would be a race condition as to which
 * waiter gets the tuple, potentially leading to indefinite starvation of
 * some waiters.  The possibility of share-locking makes the problem much
 * worse --- a steady stream of share-lockers can easily block an exclusive
 * locker forever.  To provide more reliable semantics about who gets a
 * tuple-level lock first, we use the standard lock manager.  The protocol
 * for waiting for a tuple-level lock is really
 *        LockTuple()
 *        XactLockTableWait()
 *        mark tuple as locked by me
 *        UnlockTuple()
 * When there are multiple waiters, arbitration of who is to get the lock next
 * is provided by LockTuple().    However, at most one tuple-level lock will
 * be held or awaited per backend at any time, so we don't risk overflow
 * of the lock table.  Note that incoming share-lockers are required to
 * do LockTuple as well, if there is any conflict, to ensure that they don't
 * starve out waiting exclusive-lockers.  However, if there is not any active
 * conflict for a tuple, we don't incur any extra overhead.

            regards, tom lane