Transactions and Concurrency

Understanding MVCC

In the previous chapter, you saw that multiple transactions can work with the same data at the same time.

To handle this concurrency efficiently, PostgreSQL uses Multi-Version Concurrency Control, commonly called MVCC.

The core idea is that PostgreSQL can maintain multiple versions of the same logical row.

Let's see why that's useful.

What happens when PostgreSQL updates a row?

You might imagine that an UPDATE simply finds the existing row and overwrites its values.

That's not how PostgreSQL works.

When PostgreSQL updates a row, it creates a new version of that row rather than simply overwriting the existing version.

Conceptually:

For some period of time, both versions can exist inside PostgreSQL.

Which version a transaction sees depends on which version is visible to it.

Observing a row version

Let's inspect a small part of this behavior.

Type the following statement into the SQL editor and run it:

PostgreSQL stores some internal information with every row version.

One of those values is xmin.

xmin identifies the transaction that created the row version you're currently seeing.

Run:

You'll see the order together with an xmin value.

Now update the order:

Run the query again:

The order now has a status of shipped, and you should see a different xmin.

The UPDATE created a new version of the row, and that version was created by a different transaction.

You don't normally use xmin in application code. We're inspecting it only to understand what PostgreSQL is doing internally.

Why are multiple versions useful?

Here's where MVCC becomes useful.

The following example is conceptual. You don't need to type it into the SQL editor.

Suppose an order currently has:

Transaction A updates it:

The newer shipped version exists, but Transaction A hasn't committed it.

Now Transaction B reads the same row:

Transaction B isn't blocked simply because Transaction A has produced a newer version.

It can still read the previous committed version of the row.

The additional material demonstrates this behavior directly: one transaction updates a value without committing, while another transaction can still read the previous committed value rather than seeing the uncommitted change.

This is one of the major benefits of MVCC:

Readers don't normally have to wait for writers simply to read previously committed data.

How does PostgreSQL know which version to show?

PostgreSQL uses something called a snapshot.

A snapshot represents the database state that an operation is allowed to see.

When PostgreSQL reads a row, it uses information about the available row versions and the current snapshot to determine which version is visible.

The important idea is:

A row version can exist in PostgreSQL without being visible to every transaction.

Exactly when PostgreSQL establishes and uses snapshots depends on the transaction's isolation level.

We'll look at that in the next chapter.

MVCC doesn't eliminate locks

MVCC avoids a lot of unnecessary blocking, but it doesn't mean PostgreSQL never needs locks.

Suppose two transactions try to update the same row.

Conceptually:

Unlike a reader, a second writer can't simply modify another version independently and ignore the first writer.

Conflicting writes have to be coordinated, so one transaction may need to wait for another.

So MVCC and locking work together.

MVCC allows transactions to read appropriate row versions without blocking unnecessarily, while locks handle conflicting changes.

What you need to remember

The core ideas are:

An UPDATE creates a new row version rather than simply overwriting the existing version.
Multiple versions of the same logical row can exist.
PostgreSQL uses snapshots to determine which versions are visible.
A reader can often continue reading committed data while another transaction has an uncommitted update.
Conflicting writes can still require locks.

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