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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
