Re: preventing deadlocks

From: Tsirkin Evgeny <tsurkin(at)mail(dot)jct(dot)ac(dot)il>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: preventing deadlocks
Date: 2006-01-05 08:34:31
Message-ID: 43BCDA17.6070806@mail.jct.ac.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin


Bruno Wolff III wrote:

>On Wed, Jan 04, 2006 at 10:51:55 +0200,
> Tsirkin Evgeny <tsurkin(at)mail(dot)jct(dot)ac(dot)il> wrote:
>
>
>>>What you want to do in the trigger isinsert a new row into a table that
>>>contains the change in count, instead of trying to update a single row
>>>for each value/ident (btw, you'll probably get better performance if you
>>>make ident an int instead of a numeric).
>>>
>>>
>>Why?
>>
>>
>
>Because this doesn't block other processes and still gives you correct
>results.
>
>
>
I understand THAT ,I meant why int will give me more performance.

>>>So now you'll have a list of
>>>changes to the value, which you will periodically want to roll up into a
>>>table that just stores the count.
>>>
>>>
>>Interesting idea.Thanks.However it pretty complicates things ,maybe there
>>will be simpler solution.Something i did not thought about at all - i think that
>>
>>
>
>Not that avoids blocking. If you don't have a lot of concurrent queries then
>this may not be an issue for you.
>
>
>
Well i DO have a lot of concurrent queries,that is the whole point.

>>counting is something that everybody does.
>>
>>
>
>No it isn't. Smetimes they aren't needed at all, sometimes approximate values
>are good enough, and even when they are needed, it is often the case that
>it is better for count queries to run slower so that other queries run faster.
>
>
>
It was just a hope of mine.

>>[1] I have also a hope that i can create a trigger that locks counter
>>table once a 'select for update' was done on one of the tables i count.
>>However how can i say if a select that fires a trigger is a 'for update'
>>one?
>>
>>
>
>If you have a counter table and do an UPDATE that will lock that row.
>If you have multiple tables that you keep counts for you will want to lock
>the whole counter table using a LOCK command or else you can get deadlocks.
>Note this means that inserts and deletes from any of the tracked tables
>will block inserts and deletes of those tables in other concurrent queries.
>
>
>
That does not help me .What i want is :
select 1 from table a where whatever for update;
issuing this should fire a trigger that does:
select 1 from table counter where a.pkey=counter.ident or something
however this should only be done for select FOR UPDATE!
And i can't know that inside the trigger!

>>[2] Maybe there is a MVCC or something solution like Bruno suggested (that
>>i did not realy understood thought).
>>
>>
>
>The explanation given at the top is the gist of the solution that uses MVCC
>advantagesously.
>
>
OK.

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Bruno Wolff III 2006-01-05 08:40:30 Re: preventing deadlocks
Previous Message Bruno Wolff III 2006-01-05 06:57:47 Re: preventing deadlocks