Re: preventing deadlocks

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.