Skip site navigation (1) Skip section navigation (2)

preventing deadlocks

From: Tsirkin Evgeny <tsurkin(at)mail(dot)jct(dot)ac(dot)il>
To: pgsql-admin(at)postgresql(dot)org
Subject: preventing deadlocks
Date: 2005-12-27 09:48:55
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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)         |                                  
 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 
select 1 from counter  where counter_type='b' and ident in (5,6,7) for 
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 
Hope for help and sorry for long message.


pgsql-admin by date

Next:From: Murugan GDate: 2005-12-27 11:31:51
Subject: How to run a posgresql function/procedure thro crontab in Linux ES 4.0
Previous:From: Aftab AlamDate: 2005-12-27 09:14:32
Subject: sending mail from Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group