Обсуждение: lock weirdness

I'm beginning to feel myself a bit dumber than usual, but could someone
help me with this query output? Am I stupid or we have a lock problem
here?

Got several locks of a kind that, as seen in postgres docs, "is not
automatically acquired by any PostgreSQL command" and I'm pretty sure
it wasn't set by the caller user also... We have thousands of users
and only two of them had this problem. Some queries being executed
were running for more than 12 hours, so I suspect of a lock timeout
problem or even a worse cenario...

Any help is thankful. Here goes the SQL command and part of the output.

TIA.

select pgl.*, pgsa.current_query from pg_stat_activity pgsa , pg_locks pgl
where pgl.pid = pgsa.procpid;

20906200 |    17142 |             | 28546 | AccessShareLock  | t       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp WHERE nr_
uniquenumber = '659157815'
          |          |   226610671 | 29535 | ExclusiveLock    | t       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp
WHERE nr_
uniquenumber = '659157815'
          |          |   224384857 | 15580 | ShareLock        | f       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp
WHERE nr_
uniquenumber = '659157815'
          |          |   224384864 | 29554 | ExclusiveLock    | t       |
DELETE FROM <table2> WHERE nr_uniquenumberuser1 = '5191648687'
AND nr_mo
bilenumberuser2 = '659157815'
          |          |   224384857 | 28549 | ShareLock        | f       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp
WHERE nr_
uniquenumber = '659157815'
    17257 |    17142 |             | 28548 | RowExclusiveLock | t       |
<IDLE> in transaction
    17257 |    17142 |             | 28548 | AccessShareLock  | t       |
<IDLE> in transaction
 20906200 |    17142 |             | 24836 | AccessShareLock  | t       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp WHERE nr_
uniquenumber = '659157815'
          |          |   224384857 | 15579 | ShareLock        | f       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp
WHERE nr_
uniquenumber = '5191648687'
 20906200 |    17142 |             | 29485 | AccessShareLock  | t       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp WHERE nr_
uniquenumber = '659157815'
    17239 |    17142 |             | 28544 | RowExclusiveLock | t       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp WHERE
nr_
uniquenumber = '5191648687'
    17239 |    17142 |             | 28544 | AccessShareLock  | t       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp WHERE
nr_
uniquenumber = '5191648687'
          |          |   225547434 | 15579 | ExclusiveLock    | t       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp
WHERE nr_
uniquenumber = '5191648687'
    17239 |    17142 |             | 29583 | RowExclusiveLock | t       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp WHERE
nr_
uniquenumber = '659157815'
    17239 |    17142 |             | 29583 | AccessShareLock  | t       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp WHERE
nr_
uniquenumber = '659157815'
    17239 |    17142 |             | 29584 | RowExclusiveLock | t       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp WHERE
nr_
uniquenumber = '659157815'
    17239 |    17142 |             | 29584 | AccessShareLock  | t       |
UPDATE <table> SET  dt_lastpositivemessage = current_timestamp WHERE
nr_
uniquenumber = '659157815'

"Marcus Andree S. Magalhaes" <marcus.magalhaes@vlinfo.com.br> writes:
> Got several locks of a kind that, as seen in postgres docs, "is not
> automatically acquired by any PostgreSQL command"

That part of the docs is speaking of locks on tables.  The locks that
are confusing you are not table locks, they are transaction-number
locks.  The guys who are blocked waiting for ShareLock on someone else's
transaction number are waiting for that transaction to complete,
evidently because they want to update some row it already updated.

In short, your real problem is that transaction 224384857 is being held
open instead of completing.

I'm not sure why the transaction holding that lock isn't showing up in
your output.  Possibly the join against pg_stat_activity is failing ---
there are a couple of possible reasons why pg_stat_activity might not
have a row for a backend.  Try looking at just pg_locks without the
join.

            regards, tom lane