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