Re: deadlock on simple update

Well, the reason why deadlock happen is usually uncoordinated access to the
same resource - in this case the resouce is a database row. This has
nothing
to do with the complexity of the queries, but with the order of the
updates.

According to the log process 8253 waits for 8230, and 8230 waits for 8226.
And
by surprise, 8226 waits for 8253.

So I guess something like this happened

8253 updated row A but did not commit the change
8266 updated row B and attempted to update row A (so is blocked)
8230 updated row C and attempted to update row B (so is blocked)
8253 attempted to update row C (so is blocked)

Which is a deadlock - so don't expect a single update to cause deadlock,
most
likely it's a quite complicated scenario.

There are two way to fix it:

1) always lock the resources in the same order

2) ignore the deadlock and just restart the failed transaction

Tomas

On Tue, 11 May 2010 12:01:16 +0200, Jiří Pavlovský <jira(at)getnet(dot)cz> wrote:
> Hello,
> I have 8.4.2. I'm getting deadlock when multiple processes try to update
> a table. Strange is it is a simple table with no triggers firing etc.
> Just an id and a numerical field to update. So I'm at odds as to what
> could cause the deadlock?
>
> DETAIL: Process 8253 waits for ShareLock on transaction 7001023;
> blocked by process 8230.
> Process 8230 waits for ExclusiveLock on tuple (17,269) of
> relation 17544 of database 16417; blocked by process 8226.
> Process 8226 waits for ShareLock on transaction 7000961; blocked
> by process 8253.
> Process 8253:
> UPDATE statistics SET count = count + 1 WHERE ArticleID=$1 AND
> Language=$2
>
> Process 8230:
> UPDATE statistics SET count = count + 1 WHERE ArticleID=$1 AND
> Language=$2
>
> Process 8226:
> UPDATE statistics SET count = count + 1 WHERE ArticleID=$1 AND
> Language=$2
>
> HINT: See server log for query details.
> STATEMENT:
> UPDATE statistics SET count = count + 1 WHERE ArticleID=$1 AND
> Language=$2
>
> ERROR: current transaction is aborted, commands ignored until end of
> transaction block