Transaction Management and ACID Properties

Difficulty: Advanced
Generated on: 2025-07-10 02:32:41
Category: SQL Cheatsheet for Database Development

---

SQL Transaction Management & ACID Properties Cheatsheet (Advanced)

1. Quick Overview

What is it? Transaction management ensures that a series of database operations are treated as a single, indivisible unit of work. Either all operations succeed (commit), or all operations fail (rollback), maintaining data integrity. ACID properties (Atomicity, Consistency, Isolation, Durability) are the core principles that guarantee reliable transaction behavior.

When to use it? Whenever you need to perform multiple related database operations that must either all succeed or all fail as a unit. Examples include transferring funds between accounts, placing an order involving multiple tables (customers, orders, products), or updating multiple related records.

2. Syntax

-- Starting a Transaction
START TRANSACTION;  -- or BEGIN; or BEGIN TRANSACTION; (DBMS specific)

-- Performing Database Operations
INSERT INTO accounts (account_id, balance) VALUES (123, 1000);
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;

-- Committing the Transaction (make changes permanent)
COMMIT;

-- Rolling Back the Transaction (undo changes)
ROLLBACK;

-- Setting Savepoints (creating checkpoints within a transaction)
SAVEPOINT savepoint_name;

-- Rolling back to a Savepoint
ROLLBACK TO savepoint_name;

-- Releasing a Savepoint
RELEASE SAVEPOINT savepoint_name;  -- Not supported by all databases

-- Setting Isolation Level (see Isolation Levels section below)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- Example

3. Common Use Cases

• Banking Transactions: Transferring funds between accounts.
• E-commerce Orders: Creating an order involves inserting data into customer, order, order_items, and inventory tables.
• Inventory Management: Updating stock levels after a sale.
• Data Migration: Bulk loading or transforming data where failure should result in a complete rollback.
• Multi-Table Updates: Updating related data across multiple tables, such as updating a customer’s address and their related order records.

4. Best Practices

• Keep Transactions Short: Long-running transactions can lead to lock contention and performance problems.
• Minimize Lock Duration: Design queries to acquire locks for the shortest possible time.
• Use Appropriate Isolation Levels: Balance data consistency with concurrency requirements. Lower isolation levels improve concurrency but may introduce anomalies.
• Handle Exceptions: Implement error handling to rollback transactions in case of failures.
• Avoid User Interaction within Transactions: User input can introduce unpredictable delays.
• Index Optimization: Ensure appropriate indexes are in place to optimize the queries within the transaction.
• Connection Pooling: Use connection pooling to reduce the overhead of establishing and closing database connections.
• Deadlock Detection: Implement deadlock detection and resolution strategies. Most databases have built-in deadlock detection mechanisms.
• Monitor Transaction Performance: Use database monitoring tools to identify slow-running transactions and potential bottlenecks.

5. Examples

Example 1: Bank Transfer

-- Sample Data
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2) NOT NULL
);

INSERT INTO accounts (account_id, balance) VALUES (123, 1000.00);
INSERT INTO accounts (account_id, balance) VALUES (456, 500.00);

-- Transaction to transfer funds
START TRANSACTION;

UPDATE accounts SET balance = balance - 200.00 WHERE account_id = 123;  -- Debit account
UPDATE accounts SET balance = balance + 200.00 WHERE account_id = 456;  -- Credit account

-- Simulate an error to test rollback
-- SELECT 1/0; -- This will cause a divide by zero error

COMMIT;  -- or ROLLBACK; if an error occurs

-- Verify the results
SELECT * FROM accounts;

Expected Output after COMMIT:

account_id	balance
123	800.00
456	700.00

Expected Output after ROLLBACK (if the divide-by-zero error happened):

account_id	balance
123	1000.00
456	500.00

Example 2: Order Placement

-- Sample Data (simplified)
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    stock_quantity INT
);

INSERT INTO customers (customer_id, name) VALUES (1, 'John Doe');
INSERT INTO products (product_id, name, price, stock_quantity) VALUES (101, 'Laptop', 1200.00, 10);
INSERT INTO products (product_id, name, price, stock_quantity) VALUES (102, 'Mouse', 25.00, 50);

-- Order Placement Transaction
START TRANSACTION;

-- Create a new order
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1001, 1, CURDATE());  -- CURDATE() or equivalent date function

-- Add order items
INSERT INTO order_items (order_item_id, order_id, product_id, quantity) VALUES (1, 1001, 101, 1);
INSERT INTO order_items (order_item_id, order_id, product_id, quantity) VALUES (2, 1001, 102, 2);

-- Update product stock
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 101;
UPDATE products SET stock_quantity = stock_quantity - 2 WHERE product_id = 102;

COMMIT;

-- Verify Results
SELECT * FROM orders WHERE order_id = 1001;
SELECT * FROM order_items WHERE order_id = 1001;
SELECT * FROM products WHERE product_id IN (101, 102);

Expected Output after COMMIT: The tables orders, order_items will have new records, and the stock_quantity in the products table for product IDs 101 and 102 will be decreased accordingly.

Example 3: Using Savepoints

START TRANSACTION;

INSERT INTO accounts (account_id, balance) VALUES (789, 50.00);

SAVEPOINT after_insert;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 123;

-- Oops, something went wrong!  Let's rollback to the savepoint
ROLLBACK TO after_insert;

-- Correct the mistake and continue
UPDATE accounts SET balance = balance + 50 WHERE account_id = 123;

COMMIT;

In this example, if an error occurred after updating account 123, we can rollback to the after_insert savepoint, effectively undoing the update but keeping the new account inserted.

6. Common Pitfalls

• Forgetting to Commit or Rollback: Leaving a transaction open can lock resources and cause performance issues. Always ensure transactions are explicitly committed or rolled back.
• Deadlocks: Occur when two or more transactions are blocked indefinitely, waiting for each other to release locks. Understand deadlock detection mechanisms and retry strategies.
• Long-Running Transactions: Can block other operations and increase the risk of deadlocks. Break down large transactions into smaller units if possible.
• Incorrect Isolation Level: Using an inappropriate isolation level can lead to data inconsistencies or performance degradation. Choose the level that balances consistency and concurrency.
• Unnecessary Locking: Avoid acquiring locks unnecessarily. Use techniques like optimistic locking where appropriate.
• Not Handling Exceptions: Failing to handle exceptions within a transaction can lead to inconsistent data. Always rollback on exceptions.
• Implicit Transactions: Some database clients or frameworks might implicitly start transactions. Be aware of this behavior and manage transactions explicitly.

7. Database Variations

Feature	MySQL	PostgreSQL	SQL Server	Oracle
Transaction Start	START TRANSACTION; or BEGIN;	BEGIN; or START TRANSACTION;	BEGIN TRANSACTION;	SET TRANSACTION READ WRITE; (implicit start)
Commit	COMMIT;	COMMIT;	COMMIT TRANSACTION;	COMMIT;
Rollback	ROLLBACK;	ROLLBACK;	ROLLBACK TRANSACTION;	ROLLBACK;
Savepoints	SAVEPOINT savepoint_name;, ROLLBACK TO savepoint_name;, RELEASE SAVEPOINT savepoint_name;	SAVEPOINT savepoint_name;, ROLLBACK TO SAVEPOINT savepoint_name;, RELEASE SAVEPOINT savepoint_name;	SAVE TRANSACTION savepoint_name;, ROLLBACK TRANSACTION savepoint_name; (no RELEASE)	SAVEPOINT savepoint_name;, ROLLBACK TO savepoint_name; (no RELEASE)
Isolation Levels	READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE	READ COMMITTED, REPEATABLE READ, SERIALIZABLE	READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE, SNAPSHOT	READ COMMITTED, SERIALIZABLE
Setting Isolation	SET TRANSACTION ISOLATION LEVEL ...;	SET TRANSACTION ISOLATION LEVEL ...;	SET TRANSACTION ISOLATION LEVEL ...;	SET TRANSACTION ISOLATION LEVEL ...;
Deadlock Detection	Automatic	Automatic	Automatic	Automatic
Locking Mechanism	Row-level locking (InnoDB), Table-level locking (MyISAM)	Row-level locking	Row-level locking, Page-level locking, Table-level locking	Row-level locking

ACID Properties Explained

• Atomicity: Ensures that a transaction is treated as a single, indivisible unit of work. Either all changes within the transaction are applied, or none are.
• Consistency: Guarantees that a transaction brings the database from one valid state to another. It enforces database constraints and rules.
• Isolation: Determines the degree to which transactions are isolated from each other. It prevents interference between concurrent transactions. Isolation levels control how different transactions see each other’s uncommitted changes.
• Durability: Ensures that once a transaction is committed, its changes are permanent and will survive even system failures.

Isolation Levels (Detailed)

Isolation levels control the degree to which transactions are isolated from each other. Higher isolation levels provide greater data consistency but can reduce concurrency.

Isolation Level	Description	Potential Issues
READ UNCOMMITTED	Allows a transaction to read uncommitted changes made by other transactions. This is the lowest isolation level.	Dirty Reads: Reading uncommitted data that may be rolled back. Non-Repeatable Reads: Reading the same data twice within a transaction may yield different results due to other transactions modifying the data. Phantom Reads: New rows that satisfy a query condition appear during a transaction.
READ COMMITTED	Only allows a transaction to read committed changes made by other transactions. This prevents dirty reads. Most databases default to this level.	Non-Repeatable Reads: Reading the same data twice within a transaction may yield different results due to other transactions modifying the data. Phantom Reads: New rows that satisfy a query condition appear during a transaction.
REPEATABLE READ	Guarantees that if a transaction reads a row, subsequent reads of that row within the same transaction will return the same value, unless the transaction itself modifies the row. This prevents non-repeatable reads.	Phantom Reads: New rows that satisfy a query condition appear during a transaction.
SERIALIZABLE	Provides the highest level of isolation. Transactions are executed as if they were executed serially, one after the other. This prevents dirty reads, non-repeatable reads, and phantom reads. It effectively locks the database for the duration of the transaction.	Lower concurrency. Can lead to performance issues if not used carefully.
SNAPSHOT	(SQL Server Specific) Provides transaction isolation based on the state of the data as it existed at the beginning of the transaction. Readers do not block writers, and writers do not block readers. Requires the ALLOW_SNAPSHOT_ISOLATION database option to be enabled.	Requires additional database configuration.

This cheat sheet provides a comprehensive guide to SQL transaction management and ACID properties, covering syntax, common use cases, best practices, examples, common pitfalls, and database variations. Use it as a reference for building robust and reliable database applications. Remember to tailor your approach to the specific requirements of your application and database system.