Transaction Isolation Levels — Sandbox

A Docker-based playground for exploring transaction isolation levels in PostgreSQL 16 and MS SQL Server 2022 — side by side, with runnable scenarios for every classic concurrency anomaly.

---

What's Inside

Service	Image	Port
PostgreSQL 16	postgres:16	5432
MS SQL Server 2022 Express	mcr.microsoft.com/mssql/server:2022-latest	1433
CloudBeaver (web SQL IDE)	dbeaver/cloudbeaver:latest	8978

Both databases are pre-initialized with the same sample schema and seed data. CloudBeaver connects to both from a single browser UI — open two query tabs side by side to simulate concurrent sessions.

---

Getting Started

Prerequisites: Docker Desktop (or Docker Engine + Compose plugin).

git clone https://github.com/axltweek/TransactionIsolationLevels.git
cd TransactionIsolationLevels

# Copy the example env file — defaults work out of the box
cp .env.example .env

docker compose up -d

Wait about 45 seconds for SQL Server to initialize, then verify:

docker compose logs mssql-init
# Should end with: "MSSQL initialized successfully."

---

Configuration

All credentials and ports live in .env (copied from .env.example). The defaults are fine for local use:

Variable	Default	Purpose
POSTGRES_PASSWORD	demo123	PostgreSQL demo user password
MSSQL_SA_PASSWORD	Demo@123456	SQL Server SA password
CB_SERVER_NAME	TransactionIsolationLevels	CloudBeaver server name
CB_ADMIN_NAME	cbadmin	CloudBeaver admin login (created on first launch)
CB_ADMIN_PASSWORD	CbAdmin123	CloudBeaver admin password (min 8 chars, 1 digit, mixed case)
POSTGRES_PORT	5432	Host port for PostgreSQL
MSSQL_PORT	1433	Host port for SQL Server
CB_PORT	8978	Host port for CloudBeaver

Change ports in .env if they conflict with something already running on your machine — no other files need updating.

---

Connecting via CloudBeaver

Open http://localhost:8978 in your browser. The workspace comes pre-provisioned:

• Admin account is already created — log in with the credentials from your .env (CB_ADMIN_NAME / CB_ADMIN_PASSWORD, default cbadmin / CbAdmin123). CloudBeaver reads these env vars and creates the admin on first launch — no setup wizard. Applies only to a fresh workspace — see the note below.
• Both connections are already listed in the toolbar (PostgreSQL (isolation_demo) and SQL Server (isolation_demo)), defined in cloudbeaver/data-sources.json. You don't create them.
• Scenario scripts are already in the Resource Manager, under Scripts/postgres/ and Scripts/mssql/.

The one thing that cannot be pre-shipped is the database password — CloudBeaver stores saved passwords encrypted per-workspace, so they aren't portable across a clone. The first time you open each connection, enter the password once (it then persists, because save-password is on):

• PostgreSQL — User demo, Password demo123
• SQL Server — User sa, Password Demo@123456

Note — pre-provisioning applies to a fresh workspace. The admin (CB_ADMIN_* env vars) and connection definitions are loaded when CloudBeaver first initializes its internal database. If you already created an admin via the old wizard, wipe data/cloudbeaver/ (or run a clean clone) for the env-var admin to take effect.

