Parottasalna

This is a continuation of ACID Series. In this blog i jot down notes on Isolation in ACID for better understanding. Isolation is one of the core properties of ACID (Atomicity, Consistency, Isolation, Durability) in database systems. It defines how transactions interact with each other when they run concurrently. PostgreSQL, like many other relational databases, provides different levels of isolation to balance between performance and consistency.

What is Isolation?

Isolation ensures that concurrent transactions do not interfere with each other, preserving data consistency. PostgreSQL achieves this by using MVCC (Multi-Version Concurrency Control), which creates snapshots of data for each transaction, allowing transactions to operate independently.

However, isolation is not absolute; trade-offs between consistency and performance are managed through isolation levels.

Common Read Phenomena with Examples

1. Dirty Read

A transaction reads uncommitted changes from another transaction.

Note: Dirty reads are not possible in PostgreSQL, even at the READ UNCOMMITTED isolation level. PostgreSQL treats READ UNCOMMITTED as READ COMMITTED, ensuring that transactions never see uncommitted changes.

Example:

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- No COMMIT or ROLLBACK yet.

-- Transaction 2
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- Reads the original balance (no dirty reads).
COMMIT;

2. Non-Repeatable Read

A transaction reads the same row twice and sees different data because another transaction modifies it in between.

Another Similar Example:

-- Transaction 1
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1; -- Reads initial balance.

-- Transaction 2
BEGIN;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 1;
COMMIT;

-- Back to Transaction 1
SELECT balance FROM accounts WHERE account_id = 1; -- Sees updated balance (non-repeatable read).
COMMIT;

3. Phantom Read

A transaction executes a query twice and sees different sets of rows because another transaction inserts or deletes rows.

-- Transaction 1
BEGIN;
SELECT * FROM accounts WHERE balance > 500; -- Returns 2 rows.

-- Transaction 2
BEGIN;
INSERT INTO accounts (account_holder_name, balance) VALUES ('New User', 1000);
COMMIT;

-- Back to Transaction 1
SELECT * FROM accounts WHERE balance > 500; -- Now returns 3 rows (phantom read).
COMMIT;

Isolation Levels in PostgreSQL

PostgreSQL supports the following isolation levels

1. Read Uncommitted

• Practically treated as Read Committed in PostgreSQL.
• Prevents dirty reads.
• Non-repeatable and phantom reads can occur.

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- No COMMIT or ROLLBACK yet.

-- Transaction 2
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- Reads the original balance (no dirty reads).
COMMIT;

2. Read Committed

• Default isolation level in PostgreSQL.
• Prevents dirty reads.
• Non-repeatable and phantom reads can occur.

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- COMMIT after Transaction 2 finishes.

-- Transaction 2
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- Reads the original balance (no dirty reads).
COMMIT;

3. Repeatable Read

• Prevents dirty and non-repeatable reads.
• Phantom reads can still occur.

-- Transaction 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM accounts WHERE balance > 500;
-- Transaction 2 inserts a new account with balance > 500 and commits.
-- Transaction 1 runs the same query but does not see the new account (no phantom reads).
COMMIT;

4. Serializable

• The strictest level of isolation.
• Prevents dirty, non-repeatable, and phantom reads.
• Transactions are executed as if they were serialized sequentially.

-- Transaction 1
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE account_id = 1;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Transaction 2
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
-- Results in a serialization failure.
ROLLBACK;

Why Do We Need Isolation Levels?

Concurrent transactions are a common occurrence in modern databases, particularly in multi-user environments. Without proper isolation, transactions could interfere with each other, leading to:

1. Data Integrity Issues: Uncommitted changes might be read or modified by other transactions, causing inconsistencies.
2. Lost Updates: Two transactions updating the same data simultaneously could overwrite each other’s changes.
3. Inconsistent Results: Queries might return different results within the same transaction, leading to unreliable application behavior.

Isolation levels allow developers to tailor transaction behavior to meet the specific needs of an application. For example, in high-frequency trading systems, Serializable isolation ensures absolute consistency, whereas in a reporting system, Read Committed might suffice to optimize performance.

Key Benefits of Isolation

1. Data Integrity: Prevents unintended interference between concurrent transactions.
2. Flexibility: Allows developers to choose isolation levels based on the application’s needs.
3. Concurrency: Enhances the database’s ability to handle multiple transactions simultaneously.