In PostgreSQL, what is the default transaction isolation level, how do you choose another, and what do you get if you request READ UNCOMMITTED?
answer
- four names, three behaviours
- a GUC with a default_ prefix
- the first query fixes the level
- what the weakest name maps to
basics
~20 sPostgreSQL defaults to READ COMMITTED. You pick a level per transaction with BEGIN ISOLATION LEVEL or SET TRANSACTION before its first query, or change default_transaction_isolation; READ UNCOMMITTED is accepted but behaves exactly like READ COMMITTED.
solid answer
~40 sThe default is `READ COMMITTED`, from `default_transaction_isolation = 'read committed'`. For one transaction you write `BEGIN ISOLATION LEVEL REPEATABLE READ`, or issue `SET TRANSACTION ISOLATION LEVEL ...` straight after `BEGIN`; once the transaction has run a query and taken its snapshot, a change fails with `SET TRANSACTION ISOLATION LEVEL must be called before any query`. A different default can be set in `postgresql.conf`, with `ALTER DATABASE` or `ALTER ROLE ... SET`, or for a session with `SET SESSION CHARACTERISTICS`. All four standard names are accepted, but PostgreSQL implements three behaviours: `READ UNCOMMITTED` runs exactly like `READ COMMITTED`, because a reader picks row versions by snapshot and never sees another transaction's uncommitted version. `SHOW transaction_isolation` reports the level the transaction requested.
code
sql · 14 linesSHOW default_transaction_isolation; -- read committed
BEGIN ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation; -- repeatable read
SELECT count(*) FROM seats WHERE event_id = 42 AND status = 'available';
COMMIT;
BEGIN;
SELECT count(*) FROM seats WHERE event_id = 42;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
ROLLBACK;
ALTER ROLE ticketing_app SET default_transaction_isolation = 'serializable';go deeper
Recall the default, READ COMMITTED, and the two ways to pick a level for one transaction: BEGIN ISOLATION LEVEL, or SET TRANSACTION as the first statement.
Explain why READ UNCOMMITTED maps to READ COMMITTED: readers choose row versions by snapshot, so another transaction's uncommitted version is never visible.
Show how you would raise the level safely: a per-role default, a retry path for 40001, and awareness that a hot standby tops out at REPEATABLE READ.
Weigh a server-wide stronger default against per-transaction opt-in: who owns the retry logic, which clients break, and how the level is enforced across services.
## The default and where it comes from Every PostgreSQL transaction runs at an **isolation level**, which decides what it can see of other transactions' work. Out of the box that level is **READ COMMITTED**: the configuration parameter `default_transaction_isolation` ships as `'read committed'`, and each new transaction copies it into `transaction_isolation`. At this level every statement takes a fresh **snapshot** — a record of which transactions had committed when the statement began — so two `SELECT`s in one transaction can see different committed data. READ COMMITTED is the default because it is cheap and forgiving: when an `UPDATE` runs into a row another session has just changed, it waits and then works on the newest committed version instead of failing. The stronger levels trade that for a stable view and can end a transaction with a **serialization failure** (SQLSTATE `40001`) that the application must retry. ## Choosing a level There are several equivalent ways to ask for a level; they differ only in scope. | Scope | How | Example | |---|---|---| | One transaction | `BEGIN` / `START TRANSACTION` with the level | `BEGIN ISOLATION LEVEL REPEATABLE READ;` | | One transaction | `SET TRANSACTION` right after `BEGIN` | `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;` | | Rest of the session | `SET SESSION CHARACTERISTICS` or `SET default_transaction_isolation` | `SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;` | | A role or a database | `ALTER ROLE` / `ALTER DATABASE ... SET` | `ALTER ROLE ticketing_app SET default_transaction_isolation = 'serializable';` | | Whole server | `postgresql.conf` | `default_transaction_isolation = 'repeatable read'` | `SET SESSION CHARACTERISTICS` is only a verbose spelling of setting `default_transaction_isolation`. The per-transaction parameter `transaction_isolation` cannot be placed in the configuration file; it exists so the current transaction's level can be read or set from SQL. Client drivers usually expose the same choice as a connection or transaction property and send one of these commands for you. ## The timing rule The level must be fixed **before the transaction takes its first snapshot**, which happens at its first query or data-modifying statement. Run a `SELECT` first and then try to change the level, and PostgreSQL raises `SET TRANSACTION ISOLATION LEVEL must be called before any query`; like any error, that aborts the transaction. Setting the level to the value it already has is always allowed, and a change inside a subtransaction (after a `SAVEPOINT`) is rejected, so the level belongs to the top-level transaction. The read-only and deferrable properties follow the same before-the-first-query rule. ## Four names, three behaviours The SQL standard names four levels. PostgreSQL accepts all four and implements three: | You request | You get | Snapshot taken | |---|---|---| | `READ UNCOMMITTED` | READ COMMITTED behaviour | per statement | | `READ COMMITTED` (default) | READ COMMITTED | per statement | | `REPEATABLE READ` | snapshot isolation | once, at the first query | | `SERIALIZABLE` | Serializable Snapshot Isolation (SSI) | once, at the first query, plus conflict monitoring | READ UNCOMMITTED collapses into READ COMMITTED because of the storage model. An `UPDATE` in PostgreSQL does not overwrite a row in place; it writes a new **row version** stamped with the writing transaction's ID, and every reader decides from its snapshot which version it may see. A version written by a transaction that has not committed is invisible to every other transaction, so there is no path by which a reader could see uncommitted data. The manual calls this mapping the only sensible one for its MVCC design. The standard lists the anomalies each level must *prevent*, so giving more than was asked is conformant — which is also why PostgreSQL's REPEATABLE READ prevents phantom reads the standard would allow. ## Checking and enforcing - `SHOW transaction_isolation;` (or `current_setting('transaction_isolation')`) reports the level the current transaction requested; after asking for READ UNCOMMITTED it says `read uncommitted`, although the behaviour is READ COMMITTED. - `SHOW default_transaction_isolation;` shows what new transactions will get. - A per-role default is the usual way to move one application's connections to a stronger level without touching other clients. - On a hot standby SERIALIZABLE is not available: requesting it fails with `cannot use serializable mode in a hot standby`, and REPEATABLE READ is the strongest level a read replica offers. ## Traps interviewers listen for 1. Assuming the default is SERIALIZABLE because the SQL standard names it as the default. 2. Choosing READ UNCOMMITTED to make reports faster: plain reads in PostgreSQL already do not wait for row writers, and the request changes nothing. 3. Issuing `SET TRANSACTION` after the first query and assuming it took effect. 4. Raising the default without giving the application a retry path for `40001`.
- Why does PostgreSQL refuse to change the isolation level after the transaction's first query?The level decides how the snapshot is taken: per statement at READ COMMITTED, once per transaction at REPEATABLE READ and SERIALIZABLE, plus predicate locking at SERIALIZABLE. After the first query the transaction already holds a snapshot taken under the old rules, so switching would leave it half one level and half another. The check is whether a snapshot exists, so `SET TRANSACTION` must precede any query.
- Can you run SERIALIZABLE transactions on a PostgreSQL streaming standby?No. Serializable is not supported on a hot standby; requesting it fails with `cannot use serializable mode in a hot standby`, and REPEATABLE READ is the strongest level there. Even when every write on the primary is serializable, a REPEATABLE READ query on the standby can see a transient state that no serial order of the primary's transactions would produce.
- If you set default_transaction_isolation to serializable for the ticketing role, what does the application now owe?A retry path. Both REPEATABLE READ and SERIALIZABLE can end a transaction with SQLSTATE `40001`, and PostgreSQL offers no automatic retry, so every transaction that role runs must be rerun from the start by the client when it fails that way. The manual also suggests guarding against other levels sneaking in, for example by checking the level in triggers.
- PostgreSQL's default isolation level is SERIALIZABLE, as in the SQL standard.
- READ UNCOMMITTED in PostgreSQL lets a report read rows other sessions have not committed.
- SET TRANSACTION ISOLATION LEVEL can be issued at any point inside a transaction.
- Switching to READ UNCOMMITTED stops PostgreSQL readers waiting on writers.
- A hot standby serves SERIALIZABLE read transactions just like the primary.