PostgreSQL - Transactions

PostgreSQL - Modern Transaction Management

A transaction is a sequence of operations performed as a single, logical unit of work. All operations in a transaction must complete successfully, or none of them are applied. This ensures data integrity, a cornerstone of reliable database systems. This is often described by the acronym ACID.

The ACID Properties of Transactions

Transactions guarantee reliability through four key properties (ACID):

• Atomicity: Ensures that all operations within the transaction are treated as a single ‘atom’. The transaction either fully completes (‘commits’) or is fully undone (‘rolled back’). There is no partial completion.
• Consistency: Ensures that a transaction brings the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, and triggers.
• Isolation: Ensures that concurrently executing transactions do not interfere with each other. Each transaction appears to be executing in isolation from others, preventing issues like ‘dirty reads’.
• Durability: Ensures that once a transaction has been committed, its changes are permanent, even in the event of a power loss, crash, or other system failure.

Core Transaction Control Commands

You control transactions with three main SQL commands:

• BEGIN TRANSACTION; (or just BEGIN;): Marks the beginning of a transaction block.
• COMMIT;: Saves all changes made during the transaction, making them permanent.
• ROLLBACK;: Discards all changes made during the transaction, returning the database to the state it was in before the transaction started.

These commands are used with DML (Data Manipulation Language) statements like INSERT, UPDATE, and DELETE. DDL (Data Definition Language) commands like CREATE TABLE are often transaction-safe in PostgreSQL but can sometimes implicitly commit an open transaction, so it’s best to avoid mixing DML and DDL in a single transaction block.

Practical Application: A Bank Transfer

The classic example of a transaction is transferring money between two bank accounts. This involves two separate UPDATE operations: debiting one account and crediting another. We must ensure both happen, or neither does. A system crash between the two operations would be catastrophic without a transaction.

First, let’s set up our accounts table:

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    owner_name VARCHAR(100) NOT NULL,
    balance NUMERIC(15, 2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (id, owner_name, balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 500.00);

Example 1: A Successful Transfer (COMMIT)

Let’s transfer $200 from Alice to Bob.

-- Start the transaction
BEGIN TRANSACTION;

-- Step 1: Debit Alice's account
UPDATE accounts SET balance = balance - 200.00 WHERE id = 1;

-- Step 2: Credit Bob's account
UPDATE accounts SET balance = balance + 200.00 WHERE id = 2;

-- Check the state within the transaction (optional)
SELECT * FROM accounts ORDER BY id;

-- Make the changes permanent
COMMIT;

After the COMMIT, the changes are saved. A final SELECT * FROM accounts; will show Alice with $800 and Bob with $700.

Example 2: A Failed Transfer (ROLLBACK)

Now, let’s try to transfer $1200 from Alice. This will fail because her balance is only $800, and our table has a CHECK constraint to prevent negative balances.

BEGIN TRANSACTION;

-- Step 1: Attempt to debit Alice's account (this will succeed for now)
UPDATE accounts SET balance = balance - 1200.00 WHERE id = 1;
-- At this point, Alice's balance in this transaction is -400.00

-- In a real application, you might have a logic check here.
-- Let's say we detect the error and decide to abort.

-- Abort the transaction and undo all changes
ROLLBACK;

Because we used ROLLBACK, the attempted debit from Alice’s account is completely undone. A final SELECT * FROM accounts; will show that the balances are unchanged from the end of the previous successful transaction (Alice $800, Bob $700).

Advanced Control: SAVEPOINT

For complex transactions, you might want to roll back only a part of the work. SAVEPOINT allows you to set named markers within a transaction. You can then use ROLLBACK TO savepoint_name to undo changes made after that marker, without aborting the entire transaction.

BEGIN TRANSACTION;

-- Initial update
UPDATE accounts SET balance = balance - 50.00 WHERE id = 1; -- Alice now at $750

SAVEPOINT after_debit;

-- A second operation that we might want to undo
UPDATE accounts SET balance = balance + 1000.00 WHERE id = 2; -- Bob now at $1700

-- Something went wrong with the credit. Let's undo just that part.
ROLLBACK TO after_debit;

-- Now, Bob's balance is back to $700, but Alice's is still $750.

-- Finalize the transaction
COMMIT;

After this transaction, Alice will have $750 and Bob will have $700. The partial rollback was successful.

Common Pitfalls

• Orphaned Transactions: Forgetting to COMMIT or ROLLBACK in an application can leave transactions open. These idle transactions can hold locks on rows, blocking other users and consuming server resources.
• Overly Long Transactions: Avoid performing slow or user-interactive operations inside a transaction. Keep transactions as short and fast as possible to minimize locking and improve concurrency.