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

Deadlock in PostgreSQL 7.3.4

From: Philipp Reisner <philipp(dot)reisner(at)linbit(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Deadlock in PostgreSQL 7.3.4
Date: 2003-08-18 11:04:58
Message-ID: 200308181304.58685.philipp.reisner@linbit.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi,

In the meantime we were able to upgrade to PostgreSQL 7.3.4+Tom's patch
to datetime.c [see 'Postgresql 7.3.3 crashing on query' thread].

Now if the applications issues one delete statement concurrently on 
two connections both block forever. 

Please correct me if I am wrong, but should not one of the statements
succeed and the other simply fail ?

Aug 18 10:34:25 nut1 postgres[4934]: [44389] LOG:  
                     query: delete from Calls where objID = 2268645

Aug 18 10:34:25 nut1 postgres[4933]: [44071] LOG:  
                     query: delete from Calls where objID = 2268645


Architecture: i386, OS: Debian

And here the full info:

sd=> SELECT l.mode, l.granted, l.pid, l.transaction, d.datname, r.relname FROM pg_locks l, pg_database d, pg_class r WHERE d.oid=l.database AND r.oid=l.relation;
       mode       | granted |  pid  | transaction | datname |           relname
------------------+---------+-------+-------------+---------+------------------------------
 AccessShareLock  | t       | 26836 |             | sd      | pg_locks
 AccessShareLock  | t       | 26836 |             | sd      | pg_class
 AccessShareLock  | t       | 22486 |             | sd      | callactivities
 AccessShareLock  | t       |  4935 |             | sd      | callactivities
 AccessShareLock  | t       | 22487 |             | sd      | callactivities
 AccessShareLock  | t       | 22486 |             | sd      | bppersons
 AccessShareLock  | t       | 22487 |             | sd      | bppersons
 AccessShareLock  | t       |  4935 |             | sd      | bppersons
 AccessShareLock  | t       |  4935 |             | sd      | priorities
 AccessShareLock  | t       | 22487 |             | sd      | priorities
 AccessShareLock  | t       | 22486 |             | sd      | priorities
 AccessShareLock  | t       | 22487 |             | sd      | callstates
 AccessShareLock  | t       |  4935 |             | sd      | callstates
 AccessShareLock  | t       | 22486 |             | sd      | callstates
 AccessShareLock  | t       |  4935 |             | sd      | queues
 AccessShareLock  | t       | 22487 |             | sd      | queues
 AccessShareLock  | t       | 22486 |             | sd      | queues
 AccessShareLock  | t       | 22486 |             | sd      | timezones
 AccessShareLock  | t       |  4935 |             | sd      | timezones
 AccessShareLock  | t       | 22487 |             | sd      | timezones
 AccessShareLock  | t       | 22486 |             | sd      | calls
 RowExclusiveLock | t       |  4934 |             | sd      | calls
 AccessShareLock  | t       |  4934 |             | sd      | calls
 AccessShareLock  | t       |  4935 |             | sd      | calls
 RowExclusiveLock | t       |  4933 |             | sd      | calls
 AccessShareLock  | t       |  4933 |             | sd      | calls
 AccessShareLock  | t       | 22487 |             | sd      | calls
 AccessShareLock  | t       | 22487 |             | sd      | bporganizations
 AccessShareLock  | t       | 22486 |             | sd      | bporganizations
 AccessShareLock  | t       |  4935 |             | sd      | bporganizations
 AccessShareLock  | t       | 22487 |             | sd      | problemtypes
 AccessShareLock  | t       | 22486 |             | sd      | problemtypes
 AccessShareLock  | t       |  4935 |             | sd      | problemtypes
 AccessShareLock  | t       |  4935 |             | sd      | contractelements
 AccessShareLock  | t       | 22486 |             | sd      | contractelements
 AccessShareLock  | t       | 22487 |             | sd      | contractelements
 AccessShareLock  | t       |  4935 |             | sd      | contracts
 AccessShareLock  | t       | 22487 |             | sd      | contracts
 AccessShareLock  | t       | 22486 |             | sd      | contracts
 AccessShareLock  | t       | 22487 |             | sd      | bpartners
 AccessShareLock  | t       |  4935 |             | sd      | bpartners
 AccessShareLock  | t       | 22486 |             | sd      | bpartners
 AccessShareLock  | t       | 22487 |             | sd      | callcalculations
 AccessShareLock  | t       | 22486 |             | sd      | callcalculations
 AccessShareLock  | t       |  4935 |             | sd      | callcalculations
 AccessShareLock  | t       | 22487 |             | sd      | chargingtypes
 AccessShareLock  | t       | 22486 |             | sd      | chargingtypes
 AccessShareLock  | t       |  4935 |             | sd      | chargingtypes
 AccessShareLock  | t       | 22487 |             | sd      | dictpropertysethasproperties
 AccessShareLock  | t       |  4935 |             | sd      | dictpropertysethasproperties
 AccessShareLock  | t       | 22486 |             | sd      | dictpropertysethasproperties
 AccessShareLock  | t       | 22487 |             | sd      | concretepropertysets
 AccessShareLock  | t       |  4935 |             | sd      | concretepropertysets
 AccessShareLock  | t       | 22486 |             | sd      | concretepropertysets
 AccessShareLock  | t       | 22486 |             | sd      | concretepropertyvalues
 AccessShareLock  | t       |  4935 |             | sd      | concretepropertyvalues
 AccessShareLock  | t       | 22487 |             | sd      | concretepropertyvalues
 AccessShareLock  | t       |  4935 |             | sd      | dictproperties
 AccessShareLock  | t       | 22486 |             | sd      | dictproperties
 AccessShareLock  | t       | 22487 |             | sd      | dictproperties
 AccessShareLock  | t       |  4934 |             | sd      | calls_pkey
(61 rows)

 3936 ?        S      0:00 postgres: stats buffer process
 3937 ?        S      0:00 postgres: stats collector process
 4555 ?        S     69:38 postgres: sd sd 10.2.2.6 idle
 4556 ?        S      0:07 postgres: sd sd 10.2.2.6 idle
 4621 ?        S     11:19 postgres: sd sd 10.2.2.6 idle
 4632 ?        S     10:08 postgres: sd sd 10.2.2.6 idle
 4764 ?        S      0:04 postgres: sd sd 10.2.1.5 idle
 4933 ?        S      5:20 postgres: sd sd 10.2.1.5 idle in transaction
 4934 ?        S      5:08 postgres: sd sd 10.2.1.5 DELETE waiting
 4935 ?        S      6:00 postgres: sd sd 10.2.1.5 idle in transaction
 4936 ?        S      4:54 postgres: sd sd 10.2.1.5 idle
 4937 ?        S      5:30 postgres: sd sd 10.2.1.5 idle
 4938 ?        S      6:17 postgres: sd sd 10.2.1.5 idle
 4939 ?        S      6:23 postgres: sd sd 10.2.1.5 idle
22486 ?        S      1:40 postgres: sd sd 10.2.1.5 idle in transaction
22487 ?        S      1:54 postgres: sd sd 10.2.1.5 idle in transaction
24211 pts/2    S      0:00 /usr/lib/postgresql/bin/psql -d sd
24218 ?        S      0:00 postgres: sd sd [local] idle
26753 ?        S      0:00 postgres: sd sd 10.2.1.5 idle

Aug 18 10:34:25 nut1 postgres[4934]: [44383-1] LOG:  query: select  objID, ID, CustCallID, SPCallID, cel_objid, con_objid, ccu_objid, csp_objid, csf_objid, fro_objid,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-2]  to1_objid, to2_objid, CallerSLA, CallOpenTime, CallOpenTimeUTC, CallResponseTime, CallResponseTimeUTC, CallCloseTime,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-3]  CallCloseTimeUTC, CallReceiveTime, CallReceiveTimeUTC, CallSendTime, CallSendTimeUTC, ProblemStartTime, ProblemStartTimeUTC,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-4]  EndOfResponseTime, EndOfResponseTimeUTC, RemainingResponseMinutes, EndOfRecoveryTime, EndOfRecoveryTimeUTC,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-5]  RemainingRecoveryMinutes, CallStateChangeTime, CallStateChangeTimeUTC, CallAcknowledgeTime, CallAcknowledgeTimeUTC,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-6]  CallerShortName, CallerFirstName, CallerLastName, CallerTitle, CallerSalutation, CallerTel, CallerTel2, CallerMobileTel,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-7]  CallerFax, CallerLocationZip, CallerLocationCity, CallerLocationStreet, CallerLocationCountry, CallerLocation, CallerEMail,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-8]  CallerPIN, CallerDescription, CallerDepartment, CallerSign, MainCompManfacturer, MainCompType, MainCompModel, MainComponent,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-9]  MainCompName, MainCompSerNr, MainCompInvNr, MainCompSerNrProv, MainCompOpSys, MainCompLocationZip, MainCompLocationCity,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-10]  MainCompLocationStreet, MainCompLocationCountry, MainCompLocation, MainCompLocationTel, MainCompDescription,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-11]  MainCompLocationName, pro_objid, pcu_objid, psp_objid, psf_objid, pgl_objid, sev_objid, fat_objid, cst_objid, pst_objid,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-12]  cac_objid, ema_objid, InternalState, CallProvedFlag, ShortDescription, Description, Remarks, Diagnosis, edi_objid, EditTime,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-13]  EditTimeUTC, csc_objid, css_objid, csu_objid, cal_objid, SubCompManfacturer, SubCompType, SubCompModel, SubComponent,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-14]  SubCompName, SubCompSerNr, SubCompInvNr, SubCompSerNrProv, SubCompOpSys, SubCompLocationZip, SubCompLocationCity,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-15]  SubCompLocationStreet, SubCompLocationCountry, SubCompLocation, SubCompLocationTel, SubCompDescription, SubCompLocationName,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-16]  SysSpecField1, SysSpecField2, SysSpecField3, SysSpecField4, SysSpecField5, SysSpecField6, SysSpecField7, SysSpecField8,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-17]  SysSpecField9, SysSpecField10, XML_Info, CHDShortName, CHDFirstName, CHDLastName, CHDTitle, CHDSalutation, CHDTel, CHDTel2,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-18]  CHDMobileTel, CHDFax, CHDLocationZip, CHDLocationCity, CHDLocationStreet, CHDLocationCountry, CHDLocation, CHDEMail, CHDPIN,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-19]  CHDDescription, CHDDepartment, CHDSign, CCPShortName, CCPFirstName, CCPLastName, CCPTitle, CCPSalutation, CCPTel, CCPTel2,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-20]  CCPMobileTel, CCPFax, CCPLocationZip, CCPLocationCity, CCPLocationStreet, CCPLocationCountry, CCPLocation, CCPEMail, CCPPIN,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-21]  CCPDescription, CCPDepartment, CCPSign, bpr_objid, SlaResponseMinutes, SlaRecoveryMinutes, TotalResponseMinutes,
Aug 18 10:34:25 nut1 postgres[4934]: [44383-22]  TotalRecoveryMinutes, TotalAcknowledgeMinutes, Zone, unspsc, dev_objid, tzo_objid, isTestCall, que_objid, tec_objid from Calls
Aug 18 10:34:25 nut1 postgres[4934]: [44383-23]  where ID = 2266388
Aug 18 10:34:25 nut1 postgres[4934]: [44384] LOG:  duration: 0.007914 sec
Aug 18 10:34:25 nut1 postgres[4934]: [44385] LOG:  query: select  objID, ID, ShortName, Name, TZID, UTCRawOffset from TimeZones where objID = 1000040000
Aug 18 10:34:25 nut1 postgres[4934]: [44386] LOG:  duration: 0.001898 sec
Aug 18 10:34:25 nut1 postgres[4934]: [44387] LOG:  query: begin;
Aug 18 10:34:25 nut1 postgres[4934]: [44388] LOG:  duration: 0.000117 sec
Aug 18 10:34:25 nut1 postgres[4934]: [44389] LOG:  query: delete from Calls where objID = 2268645
Aug 18 10:39:18 nut1 postgres[4934]: [44390] LOG:  duration: 292.649273 sec
Aug 18 10:39:18 nut1 postgres[4934]: [44391] LOG:  pq_recvbuf: unexpected EOF on client connection


