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@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
