Which anomalies your default isolation level still lets through

You own four services. Two are on Postgres, one on MySQL, one on a SQL Server instance being lifted into Azure SQL Database next month. A reviewer asks a fair question in a design review: which transaction isolation level does each one run at, and which anomalies does that permit? Nobody in the room knows for any of the four, and two people answer confidently and differently about the same service.

That is the normal state of affairs, and the migration makes it urgent.

Predict

Suppose you win the argument and set every transaction in all four services to serializable. Which of these four bugs is still possible afterwards?

Choose one answer

Two support agents apply a credit to the same account in the same second, and the balance moves by one credit.Two on-call engineers each check that the other is still on shift, and both successfully book themselves off.Two people open the same edit form, one saves at nine o'clock, the other saves five minutes later, and the first person's change is gone.A finance report issues two queries and the totals do not reconcile, because somebody committed between them.

Lock in answer

The matrix, and what each row obliges you to do

The mechanism behind these anomalies — what lost update and write skew actually are, and why an atomic transaction can still produce a wrong total — is the database track's job, and the lesson there walks it properly. This one is about choosing: what your default permits, and what you therefore have to build.

Engine, out of the box	Default level	Permits lost update across statements	Permits write skew	What that obliges you to do
Postgres	Read committed	Yes	Yes	Make writes atomic statements, or put the invariant in a constraint. Raising to repeatable read converts the loss into a serialisation error you must catch
MySQL InnoDB	Repeatable read	Yes	Yes	The same work, with no serialisation error to retry on, because a write performs a current read rather than a snapshot read. Gap locks on locking reads do block one family of phantom
SQL Server, on premises	Read committed, lock-based	Yes	Yes	Readers block writers, so slow readers become write latency. Invariants still need constraints; the blocking is not protection
Azure SQL Database	Read committed, snapshot-based	Yes	Yes	The same invariants, and additionally: any code that relied on a reader blocking a writer now does not block

Read down the third and fourth columns and notice they are identical. No mainstream default stops either anomaly. The choice between these engines is therefore not a choice about which anomalies you suffer; it is a choice about which symptom you get when the anomaly happens, and whether you get an error you can retry or a wrong number you never see.

That reframing is the useful one, because it tells you where the work goes. You are not shopping for a level that makes the application correct. You are deciding what to enforce in the schema, what to enforce in the statement, and what to leave to a retry — and then writing down which you chose.

The artefact this produces

The output of the exercise is not a setting. It is a short record, one row per service, that a reviewer can check without opening a console. The track's milestone asks for exactly this shape for an isolation level: what is promised, what it charges you downstream, and when you last looked.

service        engine / version        level observed    observed where            checked
-------------- ----------------------- ----------------- ------------------------- ----------
billing        postgres 16             read committed    app connection via pooler 2026-10-24
billing        ^ enforcement           unique + check constraints on balance rows   2026-10-24
catalogue      mysql 8.0 (innodb)      repeatable read   app connection            2026-10-24
catalogue      ^ enforcement           none; read-modify-write at two call sites    2026-10-24  <-- finding
ledger         sql server 2022         read committed    app connection            2026-10-27
ledger         ^ snapshot setting      is_read_committed_snapshot_on = 0            2026-10-27
ledger-azure   azure sql database      read committed    app connection            2026-10-27
ledger-azure   ^ snapshot setting      is_read_committed_snapshot_on = 1            2026-10-27  <-- differs

Three rules make it worth keeping. Observe the level on a connection your application would actually use, because a pooler in transaction mode, an ORM and a framework that opens a transaction per request each have an opinion and the last one wins. Record the enforcement alongside the level, since the level alone tells a reviewer nothing about whether the invariant is protected. And date each row, because a row with no date is a claim about a system that has since been upgraded.

The catalogue row is what a finding looks like: a default that permits an anomaly, plus two code paths that can produce it, plus no schema-level enforcement. That is actionable. "We run repeatable read" is not.

What would you do

The ledger service, eleven years old, runs on an on-premises SQL Server instance where read-committed snapshot is off: readers take shared locks and block writers. It is being lifted into Azure SQL Database, where a newly created database has that setting on and readers read a snapshot instead.

Nobody wrote the code with either behaviour in mind. There are three places where a report-style read runs for several seconds, and at least two where a SELECT is followed by an UPDATEin the same transaction with no explicit lock hint. The cutover window is one weekend.

