From: Bruno Wolff III <bruno@wolff.to>
To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
Cc: pgsql-admin@postgresql.org
Subject: Re: preventing deadlocks
Date: Wed, 28 Dec 2005 14:26:07 -0600
Message-ID: <20051228202607.GA5194@wolff.to> (raw)
In-Reply-To: <43B10E07.7010503@mail.jct.ac.il>
References: <43B10E07.7010503@mail.jct.ac.il>
On Tue, Dec 27, 2005 at 11:48:55 +0200,
Tsirkin Evgeny <tsurkin@mail.jct.ac.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: bruno@wolff.to, tsurkin@mail.jct.ac.il
Subject: Re: preventing deadlocks
In-Reply-To: <20051228202607.GA5194@wolff.to>
References: <43B10E07.7010503@mail.jct.ac.il> <20051228202607.GA5194@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox