Re: Deadlock Problem

Gavin,

Are you ordering the updates by id inside one transaction ? You should
order the execution of the statements by id inside a transaction, and
the deadlocks should go away.

HTH,
Csaba.

On Wed, 2005-06-15 at 13:10, Gavin Love wrote:
> I am getting a number of deadlock errors in my log files and I was
> wondering if anyone knows how I can stop them.
>
> Query failed: ERROR: deadlock detected DETAIL: Process 11931 waits for
> ShareLock on transaction 148236867; blocked by process 11932. Process
> 11932 waits for ShareLock on transaction 148236866; blocked by process
> 11931.
>
> This is for a web application. Whenever a search result is displayed I
> need to update a counter to say it has been viewed which is done with
> between 1 and 15 updates in one transaction of the form.
>
> BEGIN;
> UPDATE stats SET click_count = click_count+1 WHERE id = '122'
> UPDATE stats SET click_count = click_count+1 WHERE id = '123'
> UPDATE stats SET click_count = click_count+1 WHERE id = '124'
> etc...
> COMMIT;
>
> My lock management config is:
> deadlock_timeout = 2000 # in milliseconds
> #max_locks_per_transaction = 64 # min 10, ~200*max_connections bytes
>
> I am using Postgres 8.0.3
>
> Does anyone know how I can stop these deadlocks from occurring?
>
> They are not a big problem as losing a few it only happens a couple of
> times a day but I prefer to have everything working as it should.
>
> Thanks
>
> Gavin
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly