Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, 07 May 2010 10:29:20 -0400
Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

> Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux