pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDeadlock like scenario
3+ messages / 3 participants
[nested] [flat]
* Deadlock like scenario
@ 2011-11-11 03:04 Shridhar Polas <shridharpolas@gmail.com>
2011-11-11 04:30 ` Re: Deadlock like scenario Robins Tharakan <robins.tharakan@comodo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Shridhar Polas @ 2011-11-11 03:04 UTC (permalink / raw)
To: pgsql-admin
Hi,
I am experience the problem where locks acquired by postgres are NOT getting
released.
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.
. There is periodic job that runs every midnight to do full vacuum
and re-indexing on table B (but NOT on table A).
. It has been found that the queries are hanging and never completes
when there are some operation on table A.
Can someone please help me out to resolve this problem. If someone need more
information on this then please let me know.
Thanks in advance,
Shridhar
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Deadlock like scenario
2011-11-11 03:04 Deadlock like scenario Shridhar Polas <shridharpolas@gmail.com>
@ 2011-11-11 04:30 ` Robins Tharakan <robins.tharakan@comodo.com>
2011-11-11 08:31 ` Re: Deadlock like scenario Raghavendra <raghavendra.rao@enterprisedb.com>
0 siblings, 1 reply; 3+ messages in thread
From: Robins Tharakan @ 2011-11-11 04:30 UTC (permalink / raw)
To: pgsql-admin; +Cc: Shridhar Polas <shridharpolas@gmail.com>
Hi Shridhar,
Probably you should read a bit about how PostgreSQL locks and what
operation requires which locks. More importantly what happens when these
locks are not available.
ISTM, your trigger operation tries a bulk operation
(INSERT/DELETE/UPDATE) on table B but the nightly reindex acquires a
lock because of which your bulk operations get stuck. Have you tried
reindexing CONCURRENTLY ? It doesn't solve all problems as Greg rightly
points out but could help in this situation.
http://www.postgresql.org/docs/8.4/static/explicit-locking.html#TABLE-LOCK-COMPATIBILITY
http://www.postgresql.org/docs/8.4/static/sql-reindex.html
--
Robins Tharakan
On 11/11/2011 08:34 AM, Shridhar Polas wrote:
> Hi,
>
> I am experience the problem where locks acquired by postgres are NOT
> getting released.
>
> 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.
>
> ·There is periodic job that runs every midnight to do full vacuum and
> re-indexing on table B (but NOT on table A).
>
> ·It has been found that the queries are hanging and never completes when
> there are some operation on table A.
>
> Can someone please help me out to resolve this problem. If someone need
> more information on this then please let me know.
>
> Thanks in advance,
>
> Shridhar
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Deadlock like scenario
2011-11-11 03:04 Deadlock like scenario Shridhar Polas <shridharpolas@gmail.com>
2011-11-11 04:30 ` Re: Deadlock like scenario Robins Tharakan <robins.tharakan@comodo.com>
@ 2011-11-11 08:31 ` Raghavendra <raghavendra.rao@enterprisedb.com>
0 siblings, 0 replies; 3+ messages in thread
From: Raghavendra @ 2011-11-11 08:31 UTC (permalink / raw)
To: Robins Tharakan <robins.tharakan@comodo.com>; +Cc: pgsql-admin@postgresql.org, Shridhar Polas <shridharpolas@gmail.com>
> 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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-11-11 08:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-11 03:04 Deadlock like scenario Shridhar Polas <shridharpolas@gmail.com>
2011-11-11 04:30 ` Robins Tharakan <robins.tharakan@comodo.com>
2011-11-11 08:31 ` Raghavendra <raghavendra.rao@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox