Database Transactions: ACID Properties

Database Transactions: ACID Properties

A database transaction is a sequence of one or more operations treated as a single unit of work. Either all operations in the transaction complete successfully, or none of them take effect. Transactions are essential for maintaining data integrity, especially when multiple related changes must happen together or when multiple users access the database concurrently.

Think of a bank transfer: you withdraw $100 from account A and deposit $100 into account B. If the deposit fails after the withdrawal succeeds, money disappears. A transaction ensures that either both operations succeed, or both are rolled back, leaving the database in a consistent state. To understand transactions properly, it is helpful to be familiar with SQL basics, relational database design, and database indexing.

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

ACID Properties:
Atomicity     → All or nothing
Consistency   → Valid state to valid state
Isolation     → Concurrent transactions don't interfere
Durability    → Committed changes survive crashes

What Is a Database Transaction

A database transaction is a logical unit of work that consists of one or more SQL statements. Transactions have a clear beginning and end. They allow you to group operations so that the database is never left in an inconsistent state, even if errors occur or the system crashes.

• BEGIN / START TRANSACTION: Marks the beginning of a transaction.
• COMMIT: Permanently saves all changes made in the transaction.
• ROLLBACK: Undoes all changes made in the transaction.
• SAVEPOINT: Creates a point within a transaction to roll back to partially.
• Autocommit: In many databases, each individual statement is its own transaction by default.

BEGIN;

-- Try to insert a new order
INSERT INTO orders (customer_id, total) VALUES (123, 150.00);

-- Get the order ID
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (LAST_INSERT_ID(), 456, 2);

-- Update inventory
UPDATE products SET stock = stock - 2 WHERE id = 456;

-- If all succeeded, commit
COMMIT;

-- If any error occurred, rollback instead
-- ROLLBACK;

Why Transactions Matter

Transactions are fundamental to data integrity. Without transactions, databases would be unreliable and unsafe for any application that requires data consistency.

• Data Integrity: Transactions ensure that related changes happen together or not at all.
• Error Recovery: If an error occurs mid-operation, you can roll back to a known good state.
• Concurrency Control: Transactions isolate concurrent operations to prevent interference.
• Crash Recovery: Committed transactions survive system crashes; uncommitted changes are rolled back.
• Audit Compliance: Transactions provide clear boundaries for auditing and logging.
• Business Logic Integrity: Complex business rules that involve multiple steps can be enforced atomically.

ACID Properties

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. These four properties guarantee that database transactions are processed reliably.

Atomicity

Atomicity ensures that a transaction is treated as a single, indivisible unit. Either all operations in the transaction complete successfully, or none of them take effect. There is no partial completion.

BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Success
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Fails (account doesn't exist)
ROLLBACK;

-- Result: First update is also undone. Account 1 balance unchanged.

Consistency

Consistency ensures that a transaction brings the database from one valid state to another valid state. All defined rules, constraints, and triggers are enforced. The database never violates its own integrity rules.

-- Foreign key constraint: order must reference existing customer
BEGIN;
    INSERT INTO orders (customer_id, total) VALUES (999, 100.00);
    -- Fails because customer 999 doesn't exist
ROLLBACK;

-- Check constraint: balance cannot be negative
BEGIN;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    -- Fails if balance would become negative
ROLLBACK;

Isolation

Isolation ensures that concurrently executing transactions do not interfere with each other. Each transaction appears to run as if it is the only transaction in the system, even though many may be running simultaneously.

Transaction A                    Transaction B
BEGIN;                           BEGIN;
    SELECT balance FROM accounts WHERE id = 1;  -- $100
                                     UPDATE accounts SET balance = balance - 50 WHERE id = 1;
                                     COMMIT;
    UPDATE accounts SET balance = balance - 50 WHERE id = 1;
    -- Transaction A still sees $100 (not $50)
COMMIT;
-- Final balance: $0 (correct, not -$50)

Durability

Durability guarantees that once a transaction has been committed, its changes persist even if the system crashes immediately afterward. The database ensures that committed data is written to non-volatile storage.

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

-- System crashes 1ms after COMMIT
-- When database restarts, both updates are permanent
-- The transaction is not lost or partially applied

Transaction Commands

BEGIN;
    INSERT INTO logs (message) VALUES ('Starting process');
    
    SAVEPOINT before_update;
    
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    
    -- Something went wrong, but we don't want to lose the log
    ROLLBACK TO SAVEPOINT before_update;
    
    -- Log entry remains, account update is undone
    INSERT INTO logs (message) VALUES ('Update failed, rolled back');
COMMIT;

Autocommit Mode

Most databases have autocommit enabled by default, meaning each individual SQL statement is its own transaction. You must explicitly disable autocommit to group multiple statements.

-- MySQL (autocommit is on by default)
SET autocommit = 0;  -- Disable autocommit
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
SET autocommit = 1;  -- Re-enable autocommit

-- PostgreSQL (autocommit is on by default in psql)
BEGIN;  -- Explicitly start transaction
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Common Transaction Patterns

Optimistic Locking

Optimistic locking assumes conflicts are rare. It uses version numbers to detect conflicts at commit time rather than locking rows.

-- Table with version column
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    version INT DEFAULT 0
);

