Database

Transaction Isolation Level Advisor

Names the anomaly you are describing and the lowest isolation level that prevents it.

Loading the tool…

Processing happens locally in your browser. What you paste or load is processed by this page and is not uploaded to a server. Nothing is stored unless you use a control that says it stores something, and you can clear anything this site has kept from the privacy page.

How to use this tool

  1. Describe what you actually observed, one symptom per line, in ordinary words.
  2. Choose your engine so the default level shown is the right one.
  3. Select Diagnose the anomaly.
  4. Read the named anomaly before the level — if the description does not match what you saw, the level below it will not help.

What isolation level advisor does

Isolation levels are taught as a table of four levels against four anomalies, which is exactly the wrong way round from how anybody meets them. You do not start with a level; you start with two users saving a form and one of the edits vanishing, or a report that shows money leaving one account without arriving in the other, or a count that changes halfway through a transaction. The hard part is putting a name to what you saw, because once it has a name the level that prevents it follows immediately.

Describe what you observed, one symptom per line, in whatever words you would use. This page matches each description against the known anomalies — dirty read, non-repeatable read, phantom, lost update, read skew, write skew — explains what is actually happening in each, and gives the lowest isolation level that prevents all of them together. It also notes the default on your engine, which is not the same everywhere: MySQL defaults to REPEATABLE READ and almost nothing else does. Where SERIALIZABLE is the answer it says what that costs, and points at the cheaper targeted fixes first.

Frequently asked questions

A lost update is two transactions fighting over the same row: both read it, both compute a new value, and the second write overwrites the first. Write skew involves different rows: each transaction reads a set, decides its own change is allowed, and together they break a rule neither could break alone. Locking the row fixes the first; only SERIALIZABLE reliably fixes the second, because no single row is in conflict.

Often not, and the differences matter. PostgreSQL at REPEATABLE READ already blocks phantoms, which the standard does not require. MySQL defaults to REPEATABLE READ where almost everything else defaults to READ COMMITTED. Oracle has no REPEATABLE READ at all. SQL Server behaves quite differently depending on whether READ_COMMITTED_SNAPSHOT is on. The result notes your engine default for this reason.

It is correct, and it is the most expensive option you have. On PostgreSQL, conflicting transactions abort and your application must retry them — code that does not retry will simply start failing. On the locking engines, concurrency drops. A SELECT ... FOR UPDATE, a unique constraint, or an atomic UPDATE ... SET x = x + 1 solves most real cases at READ COMMITTED and costs almost nothing.

Describe the observation rather than the theory. "The count was 40 the first time and 43 the second, in one transaction" matches; "our transactions are not isolated properly" cannot. Name what changed — a value, a set of rows, an update that vanished, a rule that two transactions each passed — and the anomaly usually falls out of the sentence.