Exercise 01 of 3/Foundations/40 minutes

The balance that was never real

Two database sessions, one uncommitted transaction, and a report that prints a number no transaction ever agreed to. Then the discovery that on Postgres you cannot make this bug happen at all — and why that is more interesting than if you could.

Almost every database you have shipped against was running at an isolation level that permits your application to be wrong, and almost nobody who picked that level could list the anomalies it allows.

The definitions are not hard. They are just abstract enough that reading them does not stick. So we are not going to read them — we are going to cause each anomaly on purpose, starting with the one the standard considers worst.

Get two engines running

Set this up first

Both engines, in containers, on ports that will not collide with anything you already run:

docker run --rm -d --name iso-pg -e POSTGRES_PASSWORD=secret -p 5440:5432 postgres:16
docker run --rm -d --name iso-my -e MYSQL_ROOT_PASSWORD=secret -p 3307:3306 mysql:8

Open a session in a terminal — you will need two terminals per engine, and the whole exercise depends on them being genuinely separate connections:

docker exec -it iso-pg psql -U postgres          # PostgreSQL
docker exec -it iso-my mysql -uroot -psecret     # MySQL

The table. The DDL is not the same, which is the first small lesson: MySQL cannot index a TEXT column without a prefix length, so id TEXT PRIMARY KEY fails there with ERROR 1170 (42000).

-- PostgreSQL
CREATE TABLE accounts (id TEXT PRIMARY KEY, balance BIGINT NOT NULL);
INSERT INTO accounts VALUES ('alice', 1000), ('bob', 1000);
-- MySQL
CREATE DATABASE iso; USE iso;
CREATE TABLE accounts (id VARCHAR(64) PRIMARY KEY, balance BIGINT NOT NULL);
INSERT INTO accounts VALUES ('alice', 1000), ('bob', 1000);

When you are done: docker rm -f iso-pg iso-my.

The CREATE TABLE is not the same for both engines. Why not?

AnswerCommit to one first

MySQL cannot index a TEXT column without a prefix length, so id TEXT PRIMARY KEY fails outright:

ERROR 1170 (42000): BLOB/TEXT column 'id' used in key specification without a key length

Postgres accepts it. This is the first and smallest example of the thing this whole track is about: two engines, the same SQL, different behaviour — and the difference is not documented anywhere you were looking.

Cause a dirty read on MySQL

Do this

In session A: begin a transaction, add 1,000,000 to alice, and do not commit. In session B at READ UNCOMMITTED: read alice's balance. Then roll A back and read again.

Session A (BEGIN on Postgres, START TRANSACTION on MySQL):

BEGIN;   -- MySQL: START TRANSACTION;
UPDATE accounts SET balance = balance + 1000000 WHERE id = 'alice';
-- stop here. do not commit. leave this terminal alone.

Session B, on PostgreSQL:

BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;   -- read uncommitted
SELECT balance FROM accounts WHERE id = 'alice';

Note the form. SET TRANSACTION ISOLATION LEVEL ... on its own line before BEGIN does nothing at all — Postgres answers WARNING: SET TRANSACTION can only be used in transaction blocks and leaves you at the default level. Set it on the BEGIN, or use SET SESSION CHARACTERISTICS.

Session B, on MySQL — autocommit is on by default, so the transaction has to be started explicitly, and the level is set on the session before it:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@transaction_isolation;   -- READ-UNCOMMITTED
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 'alice';

On MySQL with InnoDB, B prints 1001000. That number is not a fact. It is A's private work-in-progress, and when A rolls back it ceases to have ever been true. If B was a settlement report, it has now reported money that does not exist, and there is no audit trail explaining where the figure came from — the row it read no longer exists in any version of the database.

On PostgreSQL, B prints 1000.

Step through both engines

MySQL at READ UNCOMMITTED — step through it

alice.balance (committed)1000
Session A
Session B
START TRANSACTION;
UPDATE accounts SET balance = balance + 1000000 WHERE id = 'alice';
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id='alice';→ 1001000
ROLLBACK;
SELECT balance FROM accounts WHERE id='alice';→ 1000

 

Captured from mysql:8. B reads a number that no committed state of the database ever held.

PostgreSQL, the identical sequence

alice.balance (committed)1000
Session A
Session B
BEGIN;
UPDATE accounts SET balance = balance + 1000000 WHERE id = 'alice';
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;→ read uncommitted
SELECT balance FROM accounts WHERE id='alice';→ 1000
ROLLBACK;

 

Captured from postgres:16. Same steps, and the dirty read cannot be produced.

Work out why Postgres cannot do it

You asked Postgres for READ UNCOMMITTED and it said yes. Why did nothing change?

AnswerCommit to one first

Because the SQL standard defines each level by the anomalies it must prevent, not the ones it must allow. An engine is free to prevent more.

Postgres prevents dirty reads at every level, so READ UNCOMMITTED and READ COMMITTED are the same mode with two names. It reports back the level you asked for and behaves as the stronger one.

Is that a policy decision, or could Postgres implement it if it wanted to?

AnswerCommit to one first

It is structural, not a choice.

Postgres MVCC tags each row version with the transaction that created it, and a reader's snapshot tests visibility by asking whether that transaction has committed. An uncommitted version fails that test by construction.

There is no code path that returns it. There is nothing to switch off.

Hint 1Postgres did not ignore you, exactly

Check what level you are actually in after setting it: SHOW transaction_isolation;. The answer will surprise you, and it is documented behaviour rather than a bug.

Hint 2Ask what a dirty read would require of the storage engine

For B to see A's uncommitted row, B has to be able to read a version of the row that is not committed. Think about what Postgres's MVCC actually stores in the heap, and what a reader's snapshot is allowed to consider visible.

