Bhupendra Yadav

Postgres: SERIALIZABLE Transaction Isolation can be a FootGun

· 7 min read

In my last post on SELECT FOR UPDATE, I wrote about using row locks to prevent race conditions.

Recently, I worked on a similar problem: concurrent API calls for the same resourceID could race with each other. This time, the flow touched about 12 tables.

Trying SERIALIZABLE transaction isolation in Postgres

Using SERIALIZABLE looked simpler than figuring out which rows to lock across all 12 tables.

The Serializable isolation level provides the strictest transaction isolation. This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently.

Postgres still runs the transactions concurrently. Each transaction gets a stable view of the data, and Postgres checks how its reads and writes interact with other transactions. If it finds a possible serialization problem, it aborts a transaction. The application then needs to retry it.

This is called Serializable Snapshot Isolation, or SSI. You can start a transaction like this:

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Read the data and make changes based on it.

COMMIT;

Well it didn’t (quite) work

I wrapped the flow in a SERIALIZABLE transaction and made concurrent calls for the same resource ID locally. One succeeded and one failed, which was what I expected. I added unit tests and deployed to staging.

Then our full integration suite ran. Some tests failed with this error:

ERROR:  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.

I went through the tests and my change. Each failing test touched a separate set of rows, so I couldn’t see why they conflicted.

This made me curious & excited, I wanted to figure this out myself instead of asking Cursor to debug. So I wrote a small test that makes two concurrent calls for different resource IDs and uh huh it failed with same error.

Even more confused and excited. I started to comment out one database query at a time from code and reran the test. After some back and forth & few runs, removing one particular SELECT query made it pass.

EXPLAIN ANALYZE on the query showed that the query used a sequential scan.

At that point, I gave my observations to AI and asked for help understanding what Postgres was doing. Also did some googling and came across this article from the legendary website https://www.interdb.jp/pg/pgsql05/09.html#lock-levels-and-lock--aggregation

What the SELECT was doing

A SERIALIZABLE transaction takes SIReadLock. SIREAD locks have three levels: tuple, page, and relation(full table).

When using a sequential scan, PostgreSQL creates a relation-level SIREAD lock from the beginning, regardless of indexes or WHERE clauses. In certain situations, this implementation can cause false-positive detections of serialization anomalies.

To see it, I kept the transaction open after the SELECT and ran this query in another terminal:

SELECT
    pid,
    relation::regclass AS relation,
    locktype,
    page,
    tuple,
    mode
FROM pg_locks
WHERE mode = 'SIReadLock'
ORDER BY pid, relation::regclass::text, locktype, page, tuple;

       relation        | locktype | page | tuple |    mode
-----------------------+----------+------+-------+------------
 accounts | relation |      |       | SIReadLock

The sequential scan left this predicate lock, locktype = relation means the lock covers the table. Postgres uses it to track writes that might conflict with the transaction’s read.

The word “lock” confused me. I expected it to make UPDATE or INSERT wait, but it records a read for SSI without blocking those operations. The Postgres documentation explains that these locks do not cause blocking.

That means a write can finish and the transaction can still fail later, even at COMMIT. I tried this with two terminals.

Let’s repro

I used PostgreSQL 17.11. The table has a primary-key index on id, but no index on name. Here’s the setup for a fresh test database:

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL
);

INSERT INTO accounts(id, name, balance)
SELECT i, 'user-' || i,
       CASE i WHEN 1 THEN 1100 WHEN 2 THEN 321 ELSE 1000 END
FROM generate_series(1, 100) AS g(i);

ANALYZE accounts;

The SELECT only asks for one account:

EXPLAIN (COSTS OFF)
SELECT * FROM accounts WHERE name = 'user-1';
 Seq Scan on accounts
   Filter: (name = 'user-1'::text)

It returns one row, but Postgres scans the table to find it. I didn’t change any planner settings for this test.

In Session 1, start a transaction and read user-1:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE name = 'user-1';
 id |  name  | balance
----+--------+---------
  1 | user-1 |    1100
(1 row)

Leave that transaction open. In Session 2, read user-2:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE name = 'user-2';
 id |  name  | balance
----+--------+---------
  2 | user-2 |     321
(1 row)

Now run the UPDATE in Session 1, without committing yet:

UPDATE accounts SET balance = 111 WHERE id = 1;
-- UPDATE 1

Then run the UPDATE in Session 2:

UPDATE accounts SET balance = 123 WHERE id = 2;
-- UPDATE 1

Both updates finish without waiting. At this point both transactions have a relation-level SIReadLock on accounts. I also checked pg_blocking_pids(pid); both sessions returned {}.

Commit Session 1 first, then Session 2:

-- Session 1
exp=*> COMMIT;
COMMIT

-- Session 2
exp=*> COMMIT;
ERROR:  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.

The order matters. Both SELECTs must run before either UPDATE for this example, and both UPDATEs run before the first COMMIT. Running one whole transaction and then the other won’t reproduce this failure.

This replay uses the SQL and results from my local test, reformatted for readability:

Each transaction only asked for its own account and updated that same account. Neither changed the other’s name or balance. At the SQL level, these transactions could run in either order and return the same results.

But both SELECTs used sequential scans. Each transaction got a predicate lock covering the whole table. With that coarse tracking, each transaction’s write counted as a conflict with the other’s read. Postgres detected opposing read/write dependencies and rejected one commit.

This is a false positive caused by the broad predicate locks. That’s what made it relevant to my bug: the requests worked on different resources, but the query plan made their reads cover the same table for SSI tracking.

What if the SELECT uses an index?

For comparison, I also checked a lookup by primary key. This uses a different filter, so it only shows how the lock types change:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SET LOCAL enable_seqscan = off;

SELECT * FROM accounts WHERE id = 1;

I used enable_seqscan = off just for this test to encourage an index scan on the small table. With the transaction still open, I checked pg_locks from another session using the earlier query:

          relation          | locktype | page | tuple |    mode
----------------------------+----------+------+-------+------------
 accounts_pkey  | page     |    1 |       | SIReadLock
 accounts       | tuple    |    0 |   101 | SIReadLock

This time, Postgres tracked the read with a lock on an index page and a lock on the matching row (tuple). There was no whole-table SIReadLock. The page and tuple numbers may differ in your run.

The index lets Postgres track a smaller part of the table, reducing the chance of conflicts with writes to unrelated rows.

For the original query, the useful index is on name, not just id:

CREATE INDEX accounts_name_idx ON accounts(name);
ANALYZE accounts;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM accounts WHERE name = 'user-1';

The primary-key index was already there when the test failed. It couldn’t help Postgres find a row by name.

An index doesn’t guarantee an index scan

Postgres may still choose a sequential scan if the table is small or the query needs many rows. On a table with only 100 rows, reading the table can be cheaper than using the index. Adding an index alone doesn’t prove that this example is fixed; check the actual plan with EXPLAIN ANALYZE and inspect the locks again.

Reducing the chance of conflicts

Keep the reads inside a SERIALIZABLE transaction as narrow as the work allows. If you only need one resource, filter for that resource and make sure it doesn’t scan lots of rows. Narrower reads reduce unnecessary conflicts; they can’t prevent every serialization failure. Handle SQLSTATE 40001 by retrying the whole transaction, including its reads, with a fresh snapshot.

References:

This was a very fun finding for me.

Until next time, Happy Debugging :)

← All posts