SQL / TRANSACTIONS AND CONCURRENCY
Choosing an isolation level for real workloads
Pick the lowest isolation level that still protects each transaction's invariant, and know when a guarded UPDATE beats raising the level.
What you will learn
- Find out what level a code path runs under before assuming; engine defaults differ.
- Set the level in BEGIN for one transaction rather than server-wide.
- Replace read-then-write pairs with one guarded UPDATE and check the row count.
- Retry SQLSTATE 40001 and keep side effects outside SERIALIZABLE transactions.
Understanding Choosing an isolation level for real workloads
The default level is an engine decision, not a standard one: PostgreSQL and Oracle give you READ COMMITTED, InnoDB gives you REPEATABLE READ, and SQL Server gives a lock-based READ COMMITTED unless READ_COMMITTED_SNAPSHOT is turned on. So the first move on a real workload is to find out what the code path already runs under, then justify any transaction that needs something stronger, the way you justify an index. Levels are settable per transaction for a reason: a checkout that touches three rows and a report that scans a million have nothing in common except the connection they arrived on.
The question that decides the level is whether the transaction writes based on something it read. If the whole condition fits in the WHERE clause of the write itself, such as on_hand >= 2, remaining > 0, or held_by IS NULL, then READ COMMITTED is enough: the engine takes the row lock and re-evaluates that condition against the committed row, so the loser updates zero rows. You need SERIALIZABLE when the invariant spans rows the statement never touches, as when two doctors each cancel a shift after seeing the other still on call, because no single-row guard can observe that. The cheaper middle option is to lock the row you are reasoning about with SELECT ... FOR UPDATE and stay where you are.
Raising the level does not delete concurrency work, it relocates it into your error handling. PostgreSQL's SERIALIZABLE aborts one side with SQLSTATE 40001, while MySQL's turns plain SELECTs into shared locking reads, so the bill arrives as retries in one engine and as lock waits and deadlocks in the other. That trade only pays off when the transaction is short and has no external side effects before COMMIT: a transaction that emails the customer halfway through cannot be retried, and one that waits on user input holds a stale snapshot and loses more conflicts than it needs to.
-- PostgreSQL
CREATE TABLE inventory (sku text PRIMARY KEY, on_hand int);
INSERT INTO inventory VALUES ('kbd-01', 3);
-- Order path: the whole invariant fits in one statement, so READ COMMITTED is enough.
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE inventory SET on_hand = on_hand - 2
WHERE sku = 'kbd-01' AND on_hand >= 2;
COMMIT;
-- The same statement again cannot oversell: the guard is re-checked at write time.
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE inventory SET on_hand = on_hand - 2
WHERE sku = 'kbd-01' AND on_hand >= 2;
COMMIT;
-- Report path: every row must come from one instant, and nothing is written.
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY;
SELECT sum(on_hand) AS units FROM inventory;
COMMIT;Choose the weakest level at which something concrete still enforces your invariant, whether a guarded WHERE clause, an explicit lock, or a constraint, and reserve SERIALIZABLE plus retries for invariants no single statement can check.
Worked examples
Which level am I actually getting?
Shows the precedence between the database default, a session default, and the level named in BEGIN.
-- PostgreSQL, one session, run top to bottom
SHOW transaction_isolation;
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;
BEGIN ISOLATION LEVEL READ COMMITTED;
SHOW transaction_isolation;
COMMIT;Example explained
Line 1The first SHOW reports read committed because outside an explicit block each statement is its own transaction and inherits the database default.
Line 2SET SESSION CHARACTERISTICS changes only this connection, which is why the second SHOW reports repeatable read while every other session is untouched.
Line 3BEGIN ISOLATION LEVEL READ COMMITTED wins for that one transaction, so the order of authority is BEGIN clause, then session, then database.
Line 4This is why a connection pool that runs SET SESSION CHARACTERISTICS at checkout is dangerous: every borrowed connection inherits it, so name the level in BEGIN instead.
Lock the row instead of raising the level
A read-modify-write kept at READ COMMITTED because an explicit row lock, not the snapshot, protects the invariant.
-- PostgreSQL
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric);
INSERT INTO accounts VALUES (1, 100), (2, 40);
BEGIN; -- still the READ COMMITTED default
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;
SELECT id, balance FROM accounts ORDER BY id;Example explained
Line 1FOR UPDATE takes a row lock on account 1, so a concurrent transfer waits there instead of reading a balance that is about to change.
Line 2No level change was needed: the guarantee comes from the lock, so the two writes run under the ordinary READ COMMITTED default.
Line 3COMMIT releases the lock and the final SELECT shows 75 and 65, meaning exactly 25 moved even though the amount was computed from the earlier read.
Line 4At SERIALIZABLE both transfers would instead run to the end and one would be aborted with 40001; locking chooses waiting over retrying.
Important notes
Level names are not portable guarantees. MySQL's REPEATABLE READ and PostgreSQL's behave differently on a read-then-write pair, and SQL Server's SNAPSHOT is a separate level you must enable with ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION ON, so test the specific anomaly on your engine.
For a long PostgreSQL report, BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE waits once for a safe snapshot and afterwards can neither block writers nor be aborted.
Common mistakes
Making SERIALIZABLE the server or pool default: unrelated reports and hot writes now pay for conflict tracking, and transactions begin failing with 40001 in code that never learned to retry.
Assuming REPEATABLE READ makes a read, compute in the application, then write back pair safe. In MySQL the UPDATE stores the stale number and one update is silently lost; in PostgreSQL the second transaction dies with 40001 unless you retry it.
Using a high level where a constraint belongs: SERIALIZABLE to stop duplicate signups slows every transaction and still needs an error handler, while a UNIQUE index on email rejects exactly the duplicate row.
Try it yourself
Change, predict, then run
Create seats(id int PRIMARY KEY, held_by text) with one row whose held_by is NULL, then run BEGIN ISOLATION LEVEL READ COMMITTED; UPDATE seats SET held_by = 'bob' WHERE id = 1 AND held_by IS NULL; COMMIT; twice. Confirm the second run reports UPDATE 0 instead of overwriting the holder, and explain why no stronger level was needed.
Open the SQL workspaceCheck your understanding
A checkout transaction reads a coupon's remaining uses, decides in application code that one is left, then writes remaining = 0. Two customers hit it at the same moment on PostgreSQL. Which change stops the double redemption at the lowest cost?
- Switch that transaction to REPEATABLE READ so the coupon read stays stable.
- Set default_transaction_isolation = 'serializable' for the whole database.
- Do the check inside the write: UPDATE coupons SET remaining = remaining - 1 WHERE id = 7 AND remaining > 0, and treat zero rows updated as already used.
- Wrap the SELECT and the UPDATE in BEGIN ... COMMIT so the pair is atomic.
Show answer
Moving the condition into the writing statement means PostgreSQL locks the row and re-evaluates remaining > 0 against the committed value, so the losing transaction updates zero rows and plain READ COMMITTED is sufficient. REPEATABLE READ is the tempting answer, but a stable read only makes the decision consistent, not authoritative: the second transaction still writes a value derived from a stale read, and PostgreSQL resolves that by aborting it with 40001, so it is a fix only if you add a retry loop, at a higher price. Atomicity is a different property, so option 3 changes nothing about two sessions reading the same remaining value.