Hello,
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
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?
Thanks :)