MVCC and snapshot isolation inside PostgreSQL

Multiversion concurrency control (MVCC) keeps several versions of each row, so that readers never wait for writers and writers never wait for readers. Every transaction reads from a snapshot, the set of changes that had committed at one moment, and picks out the row versions that belong to it. PostgreSQL stores the versions in the table itself and stamps each one with two transaction IDs, xmin for the transaction that created it and xmax for the one that deleted or replaced it. Snapshot isolation inside PostgreSQL rests on those two numbers: a transaction reads one snapshot for all its queries, and when two transactions write the same row, the first to commit wins. That prevents dirty reads, non-repeatable reads and phantoms, but it allows write skew, where two transactions read the same rows and then update different ones.

Row versions on a heap page

A table’s pages hold row versions, and each page has an array of line pointers that index entries point at (PostgreSQL 18: Database Page Layout). An INSERT writes a version with t_xmin set to its transaction. An UPDATE never changes a version in place: it writes a new version and stamps the old one’s t_xmax, and the old version’s t_ctid then points to the new one. A DELETE only stamps t_xmax. The model below follows three statements and prints the columns that the pageinspect extension’s heap_page_items shows (PostgreSQL 18: pageinspect):

"""A PostgreSQL heap page in miniature. INSERT writes a row version stamped with its transaction ID in t_xmin;
UPDATE writes a new version and stamps the old one's t_xmax; DELETE only stamps t_xmax. t_ctid points from a
version to its newer version, or to itself. The page is printed with the columns of pageinspect's
heap_page_items. The transaction IDs are examples: a server uses whatever its counter has reached.
"""


class HeapPage:
    def __init__(self):
        self.items = []                                   # line pointers 1, 2, 3 ... in order

    def insert(self, xid, row):
        self.items.append({"t_xmin": xid, "t_xmax": 0, "t_ctid": len(self.items) + 1, "row": row})

    def latest(self, key):
        """The newest version of the row with this id: the one whose t_ctid points to itself."""
        for lp, item in enumerate(self.items, 1):
            if item["row"]["id"] == key and item["t_ctid"] == lp and item["t_xmax"] == 0:
                return lp, item
        raise KeyError(key)

    def update(self, xid, key, **changes):
        lp, old = self.latest(key)
        self.insert(xid, {**old["row"], **changes})
        old["t_xmax"], old["t_ctid"] = xid, len(self.items)

    def delete(self, xid, key):
        _, old = self.latest(key)
        old["t_xmax"] = xid

    def show(self, title):
        print(title)
        print(" lp | t_xmin | t_xmax | t_ctid | row")
        for lp, item in enumerate(self.items, 1):
            row = ", ".join(f"{k}={v}" for k, v in item["row"].items())
            print(f" {lp:2} | {item['t_xmin']:6} | {item['t_xmax']:6} | (0,{item['t_ctid']})  | {row}")


page = HeapPage()
page.insert(756, {"id": 1, "owner": "Asha", "balance": 500})
page.insert(756, {"id": 2, "owner": "Ravi", "balance": 300})
page.show("after INSERT by transaction 756")
page.update(757, 1, balance=450)
page.show("after UPDATE accounts SET balance = 450 WHERE id = 1, by transaction 757")
page.delete(758, 2)
page.show("after DELETE FROM accounts WHERE id = 2, by transaction 758")

after INSERT by transaction 756
 lp | t_xmin | t_xmax | t_ctid | row
  1 |    756 |      0 | (0,1)  | id=1, owner=Asha, balance=500
  2 |    756 |      0 | (0,2)  | id=2, owner=Ravi, balance=300
after UPDATE accounts SET balance = 450 WHERE id = 1, by transaction 757
 lp | t_xmin | t_xmax | t_ctid | row
  1 |    756 |    757 | (0,3)  | id=1, owner=Asha, balance=500
  2 |    756 |      0 | (0,2)  | id=2, owner=Ravi, balance=300
  3 |    757 |      0 | (0,3)  | id=1, owner=Asha, balance=450
