Deadlock Problem
Gavin Love <gavin@aardvarkmedia.co.uk>
From: Gavin Love <gavin@aardvarkmedia.co.uk>
To: pgsql-general@postgresql.org
Date: 2005-06-15T11:10:40Z
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