Hi,
Following code demonstrates an attempt to circumvent an unexpected
deadlock (cf. BUG #15026) when using exclusion constraints.
CREATE TABLE locked
(
key text NOT NULL,
EXCLUDE USING gist (key WITH =)
);
DO $$
DECLARE
id locked.key%TYPE;
BEGIN
WHILE id IS NULL
LOOP
BEGIN
SELECT key FROM locked WHERE key = 'a' INTO STRICT id;
EXCEPTION
WHEN no_data_found THEN
BEGIN
INSERT INTO locked(key) values('a') RETURNING key INTO id;
EXCEPTION
WHEN deadlock_detected THEN
RAISE WARNING 'Deadlock!! (t=%)', clock_timestamp();
END;
END;
END LOOP;
END;
$$;
This (imperfect) code works fine when two sesions compete for the record,
but will NEVER finish if three sessions compete!
To demonstrate this, first run following code from a 4th psql session:
DROP TABLE IF EXISTS locked;
CREATE TABLE locked
(
key text NOT NULL,
EXCLUDE USING gist (key WITH =)
);
BEGIN;
INSERT INTO locked(key) values('a');
Then run the DO block above from three sessions. They will wait on a
(share) lock as expected.
Now performing a ROLLBACK in the 4th session, results in a never ending
sequence of dead-lock exceptions plus retries in the other sessions.
Only "solution" I can think of is to introduce an exclusive lock on some
other entity just before doing the INSERT.
Questions:
- Anyone better ideas?
- What happens within Postgresql (special to Exclusion constraints) that
causes this deadlock and why is it (too) hard to fix it within the
backend?
Please check my two posts in pgsql-bugs on "BUG #15026: Deadlock using
GIST index" for more info including a comment from backend source code
where the dead-lock is predicted, but i.m.o underestimated.
Regards,
Mark
