SQL Transactions

A transaction is a logical unit of work that contains one or more SQL statements executed as a single, atomic operation. Either all the SQL statements in the transaction complete successfully, or none of them are applied, restoring the database to its previous state.

Transactions safeguard data integrity against hardware failures, software crashes, network interruptions, and concurrent user modifications.

The ACID Properties

To ensure data reliability, every Relational Database Management System (RDBMS) enforces four core transactional properties, collectively known as ACID:

                       +-------------------+
                       |    A C I D        |
                       +-------------------+
                       |  Atomicity        |  --> All or Nothing
                       |  Consistency      |  --> Valid States Only
                       |  Isolation        |  --> Independent Concurrent Work
                       |  Durability       |  --> Permanent Once Committed
                       +-------------------+

Atomicity: Guarantees that all operations within a transaction complete successfully. If any single statement fails, the entire transaction is aborted and rolled back.
Consistency: Ensures that a transaction brings the database from one valid state to another, strictly satisfying all defined schema rules, constraints, and triggers.
Isolation: Controls how concurrent transactions see each other's uncommitted data changes. High isolation prevents data corruption, while low isolation increases concurrency performance.
Durability: Guarantees that once a transaction has been committed, its modifications are permanently recorded in non-volatile memory (disk), surviving system crashes or power outages.

Transaction Control Commands (TCL)

Database engines use specific Transaction Control Language (TCL) statements to manage transactional boundaries:

Command	Purpose
`BEGIN TRANSACTION` / `START TRANSACTION`	Marks the starting boundary of a manual transaction block.
`COMMIT`	Saves all changes made during the current transaction permanently to the database.
`ROLLBACK`	Undoes all uncommitted changes made during the current transaction, returning data to its initial state.
`SAVEPOINT`	Establishes a named savepoint within a transaction to allow partial rollbacks.

Classic Banking Example: Money Transfer

Transferring money between accounts requires two operations: deducting money from the sender and adding money to the recipient. Using a transaction ensures money cannot disappear if a failure occurs halfway through:

-- PostgreSQL / MySQL / SQL Server Syntax Example
BEGIN TRANSACTION;

    -- Step 1: Deduct $500 from Account A
    UPDATE Accounts 
    SET Balance = Balance - 500.00 
    WHERE AccountID = 1001;

    -- Step 2: Add $500 to Account B
    UPDATE Accounts 
    SET Balance = Balance + 500.00 
    WHERE AccountID = 1002;

    -- Step 3: Check for errors or business rule violations
    -- If Balance goes negative or account is invalid:
    -- ROLLBACK;

-- Step 4: Commit both operations as a single permanent unit
COMMIT;

Working with `SAVEPOINT`

A SAVEPOINT creates a checkpoint within a transaction. This allows you to selectively roll back specific parts of a complex, multi-step transaction without aborting the entire sequence:

BEGIN TRANSACTION;

    INSERT INTO Orders (OrderID, CustomerID, OrderDate) 
    VALUES (5001, 101, CURRENT_DATE);

    -- Create a checkpoint
    SAVEPOINT OrderCreated;

    INSERT INTO OrderItems (OrderID, ProductID, Quantity) 
    VALUES (5001, 202, 1);

    -- An error occurs while inserting an invalid item
    -- Roll back ONLY to the savepoint, keeping the main Order record
    ROLLBACK TO SAVEPOINT OrderCreated;

    -- Add a different valid item instead
    INSERT INTO OrderItems (OrderID, ProductID, Quantity) 
    VALUES (5001, 203, 1);

COMMIT;

Transaction Concurrency & Anomalies

When multiple users query and modify identical table rows concurrently, several data read anomalies can occur if isolation levels are not properly configured:

Dirty Read: A transaction reads data modified by another transaction that has not yet committed. If the second transaction rolls back, the first transaction holds invalid data.
Non-Repeatable Read: A transaction reads the same row twice, but receives different column values because a second transaction updated and committed that row in between the reads.
Phantom Read: A transaction re-executes a search query with a range predicate (e.g., WHERE Price > 100), but discovers new rows added by another committed transaction.

Transaction Isolation Levels

The ANSI/ISO SQL standard defines four transaction isolation levels to balance data integrity against system concurrency:

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads	Concurrency / Speed
`READ UNCOMMITTED`	Allowed	Allowed	Allowed	Highest
`READ COMMITTED`	Prevented	Allowed	Allowed	Medium
`REPEATABLE READ`	Prevented	Prevented	Allowed	Lower
`SERIALIZABLE`	Prevented	Prevented	Prevented	Lowest

Engine Defaults: Most major database systems (such as PostgreSQL, SQL Server, and Oracle) use READ COMMITTED as their default isolation level. MySQL (InnoDB engine) defaults to REPEATABLE READ.

Setting Isolation Levels

-- Setting isolation level for the active session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
    -- Queries executed under full serializable lock
COMMIT;

Implicit vs. Explicit Transactions

Implicit (Autocommit) Mode: By default, database engines operate in autocommit mode, wrapping every individual SQL statement (INSERT, UPDATE, DELETE) in its own automatic transaction that commits immediately upon execution.
Explicit Transactions: Declared manually using BEGIN TRANSACTION / START TRANSACTION, requiring an explicit COMMIT or ROLLBACK command to close the boundary.