Re: Trigger and deadlock

Hi,

Thanks for your quick reply!
I found the table. But the ctid of the row has changed. But during my test,
I update only 1 row from this table, so I know the row.

I had already put log_statement to 'all'. It's strange because in the log,
I only see simple "SELECT ... FROM" on this table (no UPDATE or SELECT FOR
UPDATE). But I can't see request from trigger (not show by
log_statement='all'?).

Here is a log sample (simplify: insert into X should update the counter on
the specific row from Y)

1286781 22142 2013-07-26 13:55:22 CEST LOG: execute <unnamed>: insert into
X...
1286780 22426 2013-07-26 13:55:22 CEST LOG: execute <unnamed>: insert into
X...
====> it should be UPDATE on Y from trigger here
1286781 22142 2013-07-26 13:55:22 CEST LOG: execute select this_.id as
id54_0_,.... from ....
[...22142 follow its way, commit, and carry another request ]
1286785 22142 2013-07-26 13:55:23 CEST LOG: execute <unnamed>: insert into
X
1286785 22142 2013-07-26 13:55:25 CEST ERROR: deadlock detected
1286785 22142 2013-07-26 13:55:25 CEST DETAIL: Process 22142 waits for
ShareLock on transaction 1286780; blocked by process 22426.
Process 22426 waits for ExclusiveLock on tuple (365,13) of relation 2027300
of database 2026760; blocked by process 22142.
1286785 22142 2013-07-26 13:55:25 CEST HINT: See server log for query
details.
1286785 22142 2013-07-26 13:55:25 CEST CONTEXT: SQL statement "UPDATE Y
1286785 22142 2013-07-26 13:55:25 CEST STATEMENT: insert into X
Process 22426 waits for ShareLock on transaction 1286782; blocked by
process 22429.
1286785 22142 2013-07-26 13:55:25 CEST LOG: execute S_3: ROLLBACK

I will try to query pg_locks to see more info

Thanks

2013/7/26 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>

> Loïc Rollus wrote:
> > I've try to make some concurrency robustness test with an web server app
> that use Hibernate and
> > Postgres.
> > It seems that my trigger make deadlock when multiple thread use it.
> >
> > I will try to simplify examples:
> > I have a table "films"(id, title,director) and a table
> "directors"(id,name,nbreFilms). I want to
> > automaticaly count directors's films.
> >
> > So I have this triggers after each insert on films:
> >
> > CREATE OR REPLACE FUNCTION incrementDirectors() RETURNS TRIGGER AS
> $incDirectors$
> > BEGIN
> > UPDATE directors
> > SET nbreFilm = nbreFilm + 1
> > WHERE directors.id = NEW.director;
> > RETURN NEW;
> > END;
> > $incDirectors$ LANGUAGE plpgsql;
> > CREATE TRIGGER triggerIncrDirectors AFTER INSERT ON films FOR EACH ROW
> EXECUTE PROCEDURE
> > incrementDirectors();
> >
> >
> > When I do a lot of INSERT films at the same time, I have this error:
> >
> >
> ******************************************************************************************************
> > ********
> > 1286785 22142 2013-07-26 13:55:25 CEST ERROR: deadlock detected
> > 1286785 22142 2013-07-26 13:55:25 CEST DETAIL: Process 22142 waits for
> ShareLock on transaction
> > 1286780; blocked by process 22426.
> > Process 22426 waits for ExclusiveLock on tuple (365,13) of relation
> 2027300 of database 2026760;
> > blocked by process 22142.
> > 1286785 22142 2013-07-26 13:55:25 CEST HINT: See server log for query
> details.
> >
> ******************************************************************************************************
> > ********
> >
> > If I look in postgresql log for process, I see this (its a web app):
> > 1.Process 22142: take a ADD request from http,
> > 2.Process 22426: take a ADD request from http,
> > 3.Process 22142: do INSERT of new film
> > 4.Process 22146: do INSERT of new film
> > 5.Process 22142: continue request (Process 22146 seems to be blocked)
> and do COMMIT
> > 6.Process 22142: take a ADD request from http,
> > 7.Process 22142: do INSERT of new film
> > 8.DEADLOCK: process 22142 is waiting for 22146 and 22146 is waiting for
> 22142
> >
> > I don't understant why the commit of the process 22142 won't unlock
> process 22426.
> >
> > Have you an idea?
>
> It would be interesting to know what relation 2027300 of database 2026760
> is.
>
> Then you could select the offending tuple with
> SELECT * FROM <tablename> WHERE ctid='(365,13)';
>
> What I would do is to set log_statement='all' and see what
> exact SQL statements are issued. Maybe Hibernate does something
> you do not know.
>
> It may also be interesting to query pg_locks immediately before
> commit to see what locks one transaction holds.
>
> Yours,
> Laurenz Albe
>