after DELETE FROM accounts WHERE id = 2, by transaction 758
 lp | t_xmin | t_xmax | t_ctid | row
  1 |    756 |    757 | (0,3)  | id=1, owner=Asha, balance=500
  2 |    756 |    758 | (0,2)  | id=2, owner=Ravi, balance=300
  3 |    757 |      0 | (0,3)  | id=1, owner=Asha, balance=450

After the update, the page holds two versions of Asha’s row. The old one, at line pointer 1, still says balance 500 and carries t_xmax 757; the new one, at line pointer 3, says 450. Ravi’s row was not removed by the delete, only stamped with 758. Which version a query gets depends on its snapshot.

Snapshots and the visibility rule

PostgreSQL writes a snapshot as xmin:xmax:xip_list: transactions below xmin had finished, those at or above xmax had not started yet, and xip_list names those still running in between (PostgreSQL 18: System Information Functions). A version is visible to a snapshot when its creator committed before the snapshot, or is the reading transaction itself, and its deleter, if it has one, did not commit before the snapshot:

"""Which row version does each snapshot see? A snapshot is written xmin:xmax:xip_list, as PostgreSQL prints it:
transactions below xmin had finished, those at or above xmax had not started, and xip_list names the ones still
running in between. A version is visible when the transaction that created it (t_xmin) committed before the
snapshot, and the one that deleted or replaced it (t_xmax), if any, did not.
"""

versions = [                                  # Asha's row: two versions, as on the heap page
    {"t_xmin": 756, "t_xmax": 760, "balance": 450},
    {"t_xmin": 760, "t_xmax": 0, "balance": 400},
]
status = {756: "committed", 760: "in progress", 761: "committed"}   # 761 started after 760 and has finished


def finished_before(xid, snap):
    xmin, xmax, xip = snap
    return xid < xmin or (xid < xmax and xid not in xip)


def visible(v, snap, me=None):
    created = v["t_xmin"] == me or (status.get(v["t_xmin"]) == "committed" and finished_before(v["t_xmin"], snap))
    gone = v["t_xmax"] != 0 and (v["t_xmax"] == me or (status.get(v["t_xmax"]) == "committed"
                                                        and finished_before(v["t_xmax"], snap)))
    return created and not gone


def balance(snap, me=None):
    seen = [v["balance"] for v in versions if visible(v, snap, me)]
    return seen[0] if seen else None


def fmt(snap):
    return f"{snap[0]}:{snap[1]}:{','.join(map(str, snap[2]))}"


print("transaction 760 has changed Asha's balance from 450 to 400 and not committed yet;")
print("transaction 761, which started after it, has already committed")
print(f"  760 itself reads {balance((760, 762, [760]), me=760)}")
first = (760, 762, [760])
print(f"  R (REPEATABLE READ) and C (READ COMMITTED) both take snapshot {fmt(first)} and read {balance(first)}")
status[760] = "committed"
print("transaction 760 commits")
print(f"  R reads again with its snapshot {fmt(first)}: {balance(first)}")
second = (762, 762, [])
print(f"  C's next statement takes snapshot {fmt(second)} and reads {balance(second)}")

transaction 760 has changed Asha's balance from 450 to 400 and not committed yet;
transaction 761, which started after it, has already committed
  760 itself reads 400
  R (REPEATABLE READ) and C (READ COMMITTED) both take snapshot 760:762:760 and read 450
transaction 760 commits
  R reads again with its snapshot 760:762:760: 450
  C's next statement takes snapshot 762:762: and reads 400

Both readers saw 450 while 760 was uncommitted. After 760 committed, R still saw 450, because REPEATABLE READ keeps the snapshot of the transaction’s first statement for every query; C saw 400, because READ COMMITTED takes a new snapshot for each statement (PostgreSQL 18: Transaction Isolation). That is snapshot isolation inside PostgreSQL: its REPEATABLE READ level is snapshot isolation, as the manual says.

What snapshot isolation prevents

• Dirty reads are prevented: uncommitted versions are never in another transaction’s snapshot.
• Non-repeatable reads are prevented: every query reads the same snapshot.
• Phantoms are prevented: rows inserted after the snapshot are invisible to it.
• Lost updates are prevented: the second writer of a row fails if the first committed, “first committer wins”.
• Write skew is allowed: two transactions that update different rows never collide.

Berenson and colleagues first defined snapshot isolation and showed that it is weaker than serializable (A Critique of ANSI SQL Isolation Levels). Write skew can be closed by locking the rows a transaction reads, by making the transactions update one shared row, or by running at SERIALIZABLE. The cost of versions is clean-up. VACUUM removes versions that no snapshot can see any more. A HOT update keeps a new version on the same page and skips index changes when no indexed column changes. Freezing marks old versions as visible to everyone, before 32-bit transaction IDs wrap around. MySQL’s InnoDB keeps the current row in place and rebuilds older versions from undo logs, which its purge removes (MySQL 8.4: InnoDB Multi-Versioning).

Exercise

"""Whether a row version is visible to a snapshot, by PostgreSQL's rules.

version: {"t_xmin": creator, "t_xmax": deleter or replacer, 0 if none}
snapshot: (xmin, xmax, xip)    status: {xid: "committed" | "aborted" | "in progress"}    me: the reader
"""


def finished_before(xid, snapshot):
    """Whether transaction xid had finished when the snapshot was taken."""
    return False


def visible(version, snapshot, status, me):
    return False

from visibility import visible

SNAP = (900, 905, [900, 903])


def test_own_uncommitted_insert():
    """A transaction sees its own insert before it commits"""
    assert visible({"t_xmin": 900, "t_xmax": 0}, SNAP, {900: "in progress"}, me=900) is True


def test_someone_elses_uncommitted_insert():
    """Another transaction's uncommitted insert is invisible"""
    assert visible({"t_xmin": 900, "t_xmax": 0}, SNAP, {900: "in progress"}, me=901) is False


def test_committed_before_the_snapshot():
    """A version committed before the snapshot is visible"""
    assert visible({"t_xmin": 890, "t_xmax": 0}, SNAP, {890: "committed"}, me=904) is True


def test_finished_between_xmin_and_xmax():
    """A creator between xmin and xmax that is not in the running list had finished: visible"""
    assert visible({"t_xmin": 901, "t_xmax": 0}, SNAP, {901: "committed"}, me=904) is True


def test_committed_after_the_snapshot():
    """A creator that was still running when the snapshot was taken stays invisible after it commits"""
    assert visible({"t_xmin": 903, "t_xmax": 0}, SNAP, {903: "committed"}, me=904) is False
    assert visible({"t_xmin": 906, "t_xmax": 0}, SNAP, {906: "committed"}, me=904) is False


def test_deleted_before_the_snapshot():
    """A version deleted by a transaction that committed before the snapshot is gone"""
    assert visible({"t_xmin": 890, "t_xmax": 899}, SNAP, {890: "committed", 899: "committed"}, me=904) is False


def test_delete_by_an_aborted_transaction():
    """An aborted delete does not hide the version"""
    assert visible({"t_xmin": 890, "t_xmax": 902}, SNAP, {890: "committed", 902: "aborted"}, me=904) is True


def test_delete_after_the_snapshot():
    """A delete that committed after the snapshot leaves the version visible to it"""
    assert visible({"t_xmin": 890, "t_xmax": 903}, SNAP, {890: "committed", 903: "committed"}, me=904) is True


def test_own_delete():
    """A transaction no longer sees a version it deleted itself"""
    assert visible({"t_xmin": 890, "t_xmax": 904}, SNAP, {890: "committed", 904: "in progress"}, me=904) is False

Inside the heap: HOT chains, the vacuum horizon and freezing, with numbers Pro: opens with a Pro, Premium or Ultimate pass

Write skew, its fixes, and how InnoDB keeps versions instead Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• MVCC keeps several versions of each row so that reads and writes do not block each other; PostgreSQL stamps each version with xmin and xmax and chains old versions to new ones through t_ctid.
• A snapshot xmin:xmax:xip_list decides which versions a query sees: REPEATABLE READ keeps one snapshot per transaction, READ COMMITTED takes one per statement.
• Snapshot isolation prevents dirty reads, non-repeatable reads, phantoms and lost updates, but allows write skew.
• VACUUM, HOT updates and freezing keep PostgreSQL’s versions in check; InnoDB keeps old versions in undo logs.