Transactions and Concurrency

Understanding MVCC

In the previous chapter, you learned how to group multiple SQL statements inside a transaction and then either commit or roll back their changes.

An ecommerce application can have many customers using it at the same time. One customer might be placing an order while another cancels an order and someone else updates their account.

These actions can cause PostgreSQL to run multiple transactions at the same time. Some of those transactions may try to read or modify the same data.

PostgreSQL therefore needs to determine which changes each transaction is allowed to see.

PostgreSQL handles this using Multi-Version Concurrency Control, commonly called MVCC.

MVCC is PostgreSQL's system for deciding which version of a row each SQL statement is allowed to see.

Let's break down what that means.

What does multi-version mean?

When you use an UPDATE statement to change a row, you might think PostgreSQL finds the existing row and overwrites its values.

That isn't how PostgreSQL works.

When PostgreSQL updates a row, it creates a new version of that row. The previous version can remain stored alongside the new version.

Before the update, PostgreSQL has:

After the update, PostgreSQL can have:

These are two physical row versions representing the same logical order.

Which version PostgreSQL shows depends on which version is visible to the current operation.

Let's observe this behavior.

Create an order

Create an order for customer 4:

You should see INSERT 0 1.

Now inspect the order:

You'll see the order with a status of pending.

You'll also see a value in the xmin column.

xmin is a system column maintained by PostgreSQL. It identifies the transaction that created the row version you're currently seeing.

The exact xmin value isn't important. We'll use it only to observe when PostgreSQL creates a new row version.

Update the order inside a transaction

Start a transaction:

Now update the order:

You should see UPDATE 1.

The transaction is still open, so this change hasn't been committed.

Inspect the order again:

You'll now see:

You'll also see a different xmin value.

The UPDATE created a new version of the row. The current transaction can see that version even though it hasn't been committed.

PostgreSQL can now have:

Roll back the new version

Now roll back the transaction:

Inspect the order again:

You'll once again see:

The xmin should also be the original value you saw before the update.

The shipped version was created by a transaction that was rolled back, so PostgreSQL doesn't consider that version visible. The earlier committed version is still the visible version.

This demonstrates the multi-version part of MVCC:

PostgreSQL can store multiple versions of the same logical row instead of immediately overwriting the previous version.

What does concurrency control mean?

Maintaining multiple row versions is only one part of MVCC.

PostgreSQL must also decide which version each operation is allowed to see. It does this using a snapshot.

A snapshot isn't a complete copy of the database. It's information PostgreSQL uses to determine which transaction changes are visible to an operation.

When PostgreSQL reads a row, it considers:

the available versions of that row;
the transactions that created or changed those versions;
whether those transactions committed, rolled back, or are still running;
the snapshot being used by the current operation.

Using this information, PostgreSQL chooses the appropriate visible version.

Reading while another transaction updates a row

Suppose the committed version of an order contains:

Transaction A updates the order but doesn't commit:

While Transaction A remains open, Transaction B runs a normal SELECT:

Transaction B doesn't see the uncommitted shipped version.

Instead, PostgreSQL shows Transaction B the earlier committed pending version.

Transaction B also doesn't normally need to wait for Transaction A to finish before reading that committed version.

This is the main practical benefit of MVCC:

A normal SELECT can usually read committed data without waiting for a concurrent transaction that is updating the same row.

It also prevents Transaction B from seeing Transaction A's incomplete changes.

MVCC doesn't mean PostgreSQL never uses locks

MVCC determines which row version an operation can see, but some operations still need to be coordinated.

If two transactions try to update the same row, PostgreSQL can't allow both writers to proceed independently. One transaction may need to wait for the other because updates acquire row-level locks.

We'll examine this behavior later when we learn how to prevent race conditions.

For now, remember the distinction:

MVCC determines which row version is visible.
Locks coordinate conflicting operations.

What you need to remember

The core ideas are:

An UPDATE creates a new row version instead of simply overwriting the existing version.
Multiple versions of the same logical row can exist inside PostgreSQL.
PostgreSQL uses snapshots and transaction information to determine which version is visible.
A transaction can see its own uncommitted changes.
Other transactions can't see those changes until they're committed.
A normal SELECT can usually read a committed version without waiting for a concurrent writer.

Exactly when PostgreSQL creates a snapshot and how long that snapshot is used depends on the transaction's isolation level.

In the next chapter, you'll learn how PostgreSQL's default isolation level, READ COMMITTED, determines what each statement can see.