Search Postgresql Archives

locking question - why is this not a deadlock?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 

i am using PostgreSQL 7.4.1 (i am aware of my need to upgrade :)
i have a situation where i *believe* postgresql should be detecting a deadlock, but it is not. in the locks view you see below, all of the processes waiting on the 'numberfactory' table are blocked. (which smells like a deadlock to me). i killed pid 6829, and then i immediately saw the log entries that are at the bottom of this message regarding deadlocks amongst the remaining locks. pid 6829 was in that state of locks for close to an hour. 

* is this a bug?
* if so, is this a known bug? fixed in the latest 7.4 release or would i have to migrate to 8.0? 

statement used to view locks:
select ps.procpid, ps.query_start, ps.current_query, pc.relname, pl.mode, pl.granted from pg_locks pl left join pg_class pc on pl.relation = pc.oid, pg_stat_activity ps where pl.pid = ps.procpid order by ps.query_start;
(I apologize for the wrapping, its also at http://pace2020.com/ ~proyal/locks.html)
procpid | query_start | current_query | relname | mode | granted ---------+------------------------------- +------------------------------------------------------------- +------------------+------------------+--------- 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | numberfactory | AccessShareLock | t 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | numberfactory | RowExclusiveLock | t 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | csr | AccessShareLock | t 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | | ExclusiveLock | t 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | | ShareLock | f 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | salesperson | AccessShareLock | t 6829 | 2005-06-07 08:42:30.303431-04 | UPDATE numberfactory SET synjob1=78609 WHERE (synautoinc=1) | jobbillingsetup | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | <IDLE> in transaction | job | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | <IDLE> in transaction | jobtype | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | <IDLE> in transaction | jobbillingsetup | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | <IDLE> in transaction | jobstatus | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | <IDLE> in transaction | salesperson | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | <IDLE> in transaction | | ExclusiveLock | t 6856 | 2005-06-07 08:42:36.770765-04 | <IDLE> in transaction | numberfactory | AccessShareLock | t 6856 | 2005-06-07 08:42:36.770765-04 | <IDLE> in transaction | numberfactory | RowExclusiveLock | t 6856 | 2005-06-07 08:42:36.770765-04 | <IDLE> in transaction | csr | AccessShareLock | t 6826 | 2005-06-07 08:42:36.787609-04 | <IDLE> in transaction | salesperson | AccessShareLock | t 6826 | 2005-06-07 08:42:36.787609-04 | <IDLE> in transaction | shipvia | AccessShareLock | t 6826 | 2005-06-07 08:42:36.787609-04 | <IDLE> in transaction | pricelist | AccessShareLock | t 6826 | 2005-06-07 08:42:36.787609-04 | <IDLE> in transaction | | ExclusiveLock | t 7803 | 2005-06-07 08:45:12.838824-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | | ShareLock | f 7803 | 2005-06-07 08:45:12.838824-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | numberfactory | AccessShareLock | t 7803 | 2005-06-07 08:45:12.838824-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | numberfactory | RowExclusiveLock | t 7803 | 2005-06-07 08:45:12.838824-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | | ExclusiveLock | t 6857 | 2005-06-07 08:50:13.78227-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | | ExclusiveLock | t 6857 | 2005-06-07 08:50:13.78227-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | numberfactory | AccessShareLock | t 6857 | 2005-06-07 08:50:13.78227-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | numberfactory | RowExclusiveLock | t 6857 | 2005-06-07 08:50:13.78227-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | | ShareLock | f 5234 | 2005-06-07 08:51:45.638224-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | | ShareLock | f 5234 | 2005-06-07 08:51:45.638224-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | numberfactory | AccessShareLock | t 5234 | 2005-06-07 08:51:45.638224-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | numberfactory | RowExclusiveLock | t 5234 | 2005-06-07 08:51:45.638224-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | | ExclusiveLock | t 4157 | 2005-06-07 08:55:45.251806-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | | ExclusiveLock | t 4157 | 2005-06-07 08:55:45.251806-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | numberfactory | AccessShareLock | t 4157 | 2005-06-07 08:55:45.251806-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | numberfactory | RowExclusiveLock | t 4157 | 2005-06-07 08:55:45.251806-04 | UPDATE numberfactory SET synjob1=78610 WHERE (synautoinc=1) | | ShareLock | f 5774 | 2005-06-07 08:59:44.656757-04 | <IDLE> in transaction | | ExclusiveLock | t 5774 | 2005-06-07 08:59:44.656757-04 | <IDLE> in transaction | salesperson | AccessShareLock | t 10584 | 2005-06-07 09:34:29.612307-04 | <IDLE> | | ExclusiveLock | t 10584 | 2005-06-07 09:34:29.612307-04 | <IDLE> | pg_stat_activity | AccessShareLock | t 10584 | 2005-06-07 09:34:29.612307-04 | <IDLE> | pg_shadow | AccessShareLock | t 10584 | 2005-06-07 09:34:29.612307-04 | <IDLE> | pg_database | AccessShareLock | t 10584 | 2005-06-07 09:34:29.612307-04 | <IDLE> | pg_locks | AccessShareLock | t 10584 | 2005-06-07 09:34:29.612307-04 | <IDLE> | pg_class | AccessShareLock | t 
(44 rows)

deadlocks detected after i killed pid 6829:

Jun  7 09:39:09 epace postgres[6857]: [4-1] ERROR:  deadlock detected
Jun 7 09:39:09 epace postgres[6857]: [4-2] DETAIL: Process 6857 waits for ShareLock on transaction 121712126; blocked by process 5234. Jun 7 09:39:09 epace postgres[6857]: [4-3] ^IProcess 5234 waits for ShareLock on transaction 121712121; blocked by process 6857. 
Jun  7 09:39:10 epace postgres[6822]: [4-1] ERROR:  deadlock detected
Jun 7 09:39:10 epace postgres[6822]: [4-2] DETAIL: Process 6822 waits for ShareLock on transaction 121712345; blocked by process 11650. Jun 7 09:39:10 epace postgres[6822]: [4-3] ^IProcess 11650 waits for ShareLock on transaction 121712143; blocked by process 6822. 
Jun  7 09:39:11 epace postgres[4157]: [5-1] ERROR:  deadlock detected
Jun 7 09:39:11 epace postgres[4157]: [5-2] DETAIL: Process 4157 waits for ShareLock on transaction 121712346; blocked by process 6826. Jun 7 09:39:11 epace postgres[4157]: [5-3] ^IProcess 6826 waits for ShareLock on transaction 121712145; blocked by process 4157. 
Jun  7 09:39:12 epace postgres[9723]: [4-1] ERROR:  deadlock detected
Jun 7 09:39:12 epace postgres[9723]: [4-2] DETAIL: Process 9723 waits for ShareLock on transaction 121712376; blocked by process 11659. Jun 7 09:39:12 epace postgres[9723]: [4-3] ^IProcess 11659 waits for ShareLock on transaction 121712371; blocked by process 9723. 
Jun  7 09:39:13 epace postgres[6856]: [5-1] ERROR:  deadlock detected
Jun 7 09:39:13 epace postgres[6856]: [5-2] DETAIL: Process 6856 waits for ShareLock on transaction 121712353; blocked by process 5774. Jun 7 09:39:13 epace postgres[6856]: [5-3] ^IProcess 5774 waits for ShareLock on transaction 121712514; blocked by process 6856. 

thanks for the assistance.
-pete

--
peter royal -> proyal@xxxxxxxxxxxx

Attachment: smime.p7s
Description: S/MIME cryptographic signature

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux