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