| 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; |
