Forked from nathanl/postgresql_serializable_isolation.sql
Created
July 15, 2022 14:11
-
-
Save inoas/9fbd012e577fab9876889886400c2224 to your computer and use it in GitHub Desktop.
PostgreSQL Serializable Isolation - false positives
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- (This code was run in PostgreSQL 9.6.1) | |
-- Demonstration of how serializable isolation for PostgreSQL, which detects possible | |
-- interference between concurrent transactions, can produce false positives | |
-- in psql, create the following table | |
CREATE TABLE users( | |
id SERIAL NOT NULL PRIMARY KEY, | |
username VARCHAR NOT NULL | |
); | |
-- open a second psql session | |
-- in session 1, type this | |
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; | |
SELECT * FROM users WHERE username = 'alice'; | |
INSERT INTO users ("username") VALUES ('alice'); | |
-- don't type `END;` just yet; we want a concurrent transaction | |
--- in session 2, type this | |
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; | |
SELECT * FROM users WHERE username = 'bob'; | |
INSERT INTO users ("username") VALUES ('bob'); | |
-- now type `END;` in each of the sessions. | |
-- whichever one you end second will give you | |
-- this error: | |
--- ERROR: 40001: could not serialize access due to read/write dependencies among transactions | |
--- DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt. | |
--- HINT: The transaction might succeed if retried. | |
--- LOCATION: PreCommit_CheckForSerializationFailure, predicate.c:4659 | |
-- PostgreSQL is saying "I think the second one to end would have gotten | |
-- a different result if the first one had been complete before it started" | |
-- In this case, it wouldn't have; serializable isolation is giving is a | |
-- false positive for interference between concurrent transactions |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment