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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-admin by date

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