Re: Deadlock Problem

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