Solution — what each engine did, and whyTry it first

PostgreSQL accepted your statement and gave you a stronger level anyway.

BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;
--  read uncommitted
SELECT balance FROM accounts WHERE id = 'alice';   -- 1000

Postgres reports the level you asked for, and behaves as READ COMMITTED. This is explicitly permitted: the SQL standard defines isolation levels by the anomalies they must prevent, not by the ones they must allow. An engine is free to prevent more. Postgres prevents dirty reads at every level, so READ UNCOMMITTED and READ COMMITTED are the same mode with two names.

It is not being polite — it is structural. Postgres MVCC keeps each row version in the heap tagged with the transaction that created it (xmin) and the one that deleted it (xmax). A reader's snapshot tests visibility by asking whether xmin belongs to a transaction that has committed. An uncommitted version fails that test by construction. There is no code path that returns it, so there is nothing to implement and nothing to switch off.

MySQL/InnoDB implements it for real. InnoDB also uses MVCC, but at READ UNCOMMITTED it skips consistent-read and reads the latest row version in the buffer pool regardless of the writing transaction's state. So you get 1001000, and after A's rollback a re-read gives 1000 — the same query, two answers, neither wrong, in one transaction.

Two things worth having explicitly:

The four levels are a list of permitted anomalies, not a list of features.

| Level | Dirty read | Non-repeatable read | Phantom | |---|---|---|---| | READ UNCOMMITTED | permitted | permitted | permitted | | READ COMMITTED | prevented | permitted | permitted | | REPEATABLE READ | prevented | prevented | permitted | | SERIALIZABLE | prevented | prevented | prevented |

Read the table as a floor, not a specification. Postgres's REPEATABLE READ also prevents phantoms; MySQL's prevents them for plain reads but not for locking ones; neither is violating anything. The level name tells you what you are guaranteed, never what you will get — which is why the rest of this track tests behaviour rather than trusting names.

READ UNCOMMITTED has essentially no legitimate use. It is sometimes reached for as a performance trick, usually in the form of SQL Server's WITH (NOLOCK), on the theory that skipping read locks makes reports faster. On an MVCC engine readers do not block writers anyway, so there is nothing to win and the cost is reading rows that were never real. If you find it in a codebase, it is nearly always someone working around lock contention that had a different, real cause.

Find out what you are actually running

What isolation level is your production database using right now?

AnswerCommit to one first

Almost certainly not the one you assume, and almost certainly not SERIALIZABLE:

  • PostgreSQL defaults to READ COMMITTED.
  • MySQL/InnoDB defaults to the stronger-sounding REPEATABLE READ, which is subtler rather than safer — exercise 06 is entirely about how.
  • SQL Server defaults to READ COMMITTED using locks rather than MVCC, so readers and writers block each other.

Most ORMs set no level at all and inherit the engine default. Some set one per connection pool, which means the level depends on which pool served the request. Go and check rather than assuming.

How real systems choose, and what they actually run

The defaults matter more than the theory, because almost nobody changes them.

PostgreSQL — default READ COMMITTED

Dirty reads impossible at any level. READ UNCOMMITTED is an alias for READ COMMITTED. REPEATABLE READ is true snapshot isolation and also blocks phantoms; SERIALIZABLE adds Serializable Snapshot Isolation, which detects conflicts and aborts a transaction with a serialization failure rather than blocking it — so an application running it must be able to retry. This is the engine whose level names most nearly mean what you would guess.

MySQL / InnoDB — default REPEATABLE READ

The only one of these that implements READ UNCOMMITTED as written. Its default is the stronger REPEATABLE READ, which sounds safer than Postgres's default and is subtler: consistent reads come from a snapshot taken at first read, while SELECT ... FOR UPDATE and UPDATE read the latest committed row instead. A transaction can therefore see one value with a plain SELECT and a different one with a locking read — the famous MySQL surprise, and exercise 06's entire subject.

Oracle and SQL Server

Oracle offers only READ COMMITTED and SERIALIZABLE; asking for READ UNCOMMITTED is an error rather than a silent upgrade. SQL Server defaults to READ COMMITTED using locks rather than MVCC, so readers and writers block each other — which is why WITH (NOLOCK) became folklore in that ecosystem, and why enabling READ_COMMITTED_SNAPSHOT is usually the better answer.

What this means in practice. Two engines, both at their defaults, give your application different guarantees, and neither default is SERIALIZABLE — because serializable costs either blocking or retries and vendors choose throughput. The honest summary is that your production system almost certainly permits anomalies, and the engineering question is never "am I isolated" but "which anomalies am I exposed to, and which of them can my business tolerate". That is what the next four exercises establish, one anomaly at a time.

Before you move on

You should be able to explain each of these without looking.

  • Why SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED appears to work on Postgres and changes nothing.
  • Why a dirty read is structurally impossible under Postgres's snapshot visibility rules.
  • Why the standard's table is a floor and not a specification.
  • The default level of the engine you use at work, and the anomalies it permits.

Go further

  • Re-run the sequence with A doing a DELETE rather than an UPDATE, then rolling back. On MySQL at READ UNCOMMITTED, B briefly observes a row count that no committed state ever had — worse than a wrong balance, because an aggregate hides which row was involved.
  • Have B compute SUM(balance) across both accounts while A has a half-finished transfer open. This is the dirty read as it actually appears in production: not a wrong row, but a total that fails to reconcile.
  • Find the isolation level your ORM sets. Most set none and inherit the engine default; some set one per connection pool, meaning the level depends on which pool served the request. Go and check rather than assuming.