Horton

Thread

  1. Deadlock Problem

    Gavin Love <gavin@aardvarkmedia.co.uk> — 2005-06-15T11:10:40Z

    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

  2. Re: Deadlock Problem

    Csaba Nagy <nagy@ecircle-ag.com> — 2005-06-15T11:24:44Z

    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

  3. Re: Deadlock Problem

    Gavin Love <gavin@aardvarkmedia.co.uk> — 2005-06-15T11:58:07Z

    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
>

  4. Re: Deadlock Problem

    Csaba Nagy <nagy@ecircle-ag.com> — 2005-06-15T12:07:44Z

    Because I guess id is your primary key, and that would make your
transactions always update the same rows in the same order. It really
doesn't matter if you have other criteria in the where clause, the
issuing order of the statements is the problem.
Deadlock will occur if 2 concurrent transactions are updating the same
rows in different order, so ordering will eliminate the deadlocks.

A typical deadlock scenario:

T1=transaction 1;
T2=transaction 2;

T1 starts;
T2 starts;
T1 updates id 1;
T2 updates id 2;
T1 wants to update id 2, but the row is locked by T2;
T2 wants to update id 1, but the row is locked by T1;
deadlock: both transactions wait for the other one to finish.

Now if both will try to update id 1 first and then id 2, it would be:

T1 starts;
T2 starts;
T1 updates id 1;
T2 wants to update id 1, but the row is locked by T1;
T1 updates id 2;
...
T1 finishes;
T2 updates id 1;
T2 updates id 2;
...
T2 finishes;


Generally, ordering in the same way the rows being updated in all
transactions in a system will eliminate most of the deadlocks.

HTH,
Csaba.


On Wed, 2005-06-15 at 13:58, Gavin Love wrote:
> 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
> >

  5. Re: Deadlock Problem

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-06-15T14:23:12Z

    Csaba Nagy <nagy@ecircle-ag.com> writes:
>> 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?
>
> Because I guess id is your primary key, and that would make your
> transactions always update the same rows in the same order.

If the deadlocks occur because different transactions actually try to
update the same rows concurrently, then this is an appropriate solution.

However I wonder if Gavin is getting bitten by foreign key deadlocks.
Is there any foreign key reference from the stats table to other tables?

			regards, tom lane

  6. Re: Deadlock Problem

    Gavin Love <gavin@aardvarkmedia.co.uk> — 2005-06-15T14:45:55Z

    > If the deadlocks occur because different transactions actually try to
> update the same rows concurrently, then this is an appropriate solution.
> 
> However I wonder if Gavin is getting bitten by foreign key deadlocks.
> Is there any foreign key reference from the stats table to other tables?

Tom,

There are no foreign key references to or from this table as the updates 
to it consume a large % of the overall DB activity so I kept everything 
to a minimum.

I think Csaba has spotted the real problem but given that changing the 
order is not that easy and these stats are not mission critical I may 
just live with the odd deadlock. At least I understand why this is 
happening now.

Thanks,

Gavin