Re: deadlock detected

Richard Huxton <dev(at)archonet(dot)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