deadlock problem

From: Sebastian Böhm <seb(at)exse(dot)net>
To: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: deadlock problem
Date: 2011-05-30 14:04:49
Message-ID: C9DC3D07-1238-4908-9B81-4DF837BE0C3A@exse.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I need a little help with a deadlock.

when I execute this (end of the mail) function in parallel sometimes a deadlock happens.

This function does implement a insert or update functionality.

The error is:
"DETAIL: Process 29464 waits for ShareLock on transaction 1293098; blocked by process 29463.
Process 29463 waits for ShareRowExclusiveLock on relation 16585 of database 16384; blocked by process 29464."

From the postgres documentation:
"SHARE
Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes.

Acquired by CREATE INDEX (without CONCURRENTLY).

"

so where the "ShareLock" is acquired? I don't create an index here.

TThe cause of the lock itself is clear to me, but I don't know where the "ShareLock" was acquired.

Kind Regards
Sebastian Boehm

-------------------------------------------

CREATE FUNCTION acount(count_in integer) RETURNS integer
AS $$

DECLARE day_now timestamp with time zone;
DECLARE ii int;
DECLARE jj int;

BEGIN

SELECT date_trunc('day',now() at TIME ZONE 'America/Los_Angeles') at time zone 'America/Los_Angeles' INTO day_now;

SELECT count FROM summary
WHERE
day = day_now AND
INTO ii;

IF (ii IS NULL) THEN

LOCK table summary IN SHARE ROW EXCLUSIVE MODE;

SELECT count FROM summary
WHERE
day = day_now AND
INTO jj;

IF (jj IS NULL) THEN

INSERT INTO summary (day,count) VALUES (day_now,count_in);

ELSE

UPDATE summary SET count = count + count_in WHERE day = day_now;

END IF;

ELSE

UPDATE summary SET count = count + count_in WHERE day = day_now

END IF;

RETURN ii;

END;

$$
LANGUAGE plpgsql;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mathew Samuel 2011-05-30 14:29:02 UTC4115FATAL: the database system is in recovery mode
Previous Message Nicholson, Brad (Toronto, ON, CA) 2011-05-30 13:08:03 Re: Inspecting a DB - psql or system tables ?