-- Update with optimistic locking
BEGIN;
    SELECT price, version INTO @price, @version FROM products WHERE id = 1;
    -- Application calculates new price
    UPDATE products SET price = @new_price, version = @version + 1
    WHERE id = 1 AND version = @version;
    
    IF ROW_COUNT() = 0 THEN
        ROLLBACK;
        -- Conflict detected, retry
    ELSE
        COMMIT;
    END IF;

Pessimistic Locking

Pessimistic locking locks rows to prevent other transactions from modifying them. Use SELECT ... FOR UPDATE.

BEGIN;
    -- Lock the row for update
    SELECT balance INTO @balance FROM accounts WHERE id = 1 FOR UPDATE;
    
    -- Other transactions cannot modify this row until COMMIT
    UPDATE accounts SET balance = @balance - 100 WHERE id = 1;
    
COMMIT;  -- Lock released

Transaction Isolation Levels

Isolation levels define how much one transaction can see the uncommitted changes of another transaction. Higher isolation provides more consistency but reduces concurrency.

-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
    SELECT * FROM accounts WHERE id = 1;
COMMIT;

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    SELECT * FROM accounts WHERE id = 1;
COMMIT;

Common Transaction Mistakes to Avoid

• Long-Running Transactions: Transactions that run for a long time hold locks, causing contention and deadlocks. Keep transactions short.
• Forgetting to Commit or Rollback: Uncommitted transactions hold locks and can cause connection pool exhaustion.
• Ignoring Deadlocks: Deadlocks happen. Always implement retry logic for deadlock failures.
• Not Handling Errors: Always catch exceptions and roll back on error. Do not leave transactions open.
• Mixing Business Logic with Transactions: Keep transaction boundaries around database operations only. Do not include user input or external API calls inside transactions.
• Using the Wrong Isolation Level: Serializable is rarely needed. Read Committed is sufficient for most applications.

import time

def execute_with_retry(query, max_retries=3):
    for attempt in range(max_retries):
        try:
            begin()
            execute(query)
            commit()
            return
        except DeadlockError:
            rollback()
            time.sleep(0.1 * (2 ** attempt))  # Exponential backoff
            continue
    raise Exception("Max retries exceeded")

Transaction Best Practices

• Keep Transactions Short: Minimize the time between BEGIN and COMMIT. Do not include user interaction or network calls.
• Access Tables in Consistent Order: To avoid deadlocks, always access tables in the same order across transactions.
• Use Appropriate Isolation Level: Read Committed is sufficient for most applications. Increase isolation only when needed.
• Always Handle Errors: Use try-catch-finally to ensure rollback on errors and commit on success.
• Test Under Load: Concurrency issues often appear only under load. Test with realistic concurrency.
• Monitor Transaction Duration: Set up alerts for long-running transactions.
• Use Connection Pools Properly: Ensure transactions are committed or rolled back before returning connections to the pool.

Frequently Asked Questions

1. What is the difference between COMMIT and ROLLBACK?
   COMMIT permanently saves all changes made in the transaction. ROLLBACK undoes all changes made in the transaction. After COMMIT, changes cannot be undone (except with a new transaction).
2. Can I roll back after COMMIT?
   No. Once a transaction is committed, the changes are permanent. To undo them, you need to run a new transaction that reverses the changes.
3. What is a nested transaction?
   Some databases support nested transactions where an inner transaction can be rolled back independently of the outer transaction. Savepoints provide similar functionality.
4. Do all databases support transactions?
   Most relational databases (PostgreSQL, MySQL with InnoDB, SQL Server, Oracle) support transactions. Some NoSQL databases have limited transaction support (MongoDB 4.0+ has multi-document transactions).
5. What happens if the database crashes during a transaction?
   When the database restarts, it recovers by rolling back any uncommitted transactions and ensuring committed transactions are durable. This is handled automatically by the database's recovery system.
6. What should I learn next after database transactions?
   After mastering transactions, explore isolation levels in depth, deadlock detection and prevention, concurrency control, and distributed transactions for advanced transaction management.