Learning Notes #33 - Isolation in ACID | Postgres

{% raw %}

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 1BEGIN;UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;-- No COMMIT or ROLLBACK yet.-- Transaction 2BEGIN;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 1BEGIN;SELECT balance FROM accounts WHERE account_id = 1; -- Reads initial balance.-- Transaction 2BEGIN;UPDATE accounts SET balance = balance + 500 WHERE account_id = 1;COMMIT;-- Back to Transaction 1SELECT 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 1BEGIN;SELECT * FROM accounts WHERE balance > 500; -- Returns 2 rows.-- Transaction 2BEGIN;INSERT INTO accounts (account_holder_name, balance) VALUES ('New User', 1000);COMMIT;-- Back to Transaction 1SELECT * 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 1BEGIN;UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;-- No COMMIT or ROLLBACK yet.-- Transaction 2BEGIN;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 1BEGIN;UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;-- COMMIT after Transaction 2 finishes.-- Transaction 2BEGIN;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 1BEGIN 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 1BEGIN ISOLATION LEVEL SERIALIZABLE;SELECT balance FROM accounts WHERE account_id = 1;UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;-- Transaction 2BEGIN 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.

{% endraw %}