Mastering Transactions in PostgreSQL: A Complete Guide

Last update on December 28 2024 13:04:41 (UTC/GMT +8 hours)

Understanding PostgreSQL Transactions

Transactions in PostgreSQL allow you to group multiple SQL operations into a single logical unit. They ensure that either all the operations succeed or none of them are applied, maintaining database integrity.

1. What is a Transaction?

A transaction is a sequence of operations performed as a single unit of work. PostgreSQL adheres to the ACID properties:

Atomicity: All changes are committed or rolled back.
Consistency: Database remains consistent before and after the transaction.
Isolation: Concurrent transactions do not interfere with each other.
Durability: Once committed, changes are permanent.

2. Basic Transaction Commands

Command	Description	Example
BEGIN	Starts a transaction block.	BEGIN;
COMMIT	Saves changes made in the transaction.	COMMIT;
ROLLBACK	Reverts changes made in the transaction.	ROLLBACK;

3. Example Usage

Start, Commit, and Rollback

Code:

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

If an error occurs during the updates:

Code:

ROLLBACK;

4. Savepoints for Nested Transactions

Use SAVEPOINT to partially rollback within a transaction:

Code:

BEGIN;
SAVEPOINT sp1;
INSERT INTO orders (id, amount) VALUES (1, 500);
ROLLBACK TO sp1;
COMMIT;

5. Transaction Isolation Levels

PostgreSQL supports the following isolation levels:

Isolation Level	Description
Read Uncommitted	Least restrictive; uncommitted changes are visible.
Read Committed	Default level; sees only committed changes.
Repeatable Read	Ensures consistency in a single transaction.
Serializable	Most restrictive; transactions appear sequential.

Set isolation level:

Code:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

6. Autocommit Mode

PostgreSQL operates in autocommit mode by default. Disable autocommit for explicit transaction control:

Code:

SET autocommit = OFF;
BEGIN;
-- Execute operations
COMMIT;

7. Error Handling in Transactions

Ensure robust transaction handling:

Code:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Check for sufficient balance
DO $$  
BEGIN  
    IF NOT FOUND THEN  
        RAISE EXCEPTION 'Insufficient funds';  
    END IF;  
END $$;

COMMIT;

Additional Tips:

Best Practices: Keep transactions short to avoid locking resources.
Performance: Avoid long-running transactions for better scalability.

All PostgreSQL Questions, Answers, and Code Snippets Collection.