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: 43B10E07.7010503@mail.jct.ac.il (view raw or flat)
Thread:
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)         |                                  
----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



Responses

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-2014 The PostgreSQL Global Development Group