Skip site navigation (1) Skip section navigation (2)

lock weirdness

From: "Marcus Andree S(dot) Magalhaes" <marcus(dot)magalhaes(at)vlinfo(dot)com(dot)br>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: lock weirdness
Date: 2004-03-28 12:24:43
Message-ID: 64696.200.174.148.100.1080476683.squirrel@webmail.webnow.com.br (view raw or flat)
Thread:
Lists: pgsql-novice
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'



Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2004-03-28 15:44:31
Subject: Re: lock weirdness
Previous:From: Stephan SzaboDate: 2004-03-28 06:16:34
Subject: Re: Doubt on foreign key and deletions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group