Re: Deadlock like scenario

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 
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/


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux