Learning Hub

How to Stop Lost Updates and Write Skew in PostgreSQL With Isolation Levels and Retries in Python

Reproduce lost updates and write skew with two live PostgreSQL sessions, then fix them with atomic updates, row locks, an exclusion constraint, SERIALIZABLE transactions and a tested Python retry...

October 18, 2026 44 Min Read
32

Two requests reach your server at almost the same moment. Each one reads a row, decides what to do, and writes its answer. Your tests probably pass, because tests usually run one request at a time. Then a customer’s deposit vanishes, or two doctors both leave a hospital ward and nobody is on call. These are concurrency bugs, and they only exist in the overlap between two transactions.

Table Of Content

In this tutorial you will make those bugs happen on purpose, using two live PostgreSQL sessions that you step through by hand, and then fix each one with the right tool. You will reproduce a lost update and write skew, fix the first three different ways, fix the second with the SERIALIZABLE isolation level plus a retry loop, find out why SERIALIZABLE sometimes cancels transactions that never touched the same row, let an exclusion constraint do the checking for you, use row locks without causing deadlocks, and finish with a 12-test suite that fails when you break the retry loop. Most scripts print a psql-style transcript, so you can see the exact order in which the statements ran.

Four words you need first

A transaction is a group of SQL statements that PostgreSQL treats as one unit: all of them take effect, or none of them do. A session is one database connection, so two sessions are two clients working at the same time. Isolation is the promise about how much of another transaction’s work your transaction can see while both are running, and an isolation level is how strong that promise is. A serialization failure is PostgreSQL telling you that it cannot keep its promise for your transaction in this particular overlap, so you must run the whole transaction again.

The reason this matters is that stronger isolation costs something: waiting, or aborted transactions that you have to retry. The skill you are learning is choosing the cheapest tool that is still correct for each rule your application has to keep.

Prerequisites

You need a PostgreSQL server where you can create a database and run CREATE EXTENSION btree_gist (Step 8 uses it). I ran everything against PostgreSQL 18.6 on Windows 11 as the postgres superuser, with Python 3.13.14, psycopg 3.3.6 and pytest 9.1.1. I only tested that combination. If you do not have a server yet, the PostgreSQL download page lists installers and packages for each operating system. You should be comfortable reading basic SQL and Python functions; no earlier knowledge of transactions is assumed.

Create a folder for the lab, make a virtual environment, and install the two packages:

python -m pip install "psycopg[binary]" pytest

The scripts connect with the short connection strings dbname=isolab and dbname=postgres, so the host, port and user come from the standard libpq environment variables. Set them in the shell where you run the scripts. On my machine the server listens on port 55432; use your own port, and add PGPASSWORD if your server asks for a password:

export PGHOST=localhost PGPORT=5432 PGUSER=postgres
$env:PGHOST = "localhost"; $env:PGPORT = "5432"; $env:PGUSER = "postgres"

Put each file from the steps below into that folder under the name shown in its first comment line. In the end you will have this layout, and you will run each stepNN script with python in numbered order:

lab.py                  shared helpers (Step 1)
retry.py                the retry loop (Step 6)
workload.py             concurrent requests that race on purpose (Steps 6 and 9)
step01_levels.py ... step10_break_it.py
test_isolation.py       the test suite (Step 10)

Step 1: Build the lab helpers and meet the isolation levels

Everything in this tutorial depends on controlling the order in which two sessions run their statements, so we start with a small helper module. Read it once now; you will see it used in every later step.

# lab.py
"""lab.py: shared helpers. A Session prints a psql-style transcript of every statement it runs."""
import threading

import psycopg

# libpq reads PGHOST, PGPORT, PGUSER and PGPASSWORD from the environment, so these two stay short.
ADMIN_DSN = "dbname=postgres"
DSN = "dbname=isolab"


def say(text=""):
    print(text, flush=True)


def create_database():
    with psycopg.connect(ADMIN_DSN, autocommit=True) as conn:
        if not conn.execute("SELECT 1 FROM pg_database WHERE datname = 'isolab'").fetchone():
            conn.execute("CREATE DATABASE isolab")


def connect():
    """Return an autocommit connection. psycopg then sends no hidden BEGIN, so every BEGIN and
    COMMIT in a transcript is one we wrote, exactly as in psql."""
    conn = psycopg.connect(DSN, autocommit=True)
    conn.execute("SET lock_timeout = '10s'")  # a demo that waits forever is a bug: make it fail
    return conn


def sql(text):
    """Run quiet setup SQL on a throwaway connection."""
    with connect() as conn:
        conn.execute(text)


def report(cursor):
    if cursor.description is None:
        say(f"   {cursor.statusmessage}")
        return cursor.statusmessage
    rows = cursor.fetchall()
    say(f"   {rows}" if rows else "   (no rows)")
    return rows


def report_error(exc):
    say(f"   ERROR {type(exc).__name__} (SQLSTATE {exc.sqlstate}): {exc.diag.message_primary}")
    for label, text in (("DETAIL", exc.diag.message_detail), ("HINT", exc.diag.message_hint)):
        if text:
            say(f"   {label}: {text}")
    return exc


class Session:
    """A named session. Calling it prints the statement, runs it and prints the outcome."""

    def __init__(self, name):
        self.name = name
        self.conn = connect()

    def announce(self, statement, params):
        text = " ".join(statement.split())
        say(f"{self.name}> {text}" + (f"    -- {params}" if params else ""))

    def __call__(self, statement, params=None):
        self.announce(statement, params)
        try:
            return report(self.conn.execute(statement, params))
        except psycopg.Error as exc:
            return report_error(exc)

    def later(self, statement, params=None):
        """Start a statement that is going to wait for a lock; call finish() on the result."""
        return Later(self, statement, params)


class Later:
    """One statement running on a background thread because it has to wait for another session."""

    def __init__(self, session, statement, params):
        self.session = session
        session.announce(statement, params)
        self.result = None
        self.thread = threading.Thread(target=self.run, args=(statement, params))
        self.thread.start()
        self.thread.join(0.7)
        if self.thread.is_alive():
            say("   ... waiting for a lock held by another session")

    def run(self, statement, params):
        try:
            cursor = self.session.conn.execute(statement, params)
            self.result = cursor.fetchall() if cursor.description else cursor.statusmessage
        except psycopg.Error as exc:
            self.result = exc

    def finish(self):
        self.thread.join(15)
        say(f"{self.session.name}> (the waiting statement finishes)")
        if isinstance(self.result, psycopg.Error):
            return report_error(self.result)
        say(f"   {self.result}" if self.result else "   (no rows)")
        return self.result

Here is what the pieces do. connect() opens a connection in autocommit mode. Normally psycopg quietly sends a BEGIN before your first statement; in autocommit mode it does not, so every BEGIN and COMMIT you see in a transcript is one we wrote ourselves, exactly as in the psql command line tool. It also sets lock_timeout to 10 seconds so that a mistake in a demo fails with an error instead of waiting forever.

A Session is a named connection. Calling it prints the statement, runs it, and prints either the rows, the command status such as UPDATE 1, or the error with its SQLSTATE (a five-character error code) and any DETAIL and HINT lines. The Later class handles a statement that is going to wait for another session’s lock: it runs the statement on a background thread, waits 0.7 seconds, and reports that the statement is still waiting; finish() collects the result after the other session has committed. That lets one script play two sessions without hanging.

Run the first script

The first script creates the lab database and an accounts table, then asks PostgreSQL about its isolation levels:

# step01_levels.py
"""step01_levels.py: meet the isolation levels PostgreSQL really has."""
import lab
from lab import Session, say

lab.create_database()
lab.sql("""
    DROP TABLE IF EXISTS accounts;
    CREATE TABLE accounts (id int PRIMARY KEY, owner text NOT NULL, balance int NOT NULL);
    INSERT INTO accounts VALUES (1, 'alice', 100);
""")

a, b = Session("A"), Session("B")
a("SHOW server_version")
a("SHOW default_transaction_isolation")

