Re: preventing deadlocks

On Tue, Dec 27, 2005 at 11:48:55 +0200,
Tsirkin Evgeny <tsurkin(at)mail(dot)jct(dot)ac(dot)il> wrote:
>
> Hi list!
> My issue is as follows :
> I have to do some calculations based on *exact* number of rows in 2
> tables (with a filter) meaning:
> SELECT count(*) FROM a WHERE a.row1 in (1,2,3,4);
> SELECT count(*) FROM b WHERE b.row1 in (1,2,3,4);
> However i couldn't use the count(*) since it is too slow beacause of the
> table size.

You should look through the past archives on this subject. There is a way to
do this that uses MVCC for incremental changes. You do want to make a sweep
through the delta table periodically which will need stronger locking, but
this won't block reads on a and b.

> So,i created a trigger that on insert increments and on delete
> decriments special "counter" table
> that contains
> \d counter
> Column | Type | Modifiers
> --------------+-----------------------+-----------
> counter_type | character varying(30) | ---- the
> table name (a or b)
> ident | numeric(10,0) |
> ----the
> count | integer
> | ----the count
>
>
> The problem of course is the locking issues while changing a and b
> tables.What i am doing now is to
> select 1 from counter where counter_type='a' and ident in (1,2,3,4) for
> update;
> select 1 from counter where counter_type='b' and ident in (5,6,7) for
> update;
> Befor changing anything in tables "a" and "b" in transaction .I am also
> doing for update select on the
> "a" and "b" tables itself ,that is:
> select b from a where pkey in (5,6,7) for update;
>
> My problems:
> [1] Is the for update lock anouth here?
> [2] The "for update" queries HAVE to be done in the same order in all
> application which is pretty error prone -
> it is very easy to forget in one place and get a deadlock.
> [3] Can i make a trigger that automatically locks the counter_type='b'
> if a for update select was done on table b?
> something like (pseudo):
> trigger on select for update table b{
> select for update where ident = OLD.pkey ;
> }
> [4] Can i combine queries for multiple tables to make locking atomic:
> select 1 from b,counter where b.pkey in (1,2,3,4) and counter.ident in
> (1,2,3,4);
> Hope for help and sorry for long message.
> evgeny
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org