Transactions, Isolation and Locking in PostgreSQL: Serializable SSI, SELECT FOR UPDATE SKIP LOCKED and Advisory Locks

Version note: The examples in this post were run with PostgreSQL 18.7 (the official postgres:18 Docker image), .NET 10, Npgsql 10.0.3 and Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3, in the Bookstore lab we set up in post 02, right after reloading the schema.

Guess First

At Bookstore, the online bookstore used as the shared example across this site's series, two warehouse clerks correct the stock of the same book at the same time. The application reads the stock, computes the new value in C# and writes it back. To prevent lost updates, someone on the team set the isolation level to REPEATABLE READ. Two sessions, A and B, run this at the same time; A commits before B:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT quantity_on_hand FROM stock_items WHERE book_id = 1;     /* both see 36 */
UPDATE stock_items SET quantity_on_hand = 35 WHERE book_id = 1;
COMMIT;

In SQL Server, REPEATABLE READ keeps a shared lock on the row it read until the end of the transaction; both sessions wait for the other's lock to update, and one becomes a deadlock victim. What happens to B's UPDATE in PostgreSQL? And what if we run the same test with the default READ COMMITTED?

/* B, REPEATABLE READ */
UPDATE stock_items SET quantity_on_hand = 35 WHERE book_id = 1;
ERROR:  could not serialize access due to concurrent update

/* B, READ COMMITTED */
UPDATE stock_items SET quantity_on_hand = 35 WHERE book_id = 1;
UPDATE 1
COMMIT

Nobody waited and there was no deadlock. Under REPEATABLE READ, B got an error (SQLSTATE 40001) because it tried to update a row another transaction had changed after B's snapshot. Under READ COMMITTED both UPDATEs succeeded, and the stock stayed at 35 when it should be 34: a classic lost update. In this post we look at PostgreSQL's isolation levels next to their SQL Server counterparts, row lock modes, a job queue in the database with SKIP LOCKED, advisory locks, deadlocks and timeouts, and retrying 40001 errors on the .NET side.

Isolation Levels: Compared with SQL Server

Because of MVCC, which we saw in post 08, a reader in PostgreSQL never waits for a writer at any isolation level. The difference is when the snapshot is taken and how conflicts are handled. Compared with SQL Server, including RCSI, its mode where readers see the last committed version:

Compared with the isolation level post of the SQL Server Derinlemesine series: PostgreSQL's READ COMMITTED corresponds to RCSI, its REPEATABLE READ to SQL Server's SNAPSHOT, and its SERIALIZABLE to a SNAPSHOT that works with conflict detection instead of locks. That's why code using REPEATABLE READ and SERIALIZABLE in PostgreSQL has to catch the 40001 error and run the transaction again from the start.

The simplest way to prevent the lost update under READ COMMITTED is to leave the arithmetic to the database: SET quantity_on_hand = quantity_on_hand - 1. On the second UPDATE, PostgreSQL rereads the latest committed version of the row and writes 34. If the calculation really has to happen in the application, either lock the row with SELECT ... FOR UPDATE or use the optimistic check with the xmin concurrency token we'll see in post 12.

SERIALIZABLE and SSI: Write Skew and 40001

There's an anomaly REPEATABLE READ doesn't catch: write skew, where two transactions read the same data and write different rows. Bookstore's rule: a customer may have at most one Pending order at a time. The code checks first and then inserts. Two requests arrive at the same moment; TxDemo.cs in the snapshot waits until both requests have finished the check before moving on to the insert:

await using NpgsqlTransaction transaction = await connection.BeginTransactionAsync(isolation);

await using NpgsqlCommand check = new(
    "SELECT count(*) FROM orders WHERE customer_id = @customerId AND status = 'Pending'", connection, transaction);
check.Parameters.AddWithValue("customerId", CustomerId);
long pending = (long)(await check.ExecuteScalarAsync())!;

if (attempt == 1)
{
    bothChecked.SignalAndWait(); // both requests have checked, now they insert
}
if (pending > 0)
{
    await transaction.CommitAsync();
    return $"{name}: saw {pending} pending order, nothing inserted";
}
/* INSERT INTO orders ... 'Pending' ... ; COMMIT */

