> The use case is something like:- >> >> ·There is a table let’s say “A” and the trigger is created on this table >> let say “A_TRIGGER”. >> >> ·The trigger captures the data change happens in table A into table B. >> >> ·There would be a huge insert, update, delete on table A, the side >> effect of it table B also get updated very frequently. >> >> In my recent observation I seen deadlocks with ShareLock, when any trigger doing with INSERT/UPDATE/DELETE. As Robins Tharakan said its also with any blocking operation like REINDEX.
If your logs looking like Eg:- 2011-07-09 04:10:44 ETC/GMT ERROR: deadlock detected 2011-07-09 04:10:44 ETC/GMT DETAIL: Process 22986 waits for ShareLock on transaction 939; blocked by process 22959. Then I say, you need to give a try by testing Lock on Parent Table (i.e. in your case A) by *SHARE UPDATE EXCLUSIVE MODE *in trigger calling function. * * *Eg:-* CREATE OR REPLACE FUNCTION CHILD_TEST() RETURNS TRIGGER AS $$ BEGIN * LOCK TABLE PARENT IN SHARE UPDATE EXCLUSIVE MODE;* UPDATE PARENT SET A=NEW.A; RETURN NEW; END; $$ LANGUAGE PLPGSQL; Here *SHARE UPDATE EXCLUSIVE MODE *Works as a Select *For update* clause. * * http://www.postgresql.org/docs/9.0/static/sql-lock.html I believe you should give a try test on this. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