Optimising for

No code path that depended on a reader blocking a writer survives the migration undetected, and the behavioural difference between the two environments is recorded in one place with the date it was verified — accepting a slower cutover and some rework to get both.

You cannot know

• How many of the SELECT-then-UPDATE paths actually relied on the shared lock, which cannot be answered by reading the code alone
• Whether any nightly batch depended on a long reader blocking writers to get a quiet window
• How much version-store space the workload will use in the new environment, which nobody has load tested
• Whether the vendor-supplied reporting module, which nobody can modify, takes its own lock hints

Choose an option

Turn the snapshot setting off on the Azure database so the new environment matches the old one exactly, and migrate with no code changes.Keep the snapshot setting on, find the paths that relied on blocking reads, and convert each to an explicit lock or an atomic statement before the cutover.Keep the setting on, and raise the two read-then-write transactions to the engine's explicit snapshot isolation level instead of touching the statements.Migrate as-is with the platform default, and put the invariants into unique, check and foreign key constraints so correctness no longer depends on the level at all.

Check

You fix the cross-request edit from the prediction with a version column: the form carries the version it loaded, and the update refuses if the stored version has moved. What have you actually built?

Choose one answer

A stronger isolation level, implemented in the application rather than the engine.A conflict detector whose unit is the user's edit rather than the database transaction, which is why no engine setting could substitute for it.The same thing serializable would have given you, at lower cost.A lock held across a user's think time, which is the pattern everyone warns about.

Check answer

Where this stops holding

All of it assumes concurrent writers. If a given row is only ever written by one single-threaded consumer — one partition of a log consumed in order, one worker, a queue with a concurrency of one — then there is no interleaving to isolate, and the level stops being a decision for that path.

The choosing consequence is specific, and it is the one worth taking away: stop paying for it. If you had set serializable and built retry wrappers to protect a path that turns out to be single-threaded, you are carrying retry complexity and abort risk for a guarantee the architecture already provides. Drop to the default on that path, and record why in the inventory — the structural assumption, in the row where a reviewer will look for it.

Record it because nothing else will. A configuration value shows up in a console; "only one consumer ever writes this table" shows up nowhere, and it stops being true the first time somebody runs a backfill script, adds an admin endpoint, or raises the consumer concurrency from one to two to clear a backlog. None of those three changes looks like a concurrency change to the person making it.

And the cross-request anomaly is untouched by all of this. A single-threaded writer still cheerfully applies a stale edit from a form loaded five minutes ago, because that write is correct in every sense the database cares about. The version column is not an alternative to choosing a level; it is the other half of the job.

›Why the inventory beats the settingOptional

The instinct after a lesson like this is to standardise: pick a level, set it in the platform template, and consider the problem closed. It is the wrong instinct, for a reason that shows up about a year later.

A standardised level is a claim about the whole fleet that nobody rechecks, and it is false the moment one service adds a pooler in transaction mode, one framework upgrade changes when a transaction opens, or one database gets created by a managed service with its own defaults. The setting is not self-verifying.

The inventory is. Each row names where the value was observed and when, so a reviewer can reproduce it in a minute and a drift shows up as a stale date rather than as an incident. It also has room for the thing a setting cannot express — the enforcement, the structural assumption, the one path that is deliberately different — which is where the actual risk lives.

Standardise the audit, not the value.

Worth remembering

• No mainstream default stops lost update or write skew, so the engine choice decides your symptom, not your safety.
• Postgres defaults to read committed; MySQL InnoDB to repeatable read; SQL Server to lock-based read committed.
• Azure SQL Database creates databases with read-committed snapshot on, where an on-premises SQL Server has it off.
• Isolation levels only govern the window between a transaction beginning and committing.
• A cross-request edit survives every isolation level, including serializable, and needs a version column.
• Observe the level on a connection your application would use: the pooler, ORM and framework each override it.
• Record level and enforcement together and date the row — the level alone tells a reviewer nothing.
• A constraint is enforced at every level, on every path, including the admin tool and the backfill script.
• Match environments deliberately or differ deliberately, but never discover the difference during a cutover.
• If a path is genuinely single-threaded, drop the retry machinery and write down the assumption, because nothing else records it.