Aug 18 10:34:25 nut1 postgres[4933]: [44067-1] LOG:  query: select  objID, ID, CustCallID, SPCallID, cel_objid, con_objid, ccu_objid, csp_objid, csf_objid, fro_objid,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-2]  to1_objid, to2_objid, CallerSLA, CallOpenTime, CallOpenTimeUTC, CallResponseTime, CallResponseTimeUTC, CallCloseTime,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-3]  CallCloseTimeUTC, CallReceiveTime, CallReceiveTimeUTC, CallSendTime, CallSendTimeUTC, ProblemStartTime, ProblemStartTimeUTC,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-4]  EndOfResponseTime, EndOfResponseTimeUTC, RemainingResponseMinutes, EndOfRecoveryTime, EndOfRecoveryTimeUTC,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-5]  RemainingRecoveryMinutes, CallStateChangeTime, CallStateChangeTimeUTC, CallAcknowledgeTime, CallAcknowledgeTimeUTC,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-6]  CallerShortName, CallerFirstName, CallerLastName, CallerTitle, CallerSalutation, CallerTel, CallerTel2, CallerMobileTel,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-7]  CallerFax, CallerLocationZip, CallerLocationCity, CallerLocationStreet, CallerLocationCountry, CallerLocation, CallerEMail,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-8]  CallerPIN, CallerDescription, CallerDepartment, CallerSign, MainCompManfacturer, MainCompType, MainCompModel, MainComponent,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-9]  MainCompName, MainCompSerNr, MainCompInvNr, MainCompSerNrProv, MainCompOpSys, MainCompLocationZip, MainCompLocationCity,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-10]  MainCompLocationStreet, MainCompLocationCountry, MainCompLocation, MainCompLocationTel, MainCompDescription,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-11]  MainCompLocationName, pro_objid, pcu_objid, psp_objid, psf_objid, pgl_objid, sev_objid, fat_objid, cst_objid, pst_objid,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-12]  cac_objid, ema_objid, InternalState, CallProvedFlag, ShortDescription, Description, Remarks, Diagnosis, edi_objid, EditTime,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-13]  EditTimeUTC, csc_objid, css_objid, csu_objid, cal_objid, SubCompManfacturer, SubCompType, SubCompModel, SubComponent,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-14]  SubCompName, SubCompSerNr, SubCompInvNr, SubCompSerNrProv, SubCompOpSys, SubCompLocationZip, SubCompLocationCity,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-15]  SubCompLocationStreet, SubCompLocationCountry, SubCompLocation, SubCompLocationTel, SubCompDescription, SubCompLocationName,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-16]  SysSpecField1, SysSpecField2, SysSpecField3, SysSpecField4, SysSpecField5, SysSpecField6, SysSpecField7, SysSpecField8,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-17]  SysSpecField9, SysSpecField10, XML_Info, CHDShortName, CHDFirstName, CHDLastName, CHDTitle, CHDSalutation, CHDTel, CHDTel2,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-18]  CHDMobileTel, CHDFax, CHDLocationZip, CHDLocationCity, CHDLocationStreet, CHDLocationCountry, CHDLocation, CHDEMail, CHDPIN,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-19]  CHDDescription, CHDDepartment, CHDSign, CCPShortName, CCPFirstName, CCPLastName, CCPTitle, CCPSalutation, CCPTel, CCPTel2,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-20]  CCPMobileTel, CCPFax, CCPLocationZip, CCPLocationCity, CCPLocationStreet, CCPLocationCountry, CCPLocation, CCPEMail, CCPPIN,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-21]  CCPDescription, CCPDepartment, CCPSign, bpr_objid, SlaResponseMinutes, SlaRecoveryMinutes, TotalResponseMinutes,
Aug 18 10:34:25 nut1 postgres[4933]: [44067-22]  TotalRecoveryMinutes, TotalAcknowledgeMinutes, Zone, unspsc, dev_objid, tzo_objid, isTestCall, que_objid, tec_objid from Calls
Aug 18 10:34:25 nut1 postgres[4933]: [44067-23]  where ID = 2265302
Aug 18 10:34:25 nut1 postgres[4933]: [44068] LOG:  duration: 0.008038 sec
Aug 18 10:34:25 nut1 postgres[4933]: [44069] LOG:  query: begin;
Aug 18 10:34:25 nut1 postgres[4933]: [44070] LOG:  duration: 0.000121 sec
Aug 18 10:34:25 nut1 postgres[4933]: [44071] LOG:  query: delete from Calls where objID = 2268645
Aug 18 10:34:25 nut1 postgres[4933]: [44072] LOG:  duration: 0.000675 sec
Aug 18 10:39:18 nut1 postgres[4933]: [44073] LOG:  pq_recvbuf: unexpected EOF on client connection

-Philipp
-- 
: Dipl-Ing Philipp Reisner                      Tel +43-1-8178292-50 :
: LINBIT Information Technologies GmbH          Fax +43-1-8178292-82 :
: Schönbrunnerstr 244, 1120 Vienna, Austria    http://www.linbit.com :


In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2003-08-18 13:38:00
Subject: Re: Deadlock in PostgreSQL 7.3.4
Previous:From: Joe ConwayDate: 2003-08-18 04:48:07
Subject: Re: Database Grants Bug

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