$env:BOOKSTORE_PG = "Host=localhost;Port=5432;Database=bookstore;Username=bookstore_app;Password=<app-password>"
docker exec -it bookstore-pg psql -U bookstore_owner -d bookstore -f /lab/09-transactions.sql
dotnet run --project src/Bookstore.PgLab -- write-skew

-- READ COMMITTED
A: saw 0 pending orders, inserted BK-TA1
B: saw 0 pending orders, inserted BK-TB1
pending orders for customer 1234: 2
-- SERIALIZABLE
A: saw 0 pending orders, inserted BK-TA1
B: 40001 could not serialize access due to read/write dependencies among transactions
pending orders for customer 1234: 1
-- SERIALIZABLE with retry
A: attempt 1 failed with 40001, retrying
A: saw 1 pending order, nothing inserted
B: saw 0 pending orders, inserted BK-TB1
pending orders for customer 1234: 1

Under READ COMMITTED the rule was broken: two pending orders. REPEATABLE READ would give the same result, because the two transactions inserted different rows; there's no shared row that both updated. Under SERIALIZABLE, PostgreSQL noticed that each transaction changed data the other had read, and stopped one of them with 40001. SSI, Serializable Snapshot Isolation, does this with predicate locks that don't make anyone wait; they only detect the conflict. In the last block the request retried, saw the pending order and didn't insert. The correct result only comes together with the retry. The index on customer_id matters too: without it the predicate lock is taken at table level, and requests for unrelated customers start knocking each other out with 40001.

Row Lock Modes: FOR UPDATE, FOR NO KEY UPDATE and Foreign Keys

PostgreSQL has four row lock modes; from strongest to weakest, FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE. An UPDATE that doesn't change key columns takes FOR NO KEY UPDATE; a foreign key check takes FOR KEY SHARE on the parent row. These two don't conflict. The SELECT ... FOR UPDATE that applications often write, however, does. Session A locks a book row to update its price, and session B adds an order item for that book:

/* A */
BEGIN;
SELECT book_id, price FROM books WHERE book_id = 1 FOR UPDATE;

/* B */
SET lock_timeout = '1s';
BEGIN;
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES (1, 1, 1, 10.00);

ERROR:  canceling statement due to lock timeout
CONTEXT:  while locking tuple (0,1) in relation "books"
SQL statement "SELECT 1 FROM ONLY "public"."books" x WHERE "book_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

The CONTEXT line shows plainly what the foreign key check does. When A locked the same row with FOR NO KEY UPDATE, B's INSERT went through without waiting. The rule: if you lock a row to update it and won't change its primary key, write FOR NO KEY UPDATE. Otherwise a price update on a busy catalog holds up every order for the same book.

A Job Queue in the Database with SKIP LOCKED

In the outbox post of the Message Queue'larla Çalışmak series, we first wrote messages into a table in the database and then sent them to the broker with a relay. Once the relay runs on more than one instance, the question is: no two workers should take the same message, but they shouldn't wait for each other either. 09-transactions.sql creates an outbox_messages table with 1000 messages; each worker takes a batch of 10 with this query, does 20 ms of "sending" and commits after marking the batch processed:

SELECT outbox_message_id FROM outbox_messages
WHERE processed_at IS NULL
ORDER BY outbox_message_id
LIMIT 10
FOR UPDATE SKIP LOCKED

-- FOR UPDATE
worker-1: 200 messages
worker-2: 0 messages
worker-3: 330 messages
worker-4: 470 messages
total: 1000, duplicates: 0, elapsed: 2.4 s
-- FOR UPDATE SKIP LOCKED
worker-1: 250 messages
worker-2: 250 messages
worker-3: 250 messages
worker-4: 250 messages
total: 1000, duplicates: 0, elapsed: 0.6 s

Without SKIP LOCKED, all four workers asked for the same first 10 rows and queued up. When the lock was released, the waiting worker saw that the rows no longer matched the condition (READ COMMITTED rechecks the row) and returned an empty result. The code took that as "queue empty", so worker-2 exited without processing a single message; the work effectively ran in sequence. With SKIP LOCKED each worker skipped the locked rows and took the next free batch: an even split, a quarter of the time, no duplicates. The partial index (WHERE processed_at IS NULL) limits the scan to pending messages. Don't forget to delete or archive processed messages regularly; this heavily updated table is exactly what the autovacuum settings from post 08 are for.

Advisory Locks: Work That Must Run on One Instance

The nightly stock sync is scheduled on all three API instances, but only one may run at a time. An advisory lock is a lock on a number whose meaning the application decides; it has nothing to do with any table. We covered distributed locks from the job scheduler's point of view in the multi-instance post of the Background Job'lar ve Scheduled Job'lar series:

SELECT pg_try_advisory_xact_lock(hashtext('nightly-stock-sync'))

instance-1: another instance is running, skipped
instance-2: ran the sync
instance-3: another instance is running, skipped

The _xact_ variant releases the lock on its own when the transaction ends; if the connection drops, the lock goes too, so no orphaned lock is left behind. The session-level pg_advisory_lock stays until it's released by hand or the connection closes; it needs care together with a connection pool. The try variant doesn't wait and returns false if it can't get the lock; that's the right choice for skipping a job.

Deadlocks, Timeouts and the DDL Queue

When two transactions update stock rows in opposite order:

ERROR:  deadlock detected
DETAIL:  Process 909 waits for ShareLock on transaction 1404; blocked by process 917.
Process 917 waits for ShareLock on transaction 1403; blocked by process 909.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "stock_items"
COMMIT;
ROLLBACK

PostgreSQL checks for a deadlock after waiting deadlock_timeout (1 second by default) for a lock, and cancels one of the transactions in the cycle; the text of both queries is in the server log. Note the last two lines: in a transaction that got an error, COMMIT ends up as ROLLBACK. The fix is the same as in SQL Server: lock rows in the same order everywhere (for example by book_id); the patterns from the deadlock post of the SQL Server Derinlemesine series apply here too.

Four settings limit waiting: lock_timeout for waiting on a lock, statement_timeout for a single statement, idle_in_transaction_session_timeout for sitting idle inside a transaction (the cure for the session that stopped VACUUM in post 08), and transaction_timeout, added in 17, for the total length of a transaction. All are off by default; setting them per role or database with ALTER ROLE bookstore_app SET statement_timeout = '30s' is a good start.

DDL is transactional in PostgreSQL too: if an ALTER TABLE in a migration fails, the whole change is rolled back. But most ALTER TABLE forms need an ACCESS EXCLUSIVE lock, and that has a side effect. A runs a long report, B adds a column in a migration, and C does an ordinary read:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, wait_event_type, left(query, 45) AS query
FROM pg_stat_activity
WHERE datname = 'bookstore' AND pid <> pg_backend_pid() AND backend_type = 'client backend'
ORDER BY backend_start;

 pid | blocked_by | state  | wait_event_type |                   query
-----+------------+--------+-----------------+--------------------------------------------
 852 | {}         | active | Timeout         | SELECT pg_sleep(4);
 867 | {852}      | active | Lock            | ALTER TABLE books ADD COLUMN subtitle text
 881 | {867}      | active | Lock            | SELECT title FROM books WHERE book_id = 2

C's read doesn't conflict with A, but it got in line behind the ALTER TABLE waiting ahead of it. As long as the report runs, the migration waits, and as long as the migration waits, every query on the table waits; that's how a short outage starts in production. The fix is to set SET lock_timeout = '3s' in the migration session and, if the lock can't be taken, retry the migration after a short pause.

The .NET Side: Retrying on SqlState and the EF Core Execution Strategy

Npgsql throws server errors as PostgresException; SqlState tells you which error it is. The codes to retry are 40001 (PostgresErrorCodes.SerializationFailure) and 40P01 (DeadlockDetected). The retry in the write skew example above is exactly that:

catch (PostgresException ex) when (retry && ex.SqlState == PostgresErrorCodes.SerializationFailure && attempt < 5)
{
    Console.WriteLine($"{name}: attempt {attempt} failed with {ex.SqlState}, retrying");
    await Task.Delay(Random.Shared.Next(10, 50) * attempt);
}

What gets retried is not one command but the whole transaction: the read has to be repeated too, so the second attempt decides with current data. Adding randomness to the delay keeps requests that failed together from colliding together again.

In EF Core, EnableRetryOnFailure does this with an execution strategy. The Npgsql provider treats errors where PostgresException.IsTransient is true as transient; 40001 and 40P01 are on that list. But a strategy can only repeat work it runs itself:

DbContextOptions<ReportingContext> options = new DbContextOptionsBuilder<ReportingContext>()
    .UseNpgsql(connectionString, npgsql => npgsql.EnableRetryOnFailure(maxRetryCount: 3))
    .Options;

// the whole unit of work goes into the strategy, so a retry starts the transaction again
IExecutionStrategy strategy = db.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
    attempts++;
    await using IDbContextTransaction transaction = await db.Database.BeginTransactionAsync(IsolationLevel.Serializable);
    await db.Database.ExecuteSqlAsync(
        $"UPDATE stock_items SET quantity_reserved = quantity_reserved + 1 WHERE book_id = {1}");
    /* the first attempt simulates a 40001 */
    await transaction.CommitAsync();
});

ExecuteSql inside a user transaction: ok
The configured execution strategy 'NpgsqlRetryingExecutionStrategy' does not support user-initiated transactions. Use the execution strategy returned by 'DbContext.Database.CreateExecutionStrategy()' to execute all the operations in the transaction as a retriable unit.
committed after 2 attempt(s)

The first two lines come from an attempt where we opened BeginTransactionAsync outside the strategy and did some work: ExecuteSqlAsync passed silently, but the first EF Core query (db.Books.CountAsync()) threw. So the check isn't made for every command; you only see the error once a query runs inside the transaction. With the correct usage the first attempt got a 40001 (simulated with RAISE EXCEPTION ... USING ERRCODE = 'serialization_failure'), the strategy ran the transaction again from the start, and the stock went up once. When you call SaveChanges on its own, the strategy already wraps it; if you open a separate transaction, this structure is required.

Production Notes

Turn on log_lock_waits = on to see lock waits: every lock that waits longer than deadlock_timeout is written to the log. For the current state, the wait_event_type = 'Lock' rows in pg_stat_activity and pg_blocking_pids() are enough.

If you choose SERIALIZABLE, use it in every transaction that writes; a transaction writing the same data under READ COMMITTED stays outside SSI's protection. Track the 40001 rate as a metric; if it rises, transactions are too long or needed indexes are missing.

Give the application role statement_timeout and idle_in_transaction_session_timeout, and the migration session lock_timeout. Run long reports with a separate role and, if needed, on a read replica (the Veritabanı Performansı ve Scaling series).

Common Pitfalls

Calling an external service inside a transaction: if the payment service takes 30 seconds to answer, the row locks are held for 30 seconds and, as we saw in post 08, VACUUM stops. Move the external call outside the transaction.

Logging 40001 as an "unexpected error" and returning a 500 to the user: under REPEATABLE READ and SERIALIZABLE this error is a normal part of the work; they can't be used without a retry.

Mixing up NOWAIT and SKIP LOCKED: NOWAIT throws an error (55P03) when it sees a locked row, SKIP LOCKED skips the row. Use the second for queues, the first for a "someone else is editing this record right now" warning.

Running migrations without lock_timeout: with the queueing effect above, a single ALTER TABLE can stop all traffic to the table.

A Small Experiment

★☆☆ Repeat the Guess First test under READ COMMITTED with SET quantity_on_hand = quantity_on_hand - 1 and confirm the stock is 34. Did B's UPDATE wait until A committed?

★★☆ In the queue demo, raise the number of workers to 8 and set the batch size to 1. How did the time of the SKIP LOCKED version change, and why?

★★★ In the write skew demo, drop the ix_orders_customer_id index on orders and run two SERIALIZABLE transactions that open orders for two different customers at the same time. Do they get 40001 even though they are unrelated? Look at the rows with mode = 'SIReadLock' in pg_locks.

In the next post we build Bookstore's book search with PostgreSQL's full-text search: tsvector, tsquery, the Turkish configuration, unaccent and pg_trgm for typo tolerance.