lock weirdness
От | Marcus Andree S. Magalhaes |
---|---|
Тема | lock weirdness |
Дата | |
Msg-id | 64696.200.174.148.100.1080476683.squirrel@webmail.webnow.com.br обсуждение исходный текст |
Ответы |
Re: lock weirdness
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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'
В списке pgsql-novice по дате отправления: