Thread: share lock when only one user connected?

I'm running this query when I'm the only user and this should be the
only thing running.

update catalog_items
  set Code=s.codice, CodeAlt=s.codicealt, BrandID=s.marca,
  Name=initcap(s.nome), Description=s.desc_l1,
  qty=coalesce(s.quantita, 0),
  ListPrice=coalesce(s.prezzoListino, 0) ,
  valIva=s.valIva, ivaInc=s.ivaInc,
  StatusID=coalesce(s.stato, 1) ,
  Weight=s.peso, dataInserimento=s.dataInserimento, daPub=s.daPub,
  BestSeller=s.bestSeller, posInMag=s.posInMag , ISBN=s.ISBN,
  dataPub=coalesce(s.dataPubblicazione, datapub),
  updated=now()
  from (
    select a.id, a.codice, a.codicealt,
    a.marca, a.nome, a.desc_l1, a.quantita, a.prezzoListino,
    a.valIva, a.ivaInc, a.stato, a.peso, a.dataInserimento, a.daPub,
    a.bestSeller, a.posInMag, a.ISBN, ip.dataPubblicazione
    from
      import.Articoli a
      left join import.ArticoliPropTipo_1 ip on a.id=ip.idArt
      where a.action=8
    ) as s
    where s.id=catalog_items.ItemID
;

And I get

DETAIL:  Process 7188 waits for ShareLock on transaction 110562621;
blocked by process 7244. Process 7244 waits for ShareLock on
transaction 110562544; blocked by process 7188.

On that table 2 triggers are defined:

create or replace function FT1IDX_catalog_items_update() returns
trigger as
$$
declare
    _Name varchar(64);
begin
  select into _Name Name from catalog_brands
   where BrandID=new.BrandID;
   new.FT1IDX:=
     GetFTIDX('pg_catalog.english', new.Code, new.CodeAlt, new.ISBN,
     new.Name, new.Authors, _Name);
  return new;
end;
$$ language plpgsql volatile;

create or replace function FT1IDX_catalog_brands_update() returns
trigger as
$$
begin
  if(TG_OP='DELETE') then
    update catalog_items set
      FT1IDX=
       GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name, Authors, '')
        where BrandID=old.BrandID;
    return old;
  else
    if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then
      update catalog_items set
        FT1IDX=
          GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name, Authors, new.Name) where BrandID=new.BrandID;
    end if;
  end if;
return new;
end;
$$ language plpgsql volatile;

What could it be? how can I fix it?



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

Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> I'm running this query when I'm the only user and this should be the
> only thing running.

> And I get

> DETAIL:  Process 7188 waits for ShareLock on transaction 110562621;
> blocked by process 7244. Process 7244 waits for ShareLock on
> transaction 110562544; blocked by process 7188.

It's pretty hard to believe that you haven't got two processes ...
maybe there's something using dblink down in there somewhere?

Also, if you're using a recent PG release, the postmaster log should
contain additional information about the deadlock, like the specific
queries involved on both sides.

            regards, tom lane

On Thu, 28 Oct 2010 13:57:18 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> > I'm running this query when I'm the only user and this should be
> > the only thing running.
>
> > And I get
>
> > DETAIL:  Process 7188 waits for ShareLock on transaction
> > 110562621; blocked by process 7244. Process 7244 waits for
> > ShareLock on transaction 110562544; blocked by process 7188.
>
> It's pretty hard to believe that you haven't got two processes ...
> maybe there's something using dblink down in there somewhere?
>
> Also, if you're using a recent PG release, the postmaster log
> should contain additional information about the deadlock, like the
> specific queries involved on both sides.

I can't think of any other process running concurrently... but yeah
I'm surprised as well, and no there is no dblink running.

I may be wrong on assuming there is no other process running and
I'll check further, but at least I'm pretty sure there is no query
that is writing in that table other than the update.
These start to happen when involved row are above 80K and chances it
happens grow up as the number of rows involved grow (not a big
surprise).
Chances that this happens decrease if I drop the triggers. This may
just be related to the chance that 2 queries clashes since leaving
the triggers there increase the execution time.

There is nothing else on the log other than:
2010-10-26 18:12:09 CEST ERROR:  deadlock detected
2010-10-26 18:12:09 CEST DETAIL:  Process 9729 waits for ShareLock
on transaction 110146905; blocked by process 11082. Process 11082
waits for ShareLock on transaction 110145470; blocked by process
9729.

Followed by the update statement...

How am I going to find which statements where locking each other?

thanks

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

On 28 Oct 2010, at 19:28, Ivan Sergio Borgonovo wrote:

> I'm running this query when I'm the only user and this should be the
> only thing running.
>
> update catalog_items
...
>  from (
>    select a.id, a.codice, a.codicealt,
...
>    from
>      import.Articoli a
>      left join import.ArticoliPropTipo_1 ip on a.id=ip.idArt
>      where a.action=8
>    ) as s
>    where s.id=catalog_items.ItemID
> ;
>
> And I get
>
> DETAIL:  Process 7188 waits for ShareLock on transaction 110562621;
> blocked by process 7244. Process 7244 waits for ShareLock on
> transaction 110562544; blocked by process 7188.
>
> On that table 2 triggers are defined:

You left out the actual trigger definitions ;)

Could it be possible that you accidentally call the wrong trigger on update of catalog_items?

Another possibility is that the trigger on catalog_items has a side-effect of updating catalog_brands - which in turn
updatescatalog_items again, causing your situation. 

> create or replace function FT1IDX_catalog_items_update() returns
> trigger as
> $$
> declare
>     _Name varchar(64);
> begin
>  select into _Name Name from catalog_brands
>   where BrandID=new.BrandID;
>   new.FT1IDX:=
>     GetFTIDX('pg_catalog.english', new.Code, new.CodeAlt, new.ISBN,
>     new.Name, new.Authors, _Name);
>  return new;
> end;
> $$ language plpgsql volatile;
>
> create or replace function FT1IDX_catalog_brands_update() returns
> trigger as
> $$
> begin
>  if(TG_OP='DELETE') then
>    update catalog_items set
>      FT1IDX=
>       GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name, Authors, '')
>        where BrandID=old.BrandID;
>    return old;
>  else
>    if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then
>      update catalog_items set
>        FT1IDX=
>          GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name, Authors, new.Name) where BrandID=new.BrandID;
>    end if;
>  end if;
> return new;
> end;
> $$ language plpgsql volatile;
>
> What could it be? how can I fix it?
>
>
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4cca678310291669837610!

On Fri, 29 Oct 2010 08:19:27 +0200
Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:

I've to amend the "one user connected" assumption.
But I'm really sure there is no other process writing on
catalog_items.

There is a process that read catalog_items and write on another
table.

create catalog_items {
  itemid bigint primary key
}
create catalog_related {
  itemid bigint references catalog_items (itemid) on delete cascade,
  itemid_related bigint references catalog_items (itemid) on delete
  cascade
}

To add some more details the update is running inside a larger
transaction that update other tables.
There are no "on update cascade" and no other triggers.
I'm using 8.3.4

I admit I've a very naïve knowledge of locks and maybe I'm making
assumptions that aren't valid.
I've been able to find:
http://www.postgresql.org/docs/7.2/static/locking-tables.html
but no equivalent for 8.3
I assume the same is valid for 8.3 so since there are no explicit
LOCK TABLE on catalog_items what's left are the INDEX.
I've been experiencing the same problem even dropping the gin index
on the FT1IDX column but there are other btree index on that table.

How can I get more information in the logs to know which statement
were producing the lock?
One for sure was the update.

> On 28 Oct 2010, at 19:28, Ivan Sergio Borgonovo wrote:
>
> > I'm running this query when I'm the only user and this should be
> > the only thing running.
> >
> > update catalog_items
> ...
> >  from (
> >    select a.id, a.codice, a.codicealt,
> ...
> >    from
> >      import.Articoli a
> >      left join import.ArticoliPropTipo_1 ip on a.id=ip.idArt
> >      where a.action=8
> >    ) as s
> >    where s.id=catalog_items.ItemID
> > ;
> >
> > And I get
> >
> > DETAIL:  Process 7188 waits for ShareLock on transaction
> > 110562621; blocked by process 7244. Process 7244 waits for
> > ShareLock on transaction 110562544; blocked by process 7188.
> >
> > On that table 2 triggers are defined:
>
> You left out the actual trigger definitions ;)

create trigger FT1IDX_catalog_items_update_trigger
  after insert or update on catalog_items for each
  row execute procedure FT1IDX_catalog_items_update();
create trigger FT1IDX_catalog_brands_update_trigger
  after update or delete on catalog_brands for each
  row execute procedure FT1IDX_catalog_brands_update();

> Could it be possible that you accidentally call the wrong trigger
> on update of catalog_items?

> Another possibility is that the trigger on catalog_items has a
> side-effect of updating catalog_brands - which in turn updates
> catalog_items again, causing your situation.

Is this the case?
My intention was that when catalog_brands.brandid get changed
catalog_items.FT1IDX get updated

> > create or replace function FT1IDX_catalog_items_update() returns
> > trigger as
> > $$
> > declare
> >     _Name varchar(64);
> > begin
> >  select into _Name Name from catalog_brands
> >   where BrandID=new.BrandID;
> >   new.FT1IDX:=
> >     GetFTIDX('pg_catalog.english', new.Code, new.CodeAlt,
> > new.ISBN, new.Name, new.Authors, _Name);
> >  return new;
> > end;
> > $$ language plpgsql volatile;
> >
> > create or replace function FT1IDX_catalog_brands_update() returns
> > trigger as
> > $$
> > begin
> >  if(TG_OP='DELETE') then
> >    update catalog_items set
> >      FT1IDX=
> >       GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name,
> > Authors, '') where BrandID=old.BrandID;
> >    return old;
> >  else
> >    if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then
> >      update catalog_items set
> >        FT1IDX=
> >          GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN,
> > Name, Authors, new.Name) where BrandID=new.BrandID; end if;
> >  end if;
> > return new;
> > end;
> > $$ language plpgsql volatile;
> >
> > What could it be? how can I fix it?


thanks

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