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

On Fri, 07 May 2010 10:29:20 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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.

That's nice to know... but even skipping the whole drop/create
trigger thing the lock problem is still there and still happens near
the end of a long transaction that makes a lot of other stuff on
mainly one table.

The statement that cause the lock is not always the same, what is
"constant" across several modification of the overall transaction
is: the lock happens near the end of the transaction.

I'd say that that *should* be the only one transaction *writing* to
the few tables that are involved in the transaction, some of which
are very small (hundreds of record).

I expect (that doesn't mean I know) that from a writing point of
view the overall transaction doesn't involve any write concurrency.
So I thought I wouldn't be involved in stable locking problems on
*random* statement whose only fault is being near the end of the
whole transaction.

I need some help on how to learn how to track down this kind of
problem.

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it