Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 2 · Lesson 12 · 6 min read

Isolation Levels: SQL Server vs PostgreSQL MVCC

On this page
  1. Concurrency differences after migration
  2. MVCC in a two-session experiment
  3. SQL Server to PostgreSQL isolation levels
  4. One error aborts the whole transaction
  5. MERGE and upserts
  6. Deadlocks and the cost of MVCC
  7. Check yourself

Concurrency differences after migration

Concurrency bugs during a migration rarely show up in testing. Code that worked for years on SQL Server can rely on blocking without anyone knowing: a read that waited for a writer, a REPEATABLE READ that held locks, a TRY/CATCH that carried on after one failed statement. PostgreSQL behaves differently in each case. This lesson shows the differences with a two-session experiment, then maps the isolation levels.

MVCC in a two-session experiment

Open two query consoles in DataGrip on the same database, and turn off autocommit in both (the toolbar's transaction mode). Then create a table:

SQL
CREATE TABLE accounts (account_id int PRIMARY KEY, balance numeric(12,2) NOT NULL);
INSERT INTO accounts VALUES (1, 500);

Then run this in two sessions:

SQL
Session A                                         Session B
BEGIN;
UPDATE accounts SET balance = balance - 50
WHERE account_id = 1;          -- not committed
                                                  BEGIN;
                                                  SELECT balance FROM accounts
                                                  WHERE account_id = 1;   -- 500, no waiting
COMMIT;
                                                  SELECT balance FROM accounts
                                                  WHERE account_id = 1;   -- 450
                                                  COMMIT;

Session B's first SELECT returns immediately with the old value, 500. On SQL Server with default settings, it would have waited for session A. That's MVCC (Day 1). The UPDATE wrote a new row version, and B read the last committed version without taking any locks.

If you've used READ_COMMITTED_SNAPSHOT in SQL Server, this is the same behavior, just always on. If the source database never had it enabled, look for code that assumed blocking, for example a SELECT used to "wait until the other process finishes". That code now reads stale data instead of waiting. Use SELECT ... FOR UPDATE when you really need to lock the row and wait.

There's also no reason for WITH (NOLOCK) any more. Readers never block, so remove the hints, and PostgreSQL never gives you the dirty reads NOLOCK allowed.

SQL Server to PostgreSQL isolation levels

SQL ServerPostgreSQLWhat changes
READ UNCOMMITTED, NOLOCKREAD COMMITTEDDirty reads don't exist in PostgreSQL
READ COMMITTED (default)READ COMMITTED (default)Each statement reads a fresh snapshot. Readers never block.
READ COMMITTED SNAPSHOTREAD COMMITTEDThe same behavior
SNAPSHOTREPEATABLE READOne snapshot for the whole transaction
REPEATABLE READREPEATABLE READSnapshot instead of held locks, so conflicts raise an error instead of waiting
SERIALIZABLESERIALIZABLEConflict detection instead of range locks, so conflicts raise an error

The last two rows are the ones to plan for. SQL Server's stricter levels prevent anomalies by holding locks, so other sessions wait. PostgreSQL's stricter levels don't make anyone wait for reads. Instead, when two transactions conflict, PostgreSQL aborts one of them with SQLSTATE 40001:

Code
ERROR: could not serialize access due to concurrent update

At REPEATABLE READ you get it when you update a row that another transaction changed after your snapshot. At SERIALIZABLE you also get it for subtler conflicts, such as two transactions that each read something the other then changes. Code moving from SQL Server must catch 40001 and retry the whole transaction. Without retries, users see random failures under load that never happened before.

Setting the level works almost the same way, except it goes after BEGIN or on the BEGIN itself:

SQL
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM accounts;
COMMIT;

One error aborts the whole transaction

In SQL Server without SET XACT_ABORT ON, many errors only fail the statement. The transaction carries on, and code often relies on that. PostgreSQL is stricter. After any error, the transaction is marked failed, and every later statement is refused until you roll back:

SQL
BEGIN;
INSERT INTO accounts VALUES (2, 100);
INSERT INTO accounts VALUES (2, 100);   -- ERROR: duplicate key
SELECT * FROM accounts;                 -- ERROR: current transaction is aborted
ROLLBACK;

To survive an expected error, set a savepoint before the risky statement and roll back to it:

SQL
BEGIN;
INSERT INTO accounts VALUES (3, 100);
SAVEPOINT before_risky;
INSERT INTO accounts VALUES (3, 999);   -- fails: duplicate key
ROLLBACK TO SAVEPOINT before_risky;     -- the first insert survives
INSERT INTO accounts VALUES (4, 999);
COMMIT;

SAVE TRANSACTION x becomes SAVEPOINT x, and ROLLBACK TRANSACTION x becomes ROLLBACK TO SAVEPOINT x. Inside PL/pgSQL, an EXCEPTION block does the same thing automatically (Day 8).

MERGE and upserts

T-SQL code often upserts with MERGE. PostgreSQL has MERGE too, since version 15, and PostgreSQL 17 added WHEN NOT MATCHED BY SOURCE and a RETURNING clause, so most statements port with little change. Tested on PostgreSQL 18:

SQL
CREATE TABLE account_feed (account_id int PRIMARY KEY, balance numeric(12,2) NOT NULL);
INSERT INTO account_feed VALUES (1, 475), (2, 60);

MERGE INTO accounts a
USING account_feed f ON f.account_id = a.account_id
WHEN MATCHED THEN
  UPDATE SET balance = f.balance
WHEN NOT MATCHED THEN
  INSERT (account_id, balance) VALUES (f.account_id, f.balance)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
RETURNING merge_action(), a.account_id, a.balance;

merge_action() plays the part of $action in T-SQL's OUTPUT clause, reporting INSERT, UPDATE or DELETE for each row.

There's one concurrency trap you may already know from SQL Server, where MERGE needs WITH (HOLDLOCK) to be safe under concurrent upserts. In PostgreSQL, two sessions running MERGE for the same new key can both decide "not matched", and the second insert fails with a duplicate-key error. For single-row upserts from application code, use INSERT ... ON CONFLICT instead, which is atomic without any hint:

SQL
INSERT INTO accounts (account_id, balance) VALUES (2, 70)
ON CONFLICT (account_id) DO UPDATE SET balance = EXCLUDED.balance;

Keep MERGE for batch syncs from a staging table, where one job runs at a time.

Deadlocks and the cost of MVCC

Deadlocks still happen when two transactions update the same rows in opposite orders. PostgreSQL checks for them after a lock wait passes deadlock_timeout (1 second by default), aborts one transaction with SQLSTATE 40P01, and reports the cycle in the error. Handle it like SQL Server's error 1205: retry the transaction, and lock rows in a consistent order.

MVCC also has a cost SQL Server DBAs haven't paid in the same way. Old row versions stay in the table until vacuum removes them, which is SQL Server's tempdb version store moved into your tables. A transaction left open for hours, for example an idle session in a GUI tool, stops that cleanup for the whole database. Day 18 covers vacuum. For now, watch for sessions that are idle in a transaction:

SQL
SELECT pid, state, xact_start, left(query, 50) AS query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%';

Check yourself

No. PostgreSQL resolves conflicts by aborting one transaction instead of making it wait. Add a retry loop around the transaction for SQLSTATE 40001, and 40P01 for deadlocks.

The first error aborted the whole transaction. Use INSERT ... ON CONFLICT DO NOTHING, or wrap each risky insert in a savepoint or a PL/pgSQL EXCEPTION block.

Remove them. In PostgreSQL readers see a consistent snapshot and never block on writers, which is what NOLOCK was usually trying to achieve, without its risk of reading uncommitted or duplicated rows. The hint syntax also isn't valid, so queries fail until it's removed.

Use INSERT ... ON CONFLICT (key) DO UPDATE, which checks and writes atomically. MERGE can race on new keys under concurrency, much as it needed HOLDLOCK on SQL Server.

Read Committed, like SQL Server, but implemented with MVCC snapshots per statement, so readers don't take shared locks. It behaves much like SQL Server's Read Committed Snapshot Isolation, so code written against RCSI usually migrates with fewer surprises than code that relied on locking.