Trigger and deadlock

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 :)

Responses

Browse pgsql-general by date

  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