pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (12+ messages)  latest in thread

Message-ID: <20051228202607.GA5194@wolff.to>
Permalink:  ../20051228202607.GA5194@wolff.to/
Also on:    postgresql.org/message-id/20051228202607.GA5194@wolff.to

 · 

reply

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