Re: preventing deadlocks

Evgeny.

On Tue, 3 Jan 2006, Jim C. Nasby wrote:

> On Mon, Jan 02, 2006 at 11:36:11AM +0200, Tsirkin Evgeny wrote:
> > Thanks for answer.However i have already searched for a way to make count
> > faster and didn't find anything.
> > Any pointers will be appreciated.
>
> 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?

> 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
counting is something that everybody does.
[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?
[2] Maybe there is a MVCC or something solution like Bruno suggested (that
i did not realy understood thought).
Evgeny.
>
> > On Wed, 28 Dec 2005, Bruno Wolff III wrote:
> >
> > > 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 somecalculations 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
> > > > ident | numeric(10,0) |
> > > > count | integer
> > > >
> > > >
> > > > The problem of course is thelocking issues while changing a and b
> > > > tables.What i am doing now is to
> > > > select 1 from counterwhere counter_type='a' and ident in (1,2,3,4)
> > > > for update;
> > > > select 1 from counterwhere 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 awhere pkey in (5,6,7) for update;
> > > >
> > > > My problems:
> > > > [1] Is the for update lock anouthhere?
> > > > [2] The "for update" queriesHAVE 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
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: don't forget to increase your free space map settings
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>