Serializable isolation in PostgreSQL

Introduction

Serializable isolation ensures that transactions behave as if they run one after another, avoiding conflicts and keeping data accurate.

When you want to prevent errors caused by multiple users changing data at the same time.

When you need the highest level of data accuracy in financial or inventory systems.

When you want to avoid problems like lost updates or inconsistent reads.

When your application requires strict correctness over speed.

When you want to make sure complex transactions do not interfere with each other.

Syntax

PostgreSQL

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Then run your SQL statements
COMMIT;

This command sets the isolation level for the current transaction only.

Serializable isolation can slow down performance because it locks data more strictly.

Examples

This example transfers money between two accounts safely under serializable isolation.

PostgreSQL

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

This example reads pending orders with the highest isolation to avoid seeing changes from other transactions.

PostgreSQL

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM orders WHERE status = 'pending';
COMMIT;

Sample Program

This transaction adds a new product and reads it back safely with serializable isolation.

PostgreSQL

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO products (name, quantity) VALUES ('Pen', 100);
SELECT * FROM products WHERE name = 'Pen';
COMMIT;

Important Notes

Serializable isolation prevents many concurrency problems but can cause transactions to fail if conflicts occur.

You may need to retry transactions if serialization errors happen.

Use this level only when necessary because it can reduce system throughput.

Summary

Serializable isolation makes transactions appear one after another, avoiding conflicts.

It is the strictest isolation level and ensures data accuracy.

Use it when correctness is more important than speed.