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;
