From: | Loïc Rollus <loicrollus(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Trigger and deadlock |
Date: | 2013-07-26 13:00:11 |
Message-ID: | CADUcbP-7bCLFEE6HMJV3oc8rZ5-S0WiM3a0SeL5qBkpy1wyA8w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
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?
Thanks :)
From | Date | Subject | |
---|---|---|---|
Next Message | Giuseppe Broccolo | 2013-07-26 13:18:31 | Re: How to do incremental / differential backup every hour in Postgres 9.1? |
Previous Message | MauMau | 2013-07-26 12:31:12 | DATE type output does not follow datestyle parameter |