Deadlock Problem

From: Gavin Love <gavin(at)aardvarkmedia(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Deadlock Problem
Date: 2005-06-15 11:10:40
Message-ID: 42B00CB0.1040906@aardvarkmedia.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Zlatko Matić 2005-06-15 11:15:50 Re: user groups
Previous Message Danny Gaethofs 2005-06-15 10:51:47 Best approach setting up PostgreSQL