Skip site navigation (1) Skip section navigation (2)

Re: Deadlock like scenario

From: Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com>
To: Robins Tharakan <robins(dot)tharakan(at)comodo(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org, Shridhar Polas <shridharpolas(at)gmail(dot)com>
Subject: Re: Deadlock like scenario
Date: 2011-11-11 08:31:27
Message-ID: CA+h6AhjmF9_=sEh6t-o11d4oMqHg9Nrrgg_a8QM0CSKyJ3XOLw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
> 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/

In response to

pgsql-admin by date

Next:From: Ramiro BarrecaDate: 2011-11-11 15:05:43
Subject: Full text search: How to implement?
Previous:From: Karuna KarpeDate: 2011-11-11 05:10:37
Subject: Re: error log, tablespace, wal files

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group