Re: Trigger and deadlock

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