pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
To: pgsql-admin@postgresql.org
Subject: preventing deadlocks
Date: Tue, 27 Dec 2005 11:48:55 +0200
Message-ID: <43B10E07.7010503@mail.jct.ac.il> (raw)


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.
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





view thread (12+ messages)  latest in thread

Message-ID: <43B10E07.7010503@mail.jct.ac.il>
Permalink:  ../43B10E07.7010503@mail.jct.ac.il/
Also on:    postgresql.org/message-id/43B10E07.7010503@mail.jct.ac.il

 · 

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: tsurkin@mail.jct.ac.il
  Subject: Re: preventing deadlocks
  In-Reply-To: <43B10E07.7010503@mail.jct.ac.il>
  References: <43B10E07.7010503@mail.jct.ac.il>

* 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