Re: Deadlock like scenario
| От | Raghavendra |
|---|---|
| Тема | Re: Deadlock like scenario |
| Дата | |
| Msg-id | CA+h6AhjmF9_=sEh6t-o11d4oMqHg9Nrrgg_a8QM0CSKyJ3XOLw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Deadlock like scenario (Robins Tharakan <robins.tharakan@comodo.com>) |
| Список | 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 detected2011-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;AS
$$
BEGIN
UPDATE PARENT SET A=NEW.A;
RETURN NEW;
END;
$$
LANGUAGE PLPGSQL;
I believe you should give a try test on this.
В списке pgsql-admin по дате отправления: