Using postgres 9.3, I have a table called regression_runs that stores some counters. When a row in this table is updated, inserted, or deleted, a trigger function is called to update a row in the nightly_runs table to keep a running total of those counters for all regression_runs with the given ID. The approach I've taken is fairly widely documented. My problem, however is that I'm running into deadlocks when multiple processes are trying to simultaneously insert new rows in the regression_runs table with the same nightly_run_id.
The regression_runs table looks like this:
regression=> \d regression_runs
Table "public.regression_runs"
Column | Type | Modifiers
-----------------+--------------------------+--------------------------------------------------------------
id | integer | not null default nextval('regression_runs_id_seq'::regclass)
username | character varying(16) | not null
nightly_run_id | integer |
nightly_run_pid | integer |
passes | integer | not null default 0
failures | integer | not null default 0
errors | integer | not null default 0
skips | integer | not null default 0
Indexes:
"regression_runs_pkey" PRIMARY KEY, btree (id)
"regression_runs_nightly_run_id_idx" btree (nightly_run_id)
Foreign-key constraints:
"regression_runs_nightly_run_id_fkey" FOREIGN KEY (nightly_run_id) REFERENCES nightly_runs(id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
regression_run_update_trigger AFTER INSERT OR DELETE OR UPDATE ON regression_runs FOR EACH ROW EXECUTE PROCEDURE regression_run_update()
The nightly_runs table looks like this:
regression=> \d nightly_runs
Table "public.nightly_runs"
Column | Type | Modifiers
------------+--------------------------+-----------------------------------------------------------
id | integer | not null default nextval('nightly_runs_id_seq'::regclass)
passes | integer | not null default 0
failures | integer | not null default 0
errors | integer | not null default 0
skips | integer | not null default 0
Indexes:
"nightly_runs_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "regression_runs" CONSTRAINT "regression_runs_nightly_run_id_fkey" FOREIGN KEY (nightly_run_id) REFERENCES nightly_runs(id) ON UPDATE CASCADE ON DELETE CASCADE
The trigger function regression_run_update is this:
CREATE OR REPLACE FUNCTION regression_run_update() RETURNS "trigger"
AS $$
BEGIN
IF TG_OP = 'UPDATE' THEN
IF (NEW.nightly_run_id IS NOT NULL) and (NEW.nightly_run_id = OLD.nightly_run_id) THEN
UPDATE nightly_runs SET passes = passes + (NEW.passes - OLD.passes), failures = failures + (NEW.failures - OLD.failures), errors = errors + (NEW.errors - OLD.errors), skips = skips + (NEW.skips - OLD.skips) WHERE id = NEW.nightly_run_id;
ELSE
IF NEW.nightly_run_id IS NOT NULL THEN
UPDATE nightly_runs SET passes = passes + NEW.passes, failures = failures + NEW.failures, errors = errors + NEW.errors, skips = skips + NEW.skips WHERE id = NEW.nightly_run_id;
END IF;
IF OLD.nightly_run_id IS NOT NULL THEN
UPDATE nightly_runs SET passes = passes - OLD.passes, failures = failures - OLD.failures, errors = errors - OLD.errors, skips = skips - OLD.skips WHERE id = OLD.nightly_run_id;
END IF;
END IF;
ELSIF TG_OP = 'INSERT' THEN
IF NEW.nightly_run_id IS NOT NULL THEN
UPDATE nightly_runs SET passes = passes + NEW.passes, failures = failures + NEW.failures, errors = errors + NEW.errors, skips = skips + NEW.skips WHERE id = NEW.nightly_run_id;
END IF;
ELSIF TG_OP = 'DELETE' THEN
IF OLD.nightly_run_id IS NOT NULL THEN
UPDATE nightly_runs SET passes = passes - OLD.passes, failures = failures - OLD.failures, errors = errors - OLD.errors, skips = skips - OLD.skips WHERE id = OLD.nightly_run_id;
END IF;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
What I see in the postgres log file is this:
ERROR: deadlock detected
DETAIL: Process 20266 waits for ShareLock on transaction 7520; blocked by process 20263.
Process 20263 waits for ExclusiveLock on tuple (1,70) of relation 18469 of database 18354; blocked by process 20266.
Process 20266: insert into regression_runs (username, nightly_run_id, nightly_run_pid) values ('tbeadle', 135, 20262);
Process 20263: insert into regression_runs (username, nightly_run_id, nightly_run_pid) values ('tbeadle', 135, 20260);
HINT: See server log for query details.
CONTEXT: SQL statement "UPDATE nightly_runs SET passes = passes + NEW.passes, failures = failures + NEW.failures, errors = errors + NEW.errors, skips = skips + NEW.skips WHERE id = NEW.nightly_run_id"
PL/pgSQL function regression_run_update() line 16 at SQL statement
STATEMENT: insert into regression_runs (username, nightly_run_id, nightly_run_pid) values ('tbeadle', 135, 20262);
I can reproduce the problem with this script:
#!/usr/bin/env python
import os
import multiprocessing
import psycopg2
class Foo(object):
def child(self):
pid = os.getpid()
conn = psycopg2.connect(
'dbname=regression host=localhost user=regression')
cur = conn.cursor()
for i in xrange(100):
cur.execute(
"insert into regression_runs "
"(username, nightly_run_id, nightly_run_pid) "
"values "
"('tbeadle', %s, %s);", (self.nid, pid))
conn.commit()
return
def start(self):
conn = psycopg2.connect(
'dbname=regression host=localhost user=regression')
cur = conn.cursor()
cur.execute('insert into nightly_runs default values returning id;')
row = cur.fetchone()
conn.commit()
self.nid = row[0]
procs = []
for child in xrange(5):
procs.append(multiprocessing.Process(target=self.child))
for proc in procs:
proc.start()
for proc in procs:
proc.join()
Foo().start()
I can not figure out why the deadlock is happening or what I can do about it. Please help!