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

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 reshuffled the update process and I started to have other lock
problems.
The only process that I'm aware of that should be *writing* to the
tables involved is the update process. I'd expect other process are
reading but just the update should be writing.

The lock problem happens nearly at the end of the overall update
process that is one big transaction, reshuffling a bit the steps
doesn't make it go away... it just locks on other statements but
still at the *end* of the process after a bunch of update and insert
have been made on a bunch of other tables the largest of whom is an
update of roughly 85834 rows on a table containing 1M rows.

The only thing that look constant is: I get a deadlock at the end of
a long process on a random statement.

Where am I going to start from to solve this?

DETAIL:  Process 3662 waits for ShareLock on transaction 59301028;
blocked by process 4303. Process 4303 waits for ShareLock on
transaction 59299342; blocked by process 3662. CONTEXT:  SQL
statement "update catalog_items set Authors= $1  where ItemID= $2 "
PL/pgSQL function "updateauthorsall" line 19 at SQL statement

create or replace function UpdateAuthorsAll()
  returns void
  as
  $$
  declare
    _row record;
    _ItemID bigint;
    _Authors varchar(1024);
    _AuthorsOLD varchar(1024);
    _Name varchar(50);
  begin
    _Authors := '';
    _ItemID := null;
    for _row in select a.Name, ia.ItemID from   catalog_itemauthor
      ia join   catalog_author   a on a.AuthorID=ia.AuthorID
      order by ia.ItemID
      loop
      if(_row.ItemID<>_ItemID) then
        if(length(_Authors)>2) then
          _Authors := substring(_Authors from 3);
          select into _AuthorsOLD Authors from   catalog_items
            where ItemID=_ItemID;
          if(coalesce(_Authors, '')<>coalesce(_AuthorsOLD, '')) then
            update   catalog_items   set Authors=_Authors where
              ItemID=_ItemID;
          end if;
        end if;
        _Authors := '';
      end if;
      _ItemID := _row.ItemID;
      _Name := trim(E' \t' from _row.Name);
      if(length(_Name)>0) then
        _Authors := _Authors || ', ' || _Name;
      end if;
    end loop;
  return;
  end;
$$ language plpgsql volatile;

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

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

On Fri, 07 May 2010 10:29:20 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

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