say()
say("-- Ask for the weakest level the SQL standard defines, and try to read uncommitted data:")
a("BEGIN")
a("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
a("SHOW transaction_isolation")
a("UPDATE accounts SET balance = 999 WHERE id = 1")
b("BEGIN ISOLATION LEVEL READ UNCOMMITTED")
b("SELECT balance FROM accounts WHERE id = 1")
b("COMMIT")
a("ROLLBACK")

say()
say("-- The level cannot be changed once a query has run in the transaction:")
b("BEGIN")
b("SELECT 1")
b("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
b("ROLLBACK")
python step01_levels.py
A> SHOW server_version
   [('18.6',)]
A> SHOW default_transaction_isolation
   [('read committed',)]

-- Ask for the weakest level the SQL standard defines, and try to read uncommitted data:
A> BEGIN
   BEGIN
A> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   SET
A> SHOW transaction_isolation
   [('read uncommitted',)]
A> UPDATE accounts SET balance = 999 WHERE id = 1
   UPDATE 1
B> BEGIN ISOLATION LEVEL READ UNCOMMITTED
   BEGIN
B> SELECT balance FROM accounts WHERE id = 1
   [(100,)]
B> COMMIT
   COMMIT
A> ROLLBACK
   ROLLBACK

-- The level cannot be changed once a query has run in the transaction:
B> BEGIN
   BEGIN
B> SELECT 1
   [(1,)]
B> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   ERROR ActiveSqlTransaction (SQLSTATE 25001): SET TRANSACTION ISOLATION LEVEL must be called before any query
B> ROLLBACK
   ROLLBACK

Read the output from the top. The default level is read committed. Then session A starts a transaction, asks for READ UNCOMMITTED, and changes the balance to 999 without committing. Session B, also at READ UNCOMMITTED, still reads 100. The SQL standard allows a transaction at that level to see uncommitted data (a “dirty read”), but PostgreSQL does not give you one. The documentation says it directly: “In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e., PostgreSQL’s Read Uncommitted mode behaves like Read Committed.” So in practice you choose between READ COMMITTED, REPEATABLE READ and SERIALIZABLE, and this tutorial walks through all three.

The last part of the output is a gotcha worth remembering. Once a transaction has run a query, SET TRANSACTION ISOLATION LEVEL fails with SQLSTATE 25001. The SET TRANSACTION reference states the rule: “The transaction isolation level cannot be changed after the first query or data-modification statement (SELECT, INSERT, DELETE, UPDATE, MERGE, FETCH, or COPY) of a transaction has been executed.” Choose the level in the BEGIN statement itself.

Check before moving on: you should see read committed as the default, B reading [(100,)], and the 25001 error on the last statement. If the first line fails with a connection error, your PG* variables are not set in this shell.

Step 2: Reproduce a lost update

A classic concurrency bug is the lost update, and it comes from a pattern you have probably written yourself: read a value into your program, change it there, and write the new value back. Imagine a bank balance of 100. Request A deposits 10 and request B deposits 20, so the balance should end at 130. Each request reads 100, adds its amount in Python, and writes the result:

# step02_lost_update.py
"""step02_lost_update.py: two deposits race and one disappears (READ COMMITTED, the default)."""
import lab
from lab import Session, say

lab.sql("""
    DROP TABLE IF EXISTS accounts;
    CREATE TABLE accounts (id int PRIMARY KEY, owner text NOT NULL, balance int NOT NULL);
    INSERT INTO accounts VALUES (1, 'alice', 100);
""")

a, b = Session("A"), Session("B")
a("BEGIN")
b("BEGIN")
read_by_a = a("SELECT balance FROM accounts WHERE id = 1")[0][0]
read_by_b = b("SELECT balance FROM accounts WHERE id = 1")[0][0]
say(f"-- A deposits 10 and B deposits 20, so the balance should end at {100 + 10 + 20}")
a("UPDATE accounts SET balance = %s WHERE id = 1", (read_by_a + 10,))
a("COMMIT")
b("UPDATE accounts SET balance = %s WHERE id = 1", (read_by_b + 20,))
b("COMMIT")

say()
say("-- The balance at the end:")
Session("C")("SELECT balance FROM accounts WHERE id = 1")
python step02_lost_update.py
A> BEGIN
   BEGIN
B> BEGIN
   BEGIN
A> SELECT balance FROM accounts WHERE id = 1
   [(100,)]
B> SELECT balance FROM accounts WHERE id = 1
   [(100,)]
-- A deposits 10 and B deposits 20, so the balance should end at 130
A> UPDATE accounts SET balance = %s WHERE id = 1    -- (110,)
   UPDATE 1
A> COMMIT
   COMMIT
B> UPDATE accounts SET balance = %s WHERE id = 1    -- (120,)
   UPDATE 1
B> COMMIT
   COMMIT

-- The balance at the end:
C> SELECT balance FROM accounts WHERE id = 1
   [(120,)]

Both sessions read 100. A writes 110 and commits. B then writes 120, because B computed it from the stale 100, and the final balance is 120. Alice’s deposit of 10 is gone, no error was raised, and PostgreSQL did exactly what READ COMMITTED promises. The documentation says a plain SELECT at this level “sees only data committed before the query began”, and B’s UPDATE simply set the balance to the number it was given.

This is why such bugs survive testing. A test that sends two requests one after the other will pass; the failure needs the two SELECT statements to run before either UPDATE. In this tutorial we force that order. In production it happens rarely and unpredictably, which is worse.

Check before moving on: the last line must say [(120,)]. If you see 130, the two SELECT statements did not both run before the first UPDATE.

Step 3: Fix the lost update three ways

The next script replays the same race three times, once with each fix, and every run should end at 130. It resets the table before each fix:

# step03_fixes.py
"""step03_fixes.py: three ways to make both deposits count."""
import lab
from lab import Session, say

SETUP = """
    DROP TABLE IF EXISTS accounts;
    CREATE TABLE accounts (id int PRIMARY KEY, owner text NOT NULL, balance int NOT NULL);
    INSERT INTO accounts VALUES (1, 'alice', 100);
"""
READ = "SELECT balance FROM accounts WHERE id = 1"
WRITE = "UPDATE accounts SET balance = %s WHERE id = 1"

say("=== Fix 1: let the database do the arithmetic")
lab.sql(SETUP)
a, b = Session("A"), Session("B")
a("BEGIN")
b("BEGIN")
a("UPDATE accounts SET balance = balance + 10 WHERE id = 1")
waiting = b.later("UPDATE accounts SET balance = balance + 20 WHERE id = 1")
a("COMMIT")
waiting.finish()
b("COMMIT")
Session("C")(READ)

say()
say("=== Fix 2: lock the row while you read it")
lab.sql(SETUP)
a, b = Session("A"), Session("B")
a("BEGIN")
b("BEGIN")
read_by_a = a(READ + " FOR UPDATE")[0][0]
waiting = b.later(READ + " FOR UPDATE")
a(WRITE, (read_by_a + 10,))
a("COMMIT")
read_by_b = waiting.finish()[0][0]
b(WRITE, (read_by_b + 20,))
b("COMMIT")
Session("C")(READ)

say()
say("=== Fix 3: REPEATABLE READ, and run the transaction again when it fails")
lab.sql(SETUP)
a, b = Session("A"), Session("B")
a("BEGIN ISOLATION LEVEL REPEATABLE READ")
b("BEGIN ISOLATION LEVEL REPEATABLE READ")
read_by_a = a(READ)[0][0]
read_by_b = b(READ)[0][0]
a(WRITE, (read_by_a + 10,))
a("COMMIT")
b(WRITE, (read_by_b + 20,))
say("-- A common mistake: run only the failed statement again")
b(WRITE, (read_by_b + 20,))
b("ROLLBACK")
say("-- The right retry starts the whole transaction over, with a new read")
b("BEGIN ISOLATION LEVEL REPEATABLE READ")
read_by_b = b(READ)[0][0]
b(WRITE, (read_by_b + 20,))
b("COMMIT")
Session("C")(READ)
python step03_fixes.py

Fix 1: let the database do the arithmetic

The simplest fix is to stop reading the value into Python. UPDATE accounts SET balance = balance + 10 reads and writes in one statement, and PostgreSQL takes care of the overlap:

A> BEGIN
   BEGIN
B> BEGIN
   BEGIN
A> UPDATE accounts SET balance = balance + 10 WHERE id = 1
   UPDATE 1
B> UPDATE accounts SET balance = balance + 20 WHERE id = 1
   ... waiting for a lock held by another session
A> COMMIT
   COMMIT
B> (the waiting statement finishes)
   UPDATE 1
B> COMMIT
   COMMIT
C> SELECT balance FROM accounts WHERE id = 1
   [(130,)]

A’s UPDATE locks the row. B’s UPDATE has to wait, which is why the transcript says it is waiting for a lock. When A commits, B’s statement finishes, and the balance is 130. The documentation explains what B’s statement does after the wait: “If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition.” In other words, B’s balance + 20 was applied to 110, not to the 100 that B’s snapshot could see. Use this fix whenever the new value can be written as an expression in SQL.

Fix 2: lock the row while you read it

Sometimes your program really must read the value, think about it, and then write. In that case lock the row at the moment you read it, with SELECT ... FOR UPDATE:

A> BEGIN
   BEGIN
B> BEGIN
   BEGIN
A> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE
   [(100,)]
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE
   ... waiting for a lock held by another session
A> UPDATE accounts SET balance = %s WHERE id = 1    -- (110,)
   UPDATE 1
A> COMMIT
   COMMIT
B> (the waiting statement finishes)
   [(110,)]
B> UPDATE accounts SET balance = %s WHERE id = 1    -- (130,)
   UPDATE 1
B> COMMIT
   COMMIT
C> SELECT balance FROM accounts WHERE id = 1
   [(130,)]

Now it is B’s read that waits. After A commits, B’s SELECT ... FOR UPDATE returns 110, the value A wrote, so B computes 130 and the balance is correct. The PostgreSQL chapter on data consistency checks at the application level says when you need this: “When non-serializable writes are possible, to ensure the current validity of a row and protect it against concurrent updates one must use SELECT FOR UPDATE, SELECT FOR SHARE, or an appropriate LOCK TABLE statement.” The price is that other sessions wait while you hold the lock, so keep the transaction short and do no slow work, such as network calls, between the lock and the commit.

Fix 3: REPEATABLE READ, and retry when PostgreSQL says no

The third approach lets both sessions run without waiting for a lock on the read, and makes PostgreSQL detect the conflict at the write. Under REPEATABLE READ a transaction works with one snapshot for its whole life, so B’s UPDATE has to refuse to overwrite a row that changed after B’s snapshot:

A> BEGIN ISOLATION LEVEL REPEATABLE READ
   BEGIN
B> BEGIN ISOLATION LEVEL REPEATABLE READ
   BEGIN
A> SELECT balance FROM accounts WHERE id = 1
   [(100,)]
B> SELECT balance FROM accounts WHERE id = 1
   [(100,)]
A> UPDATE accounts SET balance = %s WHERE id = 1    -- (110,)
   UPDATE 1
A> COMMIT
   COMMIT
B> UPDATE accounts SET balance = %s WHERE id = 1    -- (120,)
   ERROR SerializationFailure (SQLSTATE 40001): could not serialize access due to concurrent update
-- A common mistake: run only the failed statement again
B> UPDATE accounts SET balance = %s WHERE id = 1    -- (120,)
   ERROR InFailedSqlTransaction (SQLSTATE 25P02): current transaction is aborted, commands ignored until end of transaction block
B> ROLLBACK
   ROLLBACK
-- The right retry starts the whole transaction over, with a new read
B> BEGIN ISOLATION LEVEL REPEATABLE READ
   BEGIN
B> SELECT balance FROM accounts WHERE id = 1
   [(110,)]
B> UPDATE accounts SET balance = %s WHERE id = 1    -- (130,)
   UPDATE 1
B> COMMIT
   COMMIT
C> SELECT balance FROM accounts WHERE id = 1
   [(130,)]

B’s first UPDATE fails with SQLSTATE 40001 and the message could not serialize access due to concurrent update. The documentation says what to do about it: “When an application receives this error message, it should abort the current transaction and retry the whole transaction from the beginning.” The transcript also shows a trap that is easy to fall into. After the error the transaction is in an aborted state, so running only the failed statement again fails with SQLSTATE 25P02 (current transaction is aborted, commands ignored until end of transaction block). The correct retry rolls back, starts a new transaction, reads again (now 110), and writes 130. The retry has to include the read, because the old read is exactly what was wrong.

Check before moving on: each of the three runs ends with [(130,)], and in Fix 3 you can see both the 40001 and the 25P02 error. You will build a reusable version of that retry in Step 6.

Step 4: Know what your transaction can see

To choose an isolation level you need to know when a transaction stops seeing other sessions’ commits. This script shows it with a ticket table, where session B inserts a row while A has a transaction open:

# step04_snapshots.py
"""step04_snapshots.py: when does a transaction stop seeing other people's commits?"""
import lab
from lab import Session, say

SETUP = """
    DROP TABLE IF EXISTS tickets;
    CREATE TABLE tickets (id serial PRIMARY KEY, title text NOT NULL);
    INSERT INTO tickets (title) VALUES ('first'), ('second');
"""
COUNT = "SELECT count(*) FROM tickets"
ADD = "INSERT INTO tickets (title) VALUES (%s)"

for level in ("READ COMMITTED", "REPEATABLE READ"):
    say(f"=== {level}")
    lab.sql(SETUP)
    a, b = Session("A"), Session("B")
    a(f"BEGIN ISOLATION LEVEL {level}")
    a(COUNT)
    b(ADD, ("third",))
    a(COUNT)
    a("COMMIT")
    say()

say("=== REPEATABLE READ: the snapshot is taken by the first query, not by BEGIN")
lab.sql(SETUP)
a, b = Session("A"), Session("B")
a("BEGIN ISOLATION LEVEL REPEATABLE READ")
b(ADD, ("third",))
a(COUNT)
b(ADD, ("fourth",))
a(COUNT)
a("COMMIT")
python step04_snapshots.py

READ COMMITTED takes a new snapshot for every statement

A> BEGIN ISOLATION LEVEL READ COMMITTED
   BEGIN
A> SELECT count(*) FROM tickets
   [(2,)]
B> INSERT INTO tickets (title) VALUES (%s)    -- ('third',)
   INSERT 0 1
A> SELECT count(*) FROM tickets
   [(3,)]
A> COMMIT
   COMMIT

A counts 2 tickets, B commits a third one, and A’s second count in the same transaction is 3. The same query gave two answers inside one transaction. The manual calls this a phantom read: a transaction re-runs a query and “finds that the set of rows satisfying the condition has changed due to another recently-committed transaction”. It is how READ COMMITTED works. That can be fine for a single query and surprising when you read twice and expect the same answer, for example when you total a report from several queries.

REPEATABLE READ keeps one snapshot for the whole transaction

A> BEGIN ISOLATION LEVEL REPEATABLE READ
   BEGIN
A> SELECT count(*) FROM tickets
   [(2,)]
B> INSERT INTO tickets (title) VALUES (%s)    -- ('third',)
   INSERT 0 1
A> SELECT count(*) FROM tickets
   [(2,)]
A> COMMIT
   COMMIT

Here A counts 2 both times, even though B committed in between, and PostgreSQL does not show A the new row at all. The documentation describes the difference: “a query in a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction, not as of the start of the current statement within the transaction.”

A gotcha: the snapshot starts at the first query, not at BEGIN

A> BEGIN ISOLATION LEVEL REPEATABLE READ
   BEGIN
B> INSERT INTO tickets (title) VALUES (%s)    -- ('third',)
   INSERT 0 1
A> SELECT count(*) FROM tickets
   [(3,)]
B> INSERT INTO tickets (title) VALUES (%s)    -- ('fourth',)
   INSERT 0 1
A> SELECT count(*) FROM tickets
   [(3,)]
A> COMMIT
   COMMIT

A issued BEGIN ISOLATION LEVEL REPEATABLE READ before B inserted the third ticket, yet A’s first count is 3. Only the next insert (the fourth ticket) stays invisible. The phrase “first non-transaction-control statement” in the quote above is exactly this: BEGIN does not take the snapshot, the first real query does. If you open a transaction early and do other work before your first query, the snapshot will be later than you might expect.

Check before moving on: you should see 2 then 3 for READ COMMITTED, 2 then 2 for REPEATABLE READ, and 3 then 3 in the last block.

Step 5: Reproduce write skew

Lost updates happen when two transactions change the same row. Write skew is sneakier: the two transactions change different rows, but each decision depended on rows the other one changed. The PostgreSQL wiki page on serializable snapshot isolation defines it this way: “When two concurrent transactions each determine what they are writing based on reading a data set which overlaps what the other is writing, you can get a state which could not occur if either had run before the other. This is known as write skew”.

Our example is a hospital rule: at least one doctor must stay on call. Alice and Bob are both on call. Each one’s request counts the doctors on call, sees 2, concludes that it is safe to leave, and marks itself off call. The script runs the same interleaving at all three isolation levels:

# step05_write_skew.py
"""step05_write_skew.py: two on-call doctors both go home."""
import lab
from lab import Session, say

SETUP = """
    DROP TABLE IF EXISTS doctors;
    CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
    INSERT INTO doctors VALUES ('alice', true), ('bob', true);
"""
COUNT = "SELECT count(*) FROM doctors WHERE on_call"

for level in ("READ COMMITTED", "REPEATABLE READ", "SERIALIZABLE"):
    say(f"=== {level}")
    lab.sql(SETUP)
    a, b = Session("A"), Session("B")
    a(f"BEGIN ISOLATION LEVEL {level}")
    b(f"BEGIN ISOLATION LEVEL {level}")
    a(COUNT)
    b(COUNT)
    say("-- both see 2 on call, so each decides it is safe to leave")
    a("UPDATE doctors SET on_call = false WHERE name = 'alice'")
    b("UPDATE doctors SET on_call = false WHERE name = 'bob'")
    a("COMMIT")
    b("COMMIT")
    say("-- doctors on call at the end:")
    Session("C")(COUNT)
    say()

say("=== SERIALIZABLE: B starts over")
b("BEGIN ISOLATION LEVEL SERIALIZABLE")
b(COUNT)
say("-- B now sees 1 doctor on call, so B must stay")
b("ROLLBACK")
python step05_write_skew.py

READ COMMITTED breaks the rule

A> BEGIN ISOLATION LEVEL READ COMMITTED
   BEGIN
B> BEGIN ISOLATION LEVEL READ COMMITTED
   BEGIN
A> SELECT count(*) FROM doctors WHERE on_call
   [(2,)]
B> SELECT count(*) FROM doctors WHERE on_call
   [(2,)]
-- both see 2 on call, so each decides it is safe to leave
A> UPDATE doctors SET on_call = false WHERE name = 'alice'
   UPDATE 1
B> UPDATE doctors SET on_call = false WHERE name = 'bob'
   UPDATE 1
A> COMMIT
   COMMIT
B> COMMIT
   COMMIT
-- doctors on call at the end:
C> SELECT count(*) FROM doctors WHERE on_call
   [(0,)]

Both updates succeed, both commits succeed, and nobody is on call. Neither UPDATE touched the other’s row, so there was nothing to wait for.

REPEATABLE READ breaks it too

A> UPDATE doctors SET on_call = false WHERE name = 'alice'
   UPDATE 1
B> UPDATE doctors SET on_call = false WHERE name = 'bob'
   UPDATE 1
A> COMMIT
   COMMIT
B> COMMIT
   COMMIT
-- doctors on call at the end:
C> SELECT count(*) FROM doctors WHERE on_call
   [(0,)]

This is the part that is easy to miss. REPEATABLE READ stopped the lost update in Step 3 because both sessions wrote the same row. Here they wrote different rows, so it saw no conflict and the rule was broken again. The wiki page says that plain snapshot isolation and serializable snapshot isolation correspond to REPEATABLE READ and SERIALIZABLE in PostgreSQL beginning with version 9.1, and that is the difference you are looking at. The Microsoft Research report A Critique of ANSI SQL Isolation Levels (MSR-TR-95-51, by Berenson and co-authors) is where snapshot isolation was defined.

SERIALIZABLE catches it

A> BEGIN ISOLATION LEVEL SERIALIZABLE
   BEGIN
B> BEGIN ISOLATION LEVEL SERIALIZABLE
   BEGIN
A> SELECT count(*) FROM doctors WHERE on_call
   [(2,)]
B> SELECT count(*) FROM doctors WHERE on_call
   [(2,)]
-- both see 2 on call, so each decides it is safe to leave
A> UPDATE doctors SET on_call = false WHERE name = 'alice'
   UPDATE 1
B> UPDATE doctors SET on_call = false WHERE name = 'bob'
   UPDATE 1
A> COMMIT
   COMMIT
B> COMMIT
   ERROR SerializationFailure (SQLSTATE 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.

A commits first. When B tries to commit, PostgreSQL cancels B with SQLSTATE 40001 and the message could not serialize access due to read/write dependencies among transactions. The DETAIL line uses jargon from the research papers (“pivot”); you do not need the theory to act on it. The HINT is the part that matters: The transaction might succeed if retried. Each transaction read rows the other one changed, so neither could have run first, and PostgreSQL refuses to let both commit. The documentation says this level “works exactly the same as Repeatable Read except that it also monitors for conditions which could make execution of a concurrent set of serializable transactions behave in a manner inconsistent with all possible serial (one at a time) executions of those transactions.” The research behind it was described in the PostgreSQL implementation paper, which says that this “is the first implementation of SSI in a production database release”.

Now run B again from the top, as the retry rule says:

B> BEGIN ISOLATION LEVEL SERIALIZABLE
   BEGIN
B> SELECT count(*) FROM doctors WHERE on_call
   [(1,)]
-- B now sees 1 doctor on call, so B must stay
B> ROLLBACK
   ROLLBACK

B’s second attempt sees only one doctor on call and decides to stay, so the rule holds. The wiki page adds a rule that is easy to forget: “Any query results from a failed serializable transaction must be ignored; use the results from the retry.” Never act on anything you read inside a transaction that later failed.

Check before moving on: you should see 0 doctors on call at READ COMMITTED and REPEATABLE READ, the 40001 error at SERIALIZABLE with 1 doctor left, and the retry seeing 1.

Step 6: Build a retry loop and test it under real concurrency

Step 5 fixed the bug by hand. In an application the retry has to be automatic, and it has to be written once and used everywhere. Here is the helper:

# retry.py
"""retry.py: run a whole transaction again when PostgreSQL says "try again"."""
import random
import time

import psycopg
from psycopg import errors

RETRYABLE = (errors.SerializationFailure, errors.DeadlockDetected)  # SQLSTATE 40001 and 40P01


def run_transaction(conn, work, *, isolation=psycopg.IsolationLevel.SERIALIZABLE,
                    max_attempts=8, base_delay=0.005, on_retry=None):
    """Call work(conn) inside one transaction at the given isolation level.

    If PostgreSQL aborts the transaction with a serialization failure or a deadlock, wait a random
    time (exponential backoff with jitter) and call work(conn) again from the top in a new
    transaction. Returns (result, attempts_used). Every other error is raised at once.
    """
    conn.isolation_level = isolation  # used by the transactions that start after this line
    attempt = 0
    while True:
        attempt += 1
        try:
            with conn.transaction():
                return work(conn), attempt  # COMMIT runs when the block exits, still inside the try
        except RETRYABLE as exc:
            if attempt >= max_attempts:
                raise
            if on_retry:
                on_retry(attempt, exc)
            time.sleep(random.uniform(0, base_delay * 2 ** attempt))

Read it from the top. RETRYABLE lists the two exceptions that mean run the transaction again: SerializationFailure (SQLSTATE 40001) and DeadlockDetected (40P01, which you will meet in Step 9). The PostgreSQL error code appendix lists both. The documentation says why you cannot avoid writing this: “It is important that an environment which uses this technique have a generalized way of handling serialization failures (which always return with an SQLSTATE value of ‘40001’), because it will be very hard to predict exactly which transactions might contribute to the read/write dependencies and need to be rolled back to prevent serialization anomalies.”

The function sets conn.isolation_level first. psycopg’s documentation notes that this setting can only be changed when no transaction is active, so it sits before the loop. Then with conn.transaction() starts a transaction. In the psycopg documentation’s words: “When the context is entered, a transaction is started; when leaving the context the transaction is committed, or it is rolled back if an exception is raised inside the block.” Notice that the return sits inside the block, so the commit happens when the block exits, inside the try. That matters because, as Step 5 showed, the failure can arrive at COMMIT. If the loop only protected your statements and not the commit, it would miss exactly that failure.

When a retryable error happens, the loop waits a random time that grows with each attempt (exponential backoff with jitter, the same idea as in our retry with backoff tutorial) and calls work(conn) again from the top. It stops after max_attempts and re-raises the last error. Every other error is raised immediately, because retrying a unique violation or a typo in your SQL will not help.

The rules for the function you pass in

The retry loop is only safe if the function you give it follows three rules. First, it must do everything for the transaction: the reads, the decision and the writes, so a retry recomputes from fresh data. Second, it must not catch the retryable errors itself, or the loop never sees them. Third, it must not do anything a rollback cannot undo. A sent email, a payment API call or a message put on a queue will happen again on every attempt. The test suite in Step 10 includes a test that proves this: a pretend email is sent twice. Do those things after the function returns, or write them to an outbox table inside the transaction and send them later (see the transactional outbox tutorial).

A workload that races on purpose

To test the loop we need requests that collide every single time. workload.py starts one thread per doctor, gives each its own connection, and uses a barrier so that every request has read the count before any request writes. Real traffic will not be this tidy, but a bug that appears every run is much easier to fix than one that appears once a month. The function at the bottom of the file, lock_pair, is for Step 9; ignore it for now.

# workload.py
"""workload.py: concurrent requests that race on purpose, so every run hits the same bug."""
import threading
import time
from collections import Counter

from psycopg import IsolationLevel, errors

import lab
from retry import run_transaction


def reset_doctors(count):
    lab.sql(f"""
        DROP TABLE IF EXISTS doctors;
        CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
        INSERT INTO doctors SELECT 'dr' || g, true FROM generate_series(1, {int(count)}) AS g;
    """)


def go_off_call(conn, doctor, keep, after_read=lambda: None):
    """The business rule, written the way most of us would write it: check, then act."""
    on_call = conn.execute("SELECT count(*) FROM doctors WHERE on_call").fetchone()[0]
    after_read()
    if on_call <= keep:
        return "stayed"
    conn.execute("UPDATE doctors SET on_call = false WHERE name = %s", (doctor,))
    return "left"


def run(level, max_attempts, doctors=8, keep=3, show_retries=False):
    """Every doctor asks to leave at once; at least `keep` must stay on call."""
    reset_doctors(doctors)
    barrier = threading.Barrier(doctors)
    outcomes, attempts = [], []
    started = time.perf_counter()

    def at():
        return f"+{(time.perf_counter() - started) * 1000:5.1f} ms"

    def request(name):
        conn = lab.connect()
        first_try = [True]

        def after_read():
            if first_try[0]:  # make every request read before any request writes
                first_try[0] = False
                barrier.wait(timeout=30)

        def note(attempt, exc):
            if show_retries:
                reason = exc.diag.message_detail or exc.diag.message_primary
                lab.say(f"  {at()}  {name}: attempt {attempt} failed with {type(exc).__name__} ({reason}), trying again")

        try:
            outcome, used = run_transaction(conn, lambda c: go_off_call(c, name, keep, after_read),
                                            isolation=level, max_attempts=max_attempts, on_retry=note)
            outcomes.append(outcome)
            attempts.append(used)
            if show_retries:
                lab.say(f"  {at()}  {name}: finished ({outcome}) on attempt {used}")
        except errors.SerializationFailure:
            outcomes.append("gave up (40001)")
        finally:
            conn.close()

    threads = [threading.Thread(target=request, args=(f"dr{i}",)) for i in range(1, doctors + 1)]
    for thread in threads:
        thread.start()
    for thread in threads:
        thread.join()
    if len(outcomes) != doctors:
        raise RuntimeError(f"only {len(outcomes)} of {doctors} requests finished")
    with lab.connect() as conn:
        left = conn.execute("SELECT count(*) FROM doctors WHERE on_call").fetchone()[0]
    return {"on_call": left, "outcomes": dict(sorted(Counter(outcomes).items())), "attempts": sorted(attempts)}


def lock_pair(opposite):
    """Two transactions each update rows 1 and 2. In opposite order they deadlock."""
    lab.sql("""
        DROP TABLE IF EXISTS locks;
        CREATE TABLE locks (id int PRIMARY KEY, v int NOT NULL DEFAULT 0);
        INSERT INTO locks (id) VALUES (1), (2);
    """)
    barrier = threading.Barrier(2)
    attempts = []

    def transfer(rows, start_delay):
        time.sleep(start_delay)
        conn = lab.connect()
        first_try = [True]

        def work(c):
            for position, row in enumerate(rows):
                c.execute("UPDATE locks SET v = v + 1 WHERE id = %s", (row,))
                if position == 0 and first_try[0]:
                    first_try[0] = False
                    if opposite:
                        barrier.wait(timeout=30)  # both hold their first row before asking for the second
                    else:
                        time.sleep(0.4)  # hold the first row for a while

        _, used = run_transaction(conn, work, isolation=IsolationLevel.READ_COMMITTED)
        attempts.append(used)
        conn.close()

    second_rows = (2, 1) if opposite else (1, 2)
    started = time.perf_counter()
    threads = [threading.Thread(target=transfer, args=((1, 2), 0.0)),
               threading.Thread(target=transfer, args=(second_rows, 0.0 if opposite else 0.1))]
    for thread in threads:
        thread.start()
    for thread in threads:
        thread.join()
    return {"attempts": sorted(attempts), "seconds": round(time.perf_counter() - started, 1)}
# step06_retry.py
"""step06_retry.py: SERIALIZABLE plus a retry loop keeps the rule; every other combination breaks it."""
from psycopg import IsolationLevel as L

import lab
import workload
from lab import say

say("=== Two doctors, at least 1 must stay: SERIALIZABLE with retries")
result = workload.run(L.SERIALIZABLE, max_attempts=8, doctors=2, keep=1, show_retries=True)
say(result)

say()
say("=== Eight doctors ask to leave at the same moment, at least 3 must stay")
CASES = [
    ("READ COMMITTED", L.READ_COMMITTED, 1),
    ("REPEATABLE READ", L.REPEATABLE_READ, 1),
    ("SERIALIZABLE, no retry", L.SERIALIZABLE, 1),
    ("SERIALIZABLE, up to 12 attempts", L.SERIALIZABLE, 12),
]
for label, level, max_attempts in CASES:
    result = workload.run(level, max_attempts)
    say(f"{label:<33} on call at the end: {result['on_call']}  outcomes: {result['outcomes']}")
    say(f"{'':<33} attempts per request: {result['attempts']}")
python step06_retry.py

Two doctors: watch a retry happen

  + 31.5 ms  dr1: attempt 1 failed with SerializationFailure (Reason code: Canceled on identification as a pivot, during commit attempt.), trying again
  + 32.3 ms  dr2: finished (left) on attempt 1
  + 35.6 ms  dr1: finished (stayed) on attempt 2
{'on_call': 1, 'outcomes': {'left': 1, 'stayed': 1}, 'attempts': [1, 2]}

Each line starts with the time since the workload began. One doctor’s first attempt failed with the “pivot” error, the other doctor finished on the first attempt, and the failed one retried, saw that only one doctor was left on call, and stayed. Your timings and attempt counts will differ, and that is the point of the next paragraph.

The wiki page says that the “first committer wins” rule ensures progress and that the rolled-back transaction “can immediately be retried.” That is true, but in my runs an instant retry sometimes lost a second time. Here is a different run of the same script, which needed three attempts:

  + 27.3 ms  dr1: attempt 1 failed with SerializationFailure (Reason code: Canceled on identification as a pivot, during commit attempt.), trying again
  + 30.0 ms  dr1: attempt 2 failed with SerializationFailure (Reason code: Canceled on identification as a pivot, during write.), trying again
  + 34.5 ms  dr2: finished (left) on attempt 1
  + 34.9 ms  dr1: finished (stayed) on attempt 3
{'on_call': 1, 'outcomes': {'left': 1, 'stayed': 1}, 'attempts': [1, 3]}

The retry’s failure came at +30.0 ms, before the winner’s commit finished at +34.5 ms. My reading is that the retry’s new snapshot could not yet see the winner’s change, so it counted 2 doctors on call again and was cancelled again; only the third attempt saw the winner’s commit. That is why the helper sleeps for a random, growing time before it retries, and why you should always allow several attempts.

Eight doctors: compare the isolation levels

READ COMMITTED                    on call at the end: 0  outcomes: {'left': 8}
                                  attempts per request: [1, 1, 1, 1, 1, 1, 1, 1]
REPEATABLE READ                   on call at the end: 0  outcomes: {'left': 8}
                                  attempts per request: [1, 1, 1, 1, 1, 1, 1, 1]
SERIALIZABLE, no retry            on call at the end: 7  outcomes: {'gave up (40001)': 7, 'left': 1}
                                  attempts per request: [1]
SERIALIZABLE, up to 12 attempts   on call at the end: 3  outcomes: {'left': 5, 'stayed': 3}
                                  attempts per request: [1, 2, 2, 2, 2, 3, 3, 3]

Eight doctors ask to leave at the same moment and at least three must stay. At READ COMMITTED and REPEATABLE READ, all eight leave and zero are on call. At SERIALIZABLE without a retry, the data stays correct (7 on call, so the rule held) but seven requests got an error, which is safe but a poor experience for those users. With the retry loop, the final count is exactly 3, five doctors left, three were told to stay, and no request failed. The attempt counts in the last line (mine were between 1 and 3) change from run to run, while the final 3 and the outcomes {'left': 5, 'stayed': 3} do not.

Check before moving on: the last case must say on call at the end: 3. If a request reports gave up (40001) in that case, raise max_attempts; retries are normal in this design and you should plan for several.

Step 7: Understand why SERIALIZABLE cancels transactions that never touched the same row

You may have noticed something odd in the eight-doctor run: every doctor reads the whole table, but each one writes only its own row, and still most requests needed more than one attempt. SERIALIZABLE does not wait for conflicts; it records what each transaction read and compares notes. Those records are called predicate locks. They do not make other sessions wait: the documentation says this monitoring “does not introduce any blocking beyond that present in repeatable read”. The documentation explains: “Predicate locks in PostgreSQL, like in most other database systems, are based on data actually accessed by a transaction.” They show up in pg_locks with the mode SIReadLock, and how fine-grained they are depends on the query plan.

That means a harmless-looking query can make your transactions collide more often than the data requires. This script measures how much:

# step07_predicate_locks.py
"""step07_predicate_locks.py: why SERIALIZABLE aborts transactions that never touched the same row."""
import threading

from psycopg import errors

import lab
from lab import Session, say
from retry import run_transaction

LOCKS = """SELECT locktype, relation::regclass::text AS relation FROM pg_locks
           WHERE mode = 'SIReadLock' AND pid = pg_backend_pid()
           ORDER BY locktype, relation"""
LOOKUPS = {
    "id": "SELECT balance FROM acct WHERE id = %s",
    "owner": "SELECT balance FROM acct WHERE owner = %s",
}


def build(fillfactor):
    lab.sql(f"""
        DROP TABLE IF EXISTS acct;
        CREATE TABLE acct (id int PRIMARY KEY, owner text NOT NULL, balance int NOT NULL)
            WITH (fillfactor = {fillfactor});
        INSERT INTO acct SELECT g, 'user' || g, 100 FROM generate_series(1, 10000) AS g;
        ANALYZE acct;
    """)


def key(lookup, row):
    return (row,) if lookup == "id" else (f"user{row}",)


say("=== Which predicate locks does one SERIALIZABLE read leave behind?")
build(100)
a = Session("A")
for label, where in [("id", "id = 7"), ("owner", "owner = 'user7'")]:
    say(f"-- lookup by {label}")
    a("BEGIN ISOLATION LEVEL SERIALIZABLE")
    a(f"EXPLAIN (COSTS OFF) SELECT balance FROM acct WHERE {where}")
    a(f"SELECT balance FROM acct WHERE {where}")
    a(LOCKS)
    a("ROLLBACK")

say()
say("=== Where does an UPDATE put the new version of a row? (ctid is page number, then slot)")
for fillfactor in (100, 70):
    say(f"-- table loaded with fillfactor {fillfactor}")
    build(fillfactor)
    a = Session("A")
    a("SELECT ctid FROM acct WHERE id = 1")
    a("UPDATE acct SET balance = balance - 1 WHERE id = 1")
    a("SELECT ctid FROM acct WHERE id = 1")


def two_debits(lookup, first_row, second_row):
    """Two SERIALIZABLE transactions read and debit two different rows. What happens to the second COMMIT?"""
    a, b = lab.connect(), lab.connect()
    for conn in (a, b):
        conn.execute("BEGIN ISOLATION LEVEL SERIALIZABLE")
    for conn, row in ((a, first_row), (b, second_row)):
        conn.execute(LOOKUPS[lookup], key(lookup, row)).fetchone()
    for conn, row in ((a, first_row), (b, second_row)):
        conn.execute("UPDATE acct SET balance = balance - 1 WHERE id = %s", (row,))
    a.execute("COMMIT")
    try:
        b.execute("COMMIT")
        outcome = "both commit"
    except errors.SerializationFailure:
        outcome = "the second COMMIT fails with 40001"
    a.close()
    b.close()
    return outcome


say()
say("=== Two transactions, two different accounts")
for fillfactor, lookup, first, second in [(100, "owner", 1, 2), (100, "id", 1, 2), (100, "id", 1, 9000), (70, "id", 1, 2)]:
    build(fillfactor)
    say(f"fillfactor {fillfactor:>3}  lookup by {lookup:<5}  rows {first} and {second:<4}  {two_debits(lookup, first, second)}")


def hammer(lookup, workers=8):
    """Each worker reads its own account, then debits it. All workers read before any worker writes."""
    barrier = threading.Barrier(workers)
    used = []

    def worker(i):
        conn = lab.connect()
        first_try = [True]

        def work(c):
            c.execute(LOOKUPS[lookup], key(lookup, i)).fetchone()
            if first_try[0]:
                first_try[0] = False
                barrier.wait(timeout=30)
            c.execute("UPDATE acct SET balance = balance - 1 WHERE id = %s", (i,))

        try:
            used.append(run_transaction(conn, work, max_attempts=30)[1])
        except errors.SerializationFailure:
            used.append(-1)
        conn.close()

    threads = [threading.Thread(target=worker, args=(i,)) for i in range(1, workers + 1)]
    for thread in threads:
        thread.start()
    for thread in threads:
        thread.join()
    return sorted(used)


say()
say("=== Eight workers debit eight different accounts: total attempts in each of three runs")
for fillfactor, lookup in [(100, "owner"), (100, "id"), (70, "id")]:
    totals = []
    for _ in range(3):
        build(fillfactor)
        totals.append(sum(hammer(lookup)))
    say(f"fillfactor {fillfactor:>3}  lookup by {lookup:<5}  {totals}   (8 would mean no retries)")
python step07_predicate_locks.py

What one read leaves behind

-- lookup by id
A> BEGIN ISOLATION LEVEL SERIALIZABLE
   BEGIN
A> EXPLAIN (COSTS OFF) SELECT balance FROM acct WHERE id = 7
   [('Index Scan using acct_pkey on acct',), ('  Index Cond: (id = 7)',)]
A> SELECT balance FROM acct WHERE id = 7
   [(100,)]
A> SELECT locktype, relation::regclass::text AS relation FROM pg_locks WHERE mode = 'SIReadLock' AND pid = pg_backend_pid() ORDER BY locktype, relation
   [('page', 'acct_pkey'), ('tuple', 'acct')]
A> ROLLBACK
   ROLLBACK
-- lookup by owner
A> BEGIN ISOLATION LEVEL SERIALIZABLE
   BEGIN
A> EXPLAIN (COSTS OFF) SELECT balance FROM acct WHERE owner = 'user7'
   [('Seq Scan on acct',), ("  Filter: (owner = 'user7'::text)",)]
A> SELECT balance FROM acct WHERE owner = 'user7'
   [(100,)]
A> SELECT locktype, relation::regclass::text AS relation FROM pg_locks WHERE mode = 'SIReadLock' AND pid = pg_backend_pid() ORDER BY locktype, relation
   [('relation', 'acct')]
A> ROLLBACK
   ROLLBACK

A lookup by id uses the primary key index, and leaves two small locks: a page lock on the index acct_pkey and a tuple lock on the one row it read. A lookup by owner has no index, so PostgreSQL scans the whole table and leaves a single relation lock on acct. The documentation is blunt about the second case: “A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures.” It also notes that “multiple finer-grained locks (e.g., tuple locks) may be combined into fewer coarser-grained locks (e.g., page locks) during the course of the transaction to prevent exhaustion of the memory used to track the locks.”

Where an update puts the new row version

-- table loaded with fillfactor 100
A> SELECT ctid FROM acct WHERE id = 1
   [('(0,1)',)]
A> UPDATE acct SET balance = balance - 1 WHERE id = 1
   UPDATE 1
A> SELECT ctid FROM acct WHERE id = 1
   [('(62,116)',)]
-- table loaded with fillfactor 70
A> SELECT ctid FROM acct WHERE id = 1
   [('(0,1)',)]
A> UPDATE acct SET balance = balance - 1 WHERE id = 1
   UPDATE 1
A> SELECT ctid FROM acct WHERE id = 1
   [('(0,130)',)]

The ctid column is a row version’s physical address: the page number, then the slot on that page. In the table filled to the brim (fillfactor 100, the default), the updated row moved from page 0 to page 62, near the end of the table. In the table that left 30 percent of each page free (fillfactor 70), it stayed on page 0. The CREATE TABLE reference explains the setting: with a smaller fillfactor, “the remaining space on each page is reserved for updating rows on that page”. This matters because of an optimization the documentation calls heap-only tuples (HOT). It is possible when “The update does not modify any columns referenced by the table’s indexes” and “There is sufficient free space on the page containing the old row for the updated row”, and in that case “New index entries are not needed to represent updated rows”. The same page adds: “You can increase the likelihood of sufficient page space for HOT updates by decreasing a table’s fillfactor.” So an update that has to move to another page also has to write a new entry into the index.

Two transactions, two different accounts

fillfactor 100  lookup by owner  rows 1 and 2     the second COMMIT fails with 40001
fillfactor 100  lookup by id     rows 1 and 2     the second COMMIT fails with 40001
fillfactor 100  lookup by id     rows 1 and 9000  both commit
fillfactor  70  lookup by id     rows 1 and 2     both commit

Each line reads and debits two different accounts in two SERIALIZABLE transactions, and shows whether the second commit survives. The sequential scan (lookup by owner) fails, as the documentation predicts. The surprise is the second line: a lookup by id on rows 1 and 2 also fails in the table with fillfactor 100, while rows 1 and 9000 commit together, and rows 1 and 2 commit together in the table with fillfactor 70. My explanation, which fits all four lines but which I did not prove by reading PostgreSQL’s source: rows 1 and 2 share one page of the index, the first transaction holds a lock on that index page (the page lock on acct_pkey in the first block), and in the full table the second transaction’s update has to write a new index entry onto that same page, which counts as a conflict. When the update can stay on its heap page, no index entry is written and there is no conflict. Rows 1 and 9000 live on different index pages, so they did not collide.

Eight workers, eight accounts

fillfactor 100  lookup by owner  [20, 19, 18]   (8 would mean no retries)
fillfactor 100  lookup by id     [15, 15, 15]   (8 would mean no retries)
fillfactor  70  lookup by id     [8, 8, 8]   (8 would mean no retries)

Eight workers each read and debit their own account, with everyone reading before anyone writes. With 8 attempts in total there were no retries. A lookup by owner needed 18 to 20 attempts in total (10 to 12 extra attempts), a lookup by primary key on the full table needed 15, and the same lookup on the table with fillfactor 70 needed exactly 8. The numbers come from a synthetic table of 10,000 rows on one machine, so treat the pattern, not the exact values, as the lesson.

What to do about it

Do not try to eliminate these failures; handle them. Always run SERIALIZABLE work through the retry loop from Step 6. Then reduce how often it fires. Make sure the queries that feed a decision can use an index (check them with EXPLAIN, as in the first block above), because, as the lock listing showed, an index scan leaves a page lock and a tuple lock while a sequential scan leaves one lock on the whole table. The documentation also suggests encouraging index scans “by reducing random_page_cost and/or increasing cpu_tuple_cost”; I did not test those settings. Keep serializable transactions short, and do not leave connections sitting “idle in transaction”, which the same page warns against. Lowering a table’s fillfactor removed the index-page collisions in my test; it leaves free space on every page, so the table grows, and you should measure on your own workload before changing it. The same chapter also warns that when the predicate lock table runs short of memory, PostgreSQL combines page locks into relation locks and failures rise, and it points at max_pred_locks_per_transaction, max_pred_locks_per_relation and max_pred_locks_per_page.

Check before moving on: the lock listing must show a relation lock for the owner lookup and page plus tuple locks for the id lookup, and the last block must show 8 for the fillfactor 70 row. Attempt totals for the other two rows will vary by a few.

Step 8: Let the database enforce the rule with an exclusion constraint

Write skew is not only about doctors. Double booking a meeting room is the same bug: two requests check that the slot is free, both see that it is, and both insert a booking. If your rule is about uniqueness or overlap, the best fix is not an isolation level at all, but a constraint that the database enforces for every writer. PostgreSQL can enforce a rule such as no two bookings for a room may overlap with an exclusion constraint:

# step08_constraint.py
"""step08_constraint.py: let the database refuse the double booking."""
import lab
from lab import Session, say

lab.sql("""
    CREATE EXTENSION IF NOT EXISTS btree_gist;
    DROP TABLE IF EXISTS bookings;
    CREATE TABLE bookings (id serial PRIMARY KEY, room text NOT NULL, during int4range NOT NULL);
""")
CHECK = "SELECT count(*) FROM bookings WHERE room = 'r1' AND during && int4range(%s, %s)"
BOOK = "INSERT INTO bookings (room, during) VALUES ('r1', int4range(%s, %s))"
OVERLAPS = """SELECT x.id, y.id FROM bookings x JOIN bookings y
              ON x.id < y.id AND x.room = y.room AND x.during && y.during"""

say("=== Check first, insert second (READ COMMITTED)")
a, b = Session("A"), Session("B")
a("BEGIN")
b("BEGIN")
a(CHECK, (9, 11))
b(CHECK, (10, 12))
a(BOOK, (9, 11))
b(BOOK, (10, 12))
a("COMMIT")
b("COMMIT")
say("-- pairs of bookings that overlap:")
Session("C")(OVERLAPS)

say()
say("=== The same race with an exclusion constraint")
lab.sql("""
    DELETE FROM bookings;
    ALTER TABLE bookings ADD CONSTRAINT no_double_booking
        EXCLUDE USING gist (room WITH =, during WITH &&);
""")
a, b = Session("A"), Session("B")
a("BEGIN")
b("BEGIN")
a(CHECK, (9, 11))
b(CHECK, (10, 12))
a(BOOK, (9, 11))
waiting = b.later(BOOK, (10, 12))
a("COMMIT")
waiting.finish()
b("ROLLBACK")
say("-- bookings at the end:")
Session("C")("SELECT room, during::text FROM bookings")
python step08_constraint.py

First, the race without the constraint

A> BEGIN
   BEGIN
B> BEGIN
   BEGIN
A> SELECT count(*) FROM bookings WHERE room = 'r1' AND during && int4range(%s, %s)    -- (9, 11)
   [(0,)]
B> SELECT count(*) FROM bookings WHERE room = 'r1' AND during && int4range(%s, %s)    -- (10, 12)
   [(0,)]
A> INSERT INTO bookings (room, during) VALUES ('r1', int4range(%s, %s))    -- (9, 11)
   INSERT 0 1
B> INSERT INTO bookings (room, during) VALUES ('r1', int4range(%s, %s))    -- (10, 12)
   INSERT 0 1
A> COMMIT
   COMMIT
B> COMMIT
   COMMIT
-- pairs of bookings that overlap:
C> SELECT x.id, y.id FROM bookings x JOIN bookings y ON x.id < y.id AND x.room = y.room AND x.during && y.during
   [(1, 2)]

Both sessions ran the query that asks whether the room is free, and both got 0. Both inserts succeeded at READ COMMITTED, and the final query finds one pair of bookings that overlap (9 to 11 and 10 to 12).

Then the same race with the constraint

A> BEGIN
   BEGIN
B> BEGIN
   BEGIN
A> SELECT count(*) FROM bookings WHERE room = 'r1' AND during && int4range(%s, %s)    -- (9, 11)
   [(0,)]
B> SELECT count(*) FROM bookings WHERE room = 'r1' AND during && int4range(%s, %s)    -- (10, 12)
   [(0,)]
A> INSERT INTO bookings (room, during) VALUES ('r1', int4range(%s, %s))    -- (9, 11)
   INSERT 0 1
B> INSERT INTO bookings (room, during) VALUES ('r1', int4range(%s, %s))    -- (10, 12)
   ... waiting for a lock held by another session
A> COMMIT
   COMMIT
B> (the waiting statement finishes)
   ERROR ExclusionViolation (SQLSTATE 23P01): conflicting key value violates exclusion constraint "no_double_booking"
   DETAIL: Key (room, during)=(r1, [10,12)) conflicts with existing key (room, during)=(r1, [9,11)).
B> ROLLBACK
   ROLLBACK
-- bookings at the end:
C> SELECT room, during::text FROM bookings
   [('r1', '[9,11)')]

The script empties the table and adds EXCLUDE USING gist (room WITH =, during WITH &&). The first insert succeeds. The second insert waits for the first transaction to finish, and when A commits, B fails with SQLSTATE 23P01 (exclusion_violation) and the DETAIL line names the two conflicting ranges. Only the first booking remains. This happened at the default READ COMMITTED level.

The && operator means that the two ranges overlap. An int4range includes its lower bound and excludes its upper bound (the manual says the built-in range types “all use a canonical form that includes the lower bound and excludes the upper bound”), so 9 to 11 and 11 to 13 do not overlap, and the test suite checks that case. The room WITH = part uses plain equality on text, and the PostgreSQL range types chapter explains why you need the extension: “You can use the btree_gist extension to define exclusion constraints on plain scalar data types, which can then be combined with range exclusions for maximum flexibility.” Your code should catch ExclusionViolation and tell the user the slot is taken. Do not retry it: as long as the first booking stands, the same insert fails again.

Check before moving on: the first transcript ends with a pair of overlapping ids, and the second ends with one booking, [('r1', '[9,11)')].

Step 9: Use row locks, and prevent deadlocks with a lock order

Constraints cannot express every rule. The rule that at least one doctor must stay on call is about a count, and a plain constraint cannot see other rows. SERIALIZABLE with a retry loop handles it, and so can explicit row locks at READ COMMITTED. This script shows both the locking fix and the new problem that locks introduce:

# step09_locks.py
"""step09_locks.py: a row lock fixes write skew, and a fixed lock order prevents deadlocks."""
import lab
import workload
from lab import Session, say

lab.sql("""
    DROP TABLE IF EXISTS doctors;
    CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
    INSERT INTO doctors VALUES ('alice', true), ('bob', true);
""")
LOCK_ON_CALL = "SELECT name FROM doctors WHERE on_call FOR UPDATE"

say("=== FOR UPDATE cannot be combined with count(*), so lock the rows and count them yourself")
a, b = Session("A"), Session("B")
a("SELECT count(*) FROM doctors WHERE on_call FOR UPDATE")
a(LOCK_ON_CALL)

say()
say("=== Write skew again, but each doctor first locks every row that is on call (READ COMMITTED)")
a("BEGIN")
b("BEGIN")
a(LOCK_ON_CALL)
say("-- A sees 2 doctors on call, so A may leave")
waiting = b.later(LOCK_ON_CALL)
a("UPDATE doctors SET on_call = false WHERE name = 'alice'")
a("COMMIT")
waiting.finish()
say("-- B now sees only bob, so B must stay")
b("ROLLBACK")

say()
say("=== Two transactions lock rows 1 and 2 in opposite order")
say(workload.lock_pair(opposite=True))
say()
say("=== The same two transactions, both locking in the same order")
say(workload.lock_pair(opposite=False))
python step09_locks.py

Lock the rows the decision depends on

A> SELECT count(*) FROM doctors WHERE on_call FOR UPDATE
   ERROR FeatureNotSupported (SQLSTATE 0A000): FOR UPDATE is not allowed with aggregate functions
A> SELECT name FROM doctors WHERE on_call FOR UPDATE
   [('alice',), ('bob',)]

You cannot write SELECT count(*) ... FOR UPDATE; PostgreSQL answers with SQLSTATE 0A000 (FOR UPDATE is not allowed with aggregate functions). So select the rows themselves and count them in Python. Now the race:

A> BEGIN
   BEGIN
B> BEGIN
   BEGIN
A> SELECT name FROM doctors WHERE on_call FOR UPDATE
   [('alice',), ('bob',)]
-- A sees 2 doctors on call, so A may leave
B> SELECT name FROM doctors WHERE on_call FOR UPDATE
   ... waiting for a lock held by another session
A> UPDATE doctors SET on_call = false WHERE name = 'alice'
   UPDATE 1
A> COMMIT
   COMMIT
B> (the waiting statement finishes)
   [('bob',)]
-- B now sees only bob, so B must stay
B> ROLLBACK
   ROLLBACK

A locks both on-call rows and sees two doctors. B’s identical query has to wait for A’s locks. After A takes itself off call and commits, B’s query finishes with only Bob, because the WHERE on_call condition was re-evaluated against the updated rows. B counts one doctor and stays. This worked at plain READ COMMITTED with no retry. The same rules apply to SELECT FOR UPDATE: the documentation lists it with UPDATE and DELETE as commands that “behave the same as SELECT in terms of searching for target rows”, and that passage goes on to describe the wait and the re-check you saw in Fix 1. Row locks have a limit worth knowing: the documentation says that “SELECT FOR UPDATE and SELECT FOR SHARE lock just the returned rows against concurrent updates”. A row that does not exist yet cannot be locked, so a rule about how many rows may exist, such as no more than five bookings per day, is not protected by locking the rows that exist today. That is the case for SERIALIZABLE or a constraint.

Deadlocks, and the one rule that prevents them

The cost of locking is the possibility of a deadlock: two transactions that each hold a lock the other needs. The last part of the script has two transactions update rows 1 and 2, first in opposite order and then in the same order. It reuses the retry helper:

=== Two transactions lock rows 1 and 2 in opposite order
{'attempts': [1, 2], 'seconds': 1.1}

=== The same two transactions, both locking in the same order
{'attempts': [1, 1], 'seconds': 0.8}

In opposite order, each transaction holds one row and waits for the other. PostgreSQL notices after the deadlock timeout (deadlock_timeout, which defaults to one second) and aborts one of them with SQLSTATE 40P01. The retry helper catches it, so the attempts are [1, 2] and the run takes about a second. In the same order, the second transaction simply waits for the first to finish, and both succeed on attempt 1 (the 0.8 seconds in my run are the deliberate sleeps in the demo). The documentation gives the rule: “The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.” Pick one order, for example ascending id, make every transaction lock its rows in that order as the second run does, and keep the retry loop as a safety net.

Check before moving on: the 0A000 error appears once, B’s waiting query returns [('bob',)], and the two deadlock runs report attempts [1, 2] and [1, 1].

Step 10: Lock it in with tests, then break them on purpose

A retry loop that nobody tests is the kind of code that gets simplified by the next person. This file has two kinds of tests. The first six need no database: they use a fake connection to check the retry rules (retry on 40001 and 40P01, give up after max_attempts, do not retry other errors, set the isolation level first, and run side effects again). The other six run the races from this tutorial against your server.

# test_isolation.py
"""test_isolation.py: unit tests for the retry helper (no server) and tests that need the server."""
import contextlib

import psycopg
import pytest
from psycopg import IsolationLevel as L
from psycopg import errors

import lab
import retry
import workload


class FakeConnection:
    """Just enough of a psycopg connection for run_transaction."""

    def __init__(self):
        self.isolation_level = None
        self.transactions = 0

    @contextlib.contextmanager
    def transaction(self):
        self.transactions += 1
        yield


@pytest.fixture(autouse=True)
def no_sleep(monkeypatch):
    monkeypatch.setattr(retry.time, "sleep", lambda seconds: None)


def flaky(failures, exc):
    """A work function that raises exc the first `failures` times it is called."""
    calls = []

    def work(conn):
        calls.append(1)
        if len(calls) <= failures:
            raise exc("simulated")
        return "done"

    return work, calls


def test_a_serialization_failure_is_retried_until_it_works():
    conn = FakeConnection()
    work, calls = flaky(2, errors.SerializationFailure)
    assert retry.run_transaction(conn, work) == ("done", 3)
    assert len(calls) == 3 and conn.transactions == 3


def test_a_deadlock_is_retried_too():
    work, calls = flaky(1, errors.DeadlockDetected)
    assert retry.run_transaction(FakeConnection(), work) == ("done", 2)


def test_it_gives_up_after_max_attempts_and_raises_the_last_error():
    work, calls = flaky(99, errors.SerializationFailure)
    with pytest.raises(errors.SerializationFailure):
        retry.run_transaction(FakeConnection(), work, max_attempts=4)
    assert len(calls) == 4


def test_other_errors_are_not_retried():
    work, calls = flaky(99, errors.UniqueViolation)
    with pytest.raises(errors.UniqueViolation):
        retry.run_transaction(FakeConnection(), work)
    assert len(calls) == 1


def test_the_isolation_level_is_set_before_the_transaction_starts():
    conn = FakeConnection()
    retry.run_transaction(conn, lambda c: None, isolation=L.REPEATABLE_READ)
    assert conn.isolation_level == L.REPEATABLE_READ


def test_side_effects_inside_the_function_run_again():
    sent = []

    def work(conn):
        sent.append("email")  # a side effect that a rollback cannot undo
        if len(sent) == 1:
            raise errors.SerializationFailure("simulated")

    retry.run_transaction(FakeConnection(), work)
    assert sent == ["email", "email"]


def test_serializable_with_retries_keeps_the_rule():
    result = workload.run(L.SERIALIZABLE, max_attempts=12)
    assert result["on_call"] == 3
    assert result["outcomes"] == {"left": 5, "stayed": 3}


@pytest.mark.parametrize("level", [L.READ_COMMITTED, L.REPEATABLE_READ])
def test_the_weaker_levels_let_every_request_leave(level):
    assert workload.run(level, max_attempts=1)["on_call"] == 0


def test_an_exclusion_constraint_rejects_overlapping_bookings():
    lab.sql("""
        CREATE EXTENSION IF NOT EXISTS btree_gist;
        DROP TABLE IF EXISTS bookings;
        CREATE TABLE bookings (id serial PRIMARY KEY, room text NOT NULL, during int4range NOT NULL,
            CONSTRAINT no_double_booking EXCLUDE USING gist (room WITH =, during WITH &&));
    """)
    book = "INSERT INTO bookings (room, during) VALUES ('r1', int4range(%s, %s))"
    with lab.connect() as conn:
        conn.execute(book, (9, 11))
        conn.execute(book, (11, 13))  # touching is fine: the upper bound is exclusive
        with pytest.raises(errors.ExclusionViolation):
            conn.execute(book, (10, 12))


def test_the_same_lock_order_avoids_the_deadlock():
    assert workload.lock_pair(opposite=False)["attempts"] == [1, 1]


def test_the_opposite_lock_order_deadlocks_once_and_is_retried():
    assert workload.lock_pair(opposite=True)["attempts"] == [1, 2]
python -m pytest -q
............                                                                                                                                                       [100%]
12 passed in 2.07s

The integration tests pin down the behavior you learned: SERIALIZABLE with retries keeps three doctors on call with outcomes {'left': 5, 'stayed': 3}, the weaker levels let every request leave, the exclusion constraint rejects an overlap but accepts a touching range, and the lock-order tests give [1, 1] and [1, 2]. Now make sure the tests really guard the retry loop. This script copies the lab to a temporary folder, changes one line so that SerializationFailure is no longer retried, and runs the suite again:

# step10_break_it.py
"""step10_break_it.py: copy the lab, make the retry helper ignore serialization failures, run the tests again."""
import os
import re
import shutil
import subprocess
import sys
import tempfile

HERE = os.path.dirname(os.path.abspath(__file__))
copy = tempfile.mkdtemp(prefix="breakit_", dir=os.path.dirname(HERE))
for name in ("lab.py", "retry.py", "workload.py", "test_isolation.py"):
    shutil.copy(os.path.join(HERE, name), copy)

path = os.path.join(copy, "retry.py")
source = open(path, encoding="utf-8").read()
broken = source.replace("RETRYABLE = (errors.SerializationFailure, errors.DeadlockDetected)",
                        "RETRYABLE = (errors.DeadlockDetected,)")
assert broken != source
open(path, "w", encoding="utf-8", newline="\n").write(broken)
print("changed line: RETRYABLE = (errors.DeadlockDetected,)")

result = subprocess.run([sys.executable, "-X", "utf8", "-m", "pytest", "-q", "-p", "no:cacheprovider", "--tb=no"],
                        cwd=copy, capture_output=True, text=True, encoding="utf-8",
                        env=dict(os.environ, COLUMNS="170"))
for line in result.stdout.splitlines():
    if re.match(r"(FAILED|\d+ (failed|passed))", line):
        print(line)
shutil.rmtree(copy, ignore_errors=True)
python step10_break_it.py
changed line: RETRYABLE = (errors.DeadlockDetected,)
FAILED test_isolation.py::test_a_serialization_failure_is_retried_until_it_works - psycopg.errors.SerializationFailure: simulated
FAILED test_isolation.py::test_it_gives_up_after_max_attempts_and_raises_the_last_error - assert 1 == 4
FAILED test_isolation.py::test_side_effects_inside_the_function_run_again - psycopg.errors.SerializationFailure: simulated
FAILED test_isolation.py::test_serializable_with_retries_keeps_the_rule - assert 7 == 3
4 failed, 8 passed in 1.93s

Four tests fail. Three are the unit tests that describe the retry rules. The fourth, test_serializable_with_retries_keeps_the_rule, fails with assert 7 == 3: with the retry gone, seven of the eight requests died with a 40001 error, exactly the 7-on-call result you saw in Step 6. The data would still be correct, but the feature would be broken, and the suite catches it.

Choosing the right fix

Here is the whole tutorial in one table. Start at the top and use the first row that fits your rule.

Your situation Reach for What it costs you
Add to, subtract from or otherwise compute a value in one row One atomic UPDATE ... SET x = x + ... (Step 3) Nothing; concurrent writers briefly wait
A rule about uniqueness or overlap A unique or exclusion constraint (Step 8) A constraint violation to catch and report
Read one row, run application logic, write it back SELECT ... FOR UPDATE (Step 3), or a version check such as the one in our ETag and If-Match tutorial Other sessions wait while you hold the lock
A rule across several rows that you can name and lock FOR UPDATE on those rows, in a fixed order (Step 9) Waiting, and deadlocks if the order varies
Any other rule across rows, or you cannot name the rows SERIALIZABLE plus the retry loop (Steps 5 to 7) Cancelled transactions you must retry, more of them with sequential scans

Common mistakes and how to avoid them

Retrying only the failing statement

After a 40001 error the transaction is dead. Running the same statement again gives 25P02, as Step 3 showed. Retry from BEGIN, including the reads.

Catching the error inside the transaction function

If your work function wraps its statements in a broad try/except Exception, the retry loop never sees the serialization failure, and your code carries on in an aborted transaction. Let the error escape.

Doing irreversible work inside the retried function

Emails, payments and queue messages inside the function run again on every attempt (one of the unit tests proves it). Move them after the commit, or use an outbox table.

Assuming REPEATABLE READ is almost SERIALIZABLE

It stopped the lost update but not write skew, as Step 5 showed. If a rule depends on rows other than the one you write, you need SERIALIZABLE, locks, or a constraint.

Retrying forever, or not at all

Without a limit a broken request can loop for a long time; without retries a correct system returns errors, as the no-retry line in Step 6 showed. Set max_attempts to a small number, add jitter, and report a clear error to the user when the limit is reached.

Leaving transactions open

Locks and snapshots are held until the transaction ends, so a transaction that stays open while your program waits on a network call can block other writers. The PostgreSQL documentation advises: “Don’t put more into a single transaction than needed for integrity purposes.” Keep transactions short.

Check the whole thing end to end

With the server running and the PG* variables set, run each stepNN script in numbered order and compare the output with this article, then run python -m pytest -q. You should see 12 passed. The things that must match exactly are the final balances (120, then three times 130), the doctor counts (0, 0, then 1 with the 40001 error), the final 3 on call, the exclusion violation and the deadlock attempts [1, 2]. The things that will differ are timings and the number of retries.

Where to go next

If you use Django, our select_for_update() and F() expressions tutorial shows the same fixes through the ORM. If your database is SQLite, the WAL mode and BEGIN IMMEDIATE tutorial covers the equivalent problem there. When one operation must change two databases, read our two-phase commit and saga tutorials. For safety at the row level inside PostgreSQL, our row-level security tutorial shows how transaction-scoped settings keep tenants apart. To go deeper on SERIALIZABLE, read the PostgreSQL manual chapter on transaction isolation, the SSI wiki page with its worked examples, and the explicit locking chapter.

Tags:

ConcurrencyDatabasesPostgreSQLPythonSQLTransaction Isolation

Share

No Comment! Be the first one.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts