Re: unable to avoid a deadlock at the end of a long transaction

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: unable to avoid a deadlock at the end of a long transaction
Date: 2010-05-07 14:29:20
Message-ID: 12291.1273242560@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> writes:
> I've been having this:
> psql:include/custom/import_update.custom.sql:63: ERROR: deadlock
> detected DETAIL: Process 13349 waits for AccessExclusiveLock on
> relation 250510 of database 248569; blocked by process 14153.
> Process 14153 waits for ShareLock on transaction 59160779; blocked
> by process 13349. CONTEXT: SQL statement "drop trigger if exists
> FT1IDX_catalog_brands_update_trigger on catalog_brands" PL/pgSQL
> function "ft1idx_trigger_drop" line 2 at SQL statement

I'd suggest not using DROP TRIGGER in operations that need to run
concurrently with other accesses to the same table. Consider fixing
things so the trigger is always there but knows enough to not do
anything when it doesn't need to.

The particular case here seems to be that the transaction doing DROP
has already modified some rows in the same table, and there are now
other transactions blocked waiting to modify those same rows. Perhaps
you could avoid combining the DROP with the data modifications. Or
if you must do it like that, take exclusive lock via LOCK TABLE at the
start of the whole transaction, so you aren't trying to do a lock
upgrade partway through.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bryan Murphy 2010-05-07 14:53:13 Re: missing chunk number 0 for toast value 25693266 in pg_toast_25497233
Previous Message Sorin Schwimmer 2010-05-07 14:28:41 Formatted reports