preventing deadlocks
От | Tsirkin Evgeny |
---|---|
Тема | preventing deadlocks |
Дата | |
Msg-id | 43B10E07.7010503@mail.jct.ac.il обсуждение исходный текст |
Ответы |
Re: preventing deadlocks
|
Список | 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
В списке pgsql-admin по дате отправления: