SQL Transactions and ACID Properties: Complete Guide

Why Transactions Are Essential for SQL Developers

Transactions are the foundation of reliable database operations. They ensure that your data remains consistent even when multiple operations must succeed together, when errors occur, or when multiple users access the database simultaneously. Without transactions, you risk partial updates, data corruption, and lost changes.

Whether you're building a banking application where money transfers must be atomic, an e-commerce platform where inventory and orders must stay synchronized, or any application where data integrity matters, understanding transactions is critical. The ACID properties (Atomicity, Consistency, Isolation, Durability) provide the guarantees that make databases trustworthy.

Many developers struggle with transactions because they seem simple at first but reveal complexity when dealing with concurrency, error handling, and performance. This guide covers the essential patterns and best practices you need to use transactions effectively in production systems.

2. Atomicity: All or Nothing Execution

Atomicity guarantees that a transaction is an indivisible unit of work. Either all operations in the transaction complete successfully, or none of them do. There's no middle ground where some changes are applied and others aren't.

-- Atomic order creation
BEGIN TRANSACTION;

-- Create order header
INSERT INTO orders (customer_id, order_date, total)
VALUES (789, CURRENT_TIMESTAMP, 299.99);

-- Get the new order ID
SET @order_id = LAST_INSERT_ID();

-- Add order items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES
  (@order_id, 101, 2, 149.99),
  (@order_id, 205, 1, 149.99);

-- Update inventory
UPDATE products SET stock = stock - 2 WHERE product_id = 101;
UPDATE products SET stock = stock - 1 WHERE product_id = 205;

COMMIT;

If any operation fails (like insufficient inventory), the entire transaction rolls back. You won't end up with an order that has no items or inventory that was decremented without a matching order.

4. Isolation: Managing Concurrent Transactions

Isolation determines how transaction changes are visible to other concurrent transactions. Different isolation levels provide different trade-offs between consistency and concurrency. Understanding these levels helps you prevent anomalies like dirty reads, non-repeatable reads, and phantom rows.

-- Set isolation level for a transaction
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- This transaction can only see committed changes
SELECT balance FROM accounts WHERE account_id = 123;

-- Update based on the balance
UPDATE accounts
SET balance = balance - 50
WHERE account_id = 123;

COMMIT;

Common Isolation Levels

READ UNCOMMITTED: Allows dirty reads. Fastest but least safe. Rarely used in production.

READ COMMITTED: Prevents dirty reads. Default in most databases. Good balance for most applications.

REPEATABLE READ: Prevents dirty and non-repeatable reads. Useful when you need consistent reads within a transaction.

SERIALIZABLE: Strictest level. Prevents all anomalies but can cause performance issues. Use only when necessary.

6. Savepoints: Partial Transaction Rollback

Savepoints let you create checkpoints within a transaction so you can rollback to a specific point without discarding the entire transaction. This is useful for complex operations where you want to keep some changes but undo others.

-- Using savepoints for partial rollback
BEGIN TRANSACTION;

-- Step 1: Create a customer record
INSERT INTO customers (name, email)
VALUES ('John Doe', 'john@example.com');

SAVEPOINT customer_created;

-- Step 2: Try to create an address
INSERT INTO addresses (customer_id, street, city)
VALUES (LAST_INSERT_ID(), '123 Main St', 'Boston');

SAVEPOINT address_created;

-- Step 3: Try to add optional phone number
-- This might fail due to invalid format
INSERT INTO phone_numbers (customer_id, phone)
VALUES (LAST_INSERT_ID(), 'invalid-phone');

-- If phone insert failed, rollback to keep customer and address
ROLLBACK TO SAVEPOINT address_created;

-- Continue with the transaction
COMMIT;

In this example, if adding the phone number fails, we can rollback just that operation while keeping the customer and address records. This gives you fine-grained control over transaction recovery.

When to Use Savepoints

Savepoints are useful for batch operations where some items might fail but you want to continue with the successful ones, for trying optional operations that shouldn't abort the entire transaction, or for implementing nested transaction-like behavior. However, overusing savepoints can make code harder to understand and debug.