deadlock detected

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

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