Thread

1. deadlock detected

   AI Rumman <rummandba@gmail.com> — 2012-11-05T18:39:36Z

   Hi all,
   
   I am using Postrgesql 9.1
   
   I got a message in my log:
   ERROR:  deadlock detected
   DETAIL:  Process 20265 waits for ShareLock on transaction 27774015; blocked
   by process 20262.
           Process 20262 waits for ShareLock on transaction 27774018; blocked
   by process 20265.
           Process 20265: UPDATE t1 SET product_id = 1017966 WHERE ticketid =
   '2170501'
           Process 20262: UPDATE c1 SET deleted=1 WHERE id='2170501'
   HINT:  See server log for query details.
   STATEMENT:  UPDATE t1 SET product_id = 1017966 WHERE ticketid = '2170501'
   
   How may I get more information about this deadlock like which queries
   created it.
   
   Thanks.
   

2. Re: deadlock detected

   Richard Huxton <dev@archonet.com> — 2012-11-05T19:03:34Z

   On 05/11/12 18:39, AI Rumman wrote:
   > Hi all,
   >
   > I am using Postrgesql 9.1
   >
   > I got a message in my log:
   > ERROR:  deadlock detected
   > DETAIL:  Process 20265 waits for ShareLock on transaction 27774015; 
   > blocked by process 20262.
   >         Process 20262 waits for ShareLock on transaction 27774018; 
   > blocked by process 20265.
   >         Process 20265: UPDATE t1 SET product_id = 1017966 WHERE 
   > ticketid = '2170501'
   >         Process 20262: UPDATE c1 SET deleted=1 WHERE id='2170501'
   > HINT:  See server log for query details.
   > STATEMENT:  UPDATE t1 SET product_id = 1017966 WHERE ticketid = '2170501'
   >
   > How may I get more information about this deadlock like which queries 
   > created it.
   >
   The error message shows which queries - your two UPDATEs. I'm guessing 
   either t1 or c1 are views and so refer to the same row with id "2710501".
   
   --
      Richard Huxton
   
   
   
   

3. Re: deadlock detected

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-11-05T20:38:12Z

   Richard Huxton <dev@archonet.com> writes:
   > On 05/11/12 18:39, AI Rumman wrote:
   >> ERROR:  deadlock detected
   >> DETAIL:  Process 20265 waits for ShareLock on transaction 27774015; 
   >> blocked by process 20262.
   >> Process 20262 waits for ShareLock on transaction 27774018; 
   >> blocked by process 20265.
   >> Process 20265: UPDATE t1 SET product_id = 1017966 WHERE 
   >> ticketid = '2170501'
   >> Process 20262: UPDATE c1 SET deleted=1 WHERE id='2170501'
   >> HINT:  See server log for query details.
   >> STATEMENT:  UPDATE t1 SET product_id = 1017966 WHERE ticketid = '2170501'
   >> 
   >> How may I get more information about this deadlock like which queries 
   >> created it.
   
   > The error message shows which queries - your two UPDATEs. I'm guessing 
   > either t1 or c1 are views and so refer to the same row with id "2710501".
   
   Another likely theory is both transactions trying to lock the same row
   as a result of foreign key constraint enforcement.  We'd need to know
   more about t1 and c1 and their FK constraints before being able to say
   anything definite.
   
   (Also, I suspect you need additional commands earlier in the same
   transactions to get an actual deadlock here, rather than just one update
   being blocked by the other ...)
   
   			regards, tom lane