Hi Csaba,
I am not ordering them by ID as in reality the where condition is more
complex than in my example
UPDATE stats SET click_count = click_count+1 WHERE month = '06' AND year
= '2005' AND type = 'a' AND id = '123' AND count_type = 'b'";
Can you explain why sorting by ID would make a difference as I don't
understand why what you are suggesting should make a difference?
Thanks,
Gavin
Csaba Nagy wrote:
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