Persisting across restarts: use docker compose stop / start to pause and resume — the data/cloudbeaver workspace (including your entered passwords) is preserved. docker compose down keeps it too (it's a bind mount, not a named volume); only deleting data/cloudbeaver/ resets it.

---

Running Scenario Scripts

Each scenario is a folder containing two scripts that run as two genuinely concurrent sessions:

• session_a.sql — the observer (the "master" session, where you watch the anomaly's effect or its absence)
• session_b.sql — the creator (the concurrent transaction that interferes, and which restores the original data at the end — scenarios are self-resetting)

Folders ending in _demo show the anomaly happening; the matching _fix folder shows a higher isolation level (or locking clause) preventing it.

Critical: run the two scripts on two DIFFERENT connections. Each database has a Session A and a Session B connection (e.g. SQL Server — Session A / — Session B). CloudBeaver shares one database session across all SQL tabs of the same connection — so two tabs on one connection run serially, not concurrently, and every demo silently fails (you'll just see the committed value, and the two scripts' run times add up instead of overlapping). Using the A and B connections guarantees two real, independent sessions.

To run a scenario:

1. In the Resource Manager panel, expand Scripts/postgres/<folder>/ or Scripts/mssql/<folder>/.
2. Open session_a.sql and set its tab's connection to … — Session A.
3. Open session_b.sql and set its tab's connection to … — Session B.
4. Run all of session_a.sql first (Execute Script), then immediately run all of session_b.sql in its tab.

The WAITFOR DELAY (SQL Server) / pg_sleep() (PostgreSQL) calls interleave the two sessions on a timer, so you don't have to click with split-second precision — you have a few seconds to switch tabs. A run takes about 5–8 seconds.

Why two files + two connections? Real concurrency needs two independent database sessions. Two files keep the sessions separate; two connections are what actually give you two sessions (two tabs on one connection share a session and serialize). Running both blocks in a single tab would instead nest them into one transaction — also broken.

SQL Server RCSI demo (mssql/05_rcsi_demo) toggles a database-level setting that drops other connections, so it has extra steps: run 00_enable_rcsi.sql alone first, then session_a.sql / session_b.sql, then 99_disable_rcsi.sql when done (so the other scenarios keep their default lock-based behavior). See scenarios/README.md.

---

Sample Schema

Both databases contain the same four tables:

accounts  — owner + balance  (dirty read, non-repeatable read, lost update demos)
products  — name + price     (non-repeatable read demo)
orders    — FK to products   (background data)
on_call   — doctor + shift   (write skew demo)

Seed data: Alice (10,000), Bob (5,000), Carol (7,500) in accounts; four products; two doctors on call today.

---

Scenarios

Each row is a folder holding session_a.sql + session_b.sql.

PostgreSQL

Folder	What it shows
postgres/01_dirty_read_demo/	PostgreSQL refuses dirty reads even at READ UNCOMMITTED — observer reads only committed data
postgres/02_nonrepeatable_read_demo/ · _fix/	A value changes between two reads (READ COMMITTED) → prevented at REPEATABLE READ
postgres/03_phantom_read_demo/ · _fix/	A new row appears in a re-run query (READ COMMITTED) → prevented at REPEATABLE READ (PG exceeds the standard)
postgres/04_lost_update_demo/	One update silently overwrites another (read-modify-write race)
postgres/04_lost_update_fix_for_update/	Pessimistic fix: SELECT … FOR UPDATE locks the row; the loser waits, then re-reads
postgres/04_lost_update_fix_repeatable_read/	Optimistic fix: REPEATABLE READ aborts the loser with a serialization error (40001) — no locking clause
postgres/05_write_skew_demo/ · _fix/	Two non-conflicting writes break an invariant (REPEATABLE READ) → SERIALIZABLE/SSI aborts one

MS SQL Server

Folder	What it shows
mssql/01_dirty_read_demo/ · _fix/	WITH (NOLOCK) reads an uncommitted value → default READ COMMITTED blocks instead
mssql/02_nonrepeatable_read_demo/ · _fix/	A value changes between two reads (READ COMMITTED) → prevented at REPEATABLE READ
mssql/03_phantom_read_demo/ · _fix/	A new row appears at REPEATABLE READ → blocked by range locks at SERIALIZABLE
mssql/04_snapshot_isolation_demo/	SNAPSHOT gives a consistent transaction-start view; writers don't block readers
mssql/04_snapshot_update_conflict/	Two SNAPSHOT writers on one row → the second aborts (Msg 3960)
mssql/05_rcsi_demo/	RCSI: a reader is served the last committed version and never blocks on a writer
mssql/06_lost_update_demo/ · _fix/	One update silently overwrites another → prevented with WITH (UPDLOCK) (SQL Server's FOR UPDATE)
mssql/07_write_skew_demo/ · _fix/	Two non-conflicting writes break an invariant → SERIALIZABLE prevents it via a deadlock (Msg 1205), not an SSI abort

---

Tear Down

# Stop containers, keep all data intact
docker compose stop

# Remove containers (data survives — it's in ./data/)
docker compose down

# Full reset — wipe all data and start fresh
docker compose down && rm -rf data/postgres/* data/mssql/* data/cloudbeaver/*

Data is stored in ./data/ as bind mounts, not Docker named volumes, so docker compose down -v does nothing special here — -v only removes named volumes.

---

Notes

• SQL Server uses Demo@123456 as the SA password — meets the complexity requirement for the image.
• The mssql-init container runs once and exits (restart: no). If it fails, check docker compose logs mssql-init. To re-run: docker compose up mssql-init. The init script (init/mssql/01_init.sql) is idempotent — tables are created only if missing and seed rows inserted only when empty, so re-running never duplicates data. (PostgreSQL's init runs only when data/postgres is empty, so schema changes there require wiping that folder.)
• CloudBeaver scripts are bind-mounted straight into the Resource Manager (scenarios/postgres, scenarios/mssql → …/Scripts/), so there's no separate init container. The admin is created from the CB_ADMIN_NAME / CB_ADMIN_PASSWORD env vars (in .env), and the connection topology is mounted read-only from cloudbeaver/data-sources.json; if CloudBeaver ever logs a write error on that file, drop the :ro flag on its mount.
• Snapshot Isolation's one-time ALTER DATABASE … ALLOW_SNAPSHOT_ISOLATION ON is included inline in its scenario (safe to re-run). RCSI's toggle drops connections, so it lives in separate 00_enable_rcsi.sql / 99_disable_rcsi.sql files — run them around the demo and turn RCSI back off afterward.
• Tested on Docker Desktop for Windows and macOS. On Apple Silicon add platform: linux/amd64 to the mssql and mssql-init services if the image fails to pull.