SQL Transactions

Last updated: October 26, 2023

Transactions are a fundamental concept in database management, ensuring data integrity and consistency. A transaction is a sequence of operations performed as a single logical unit of work. All operations within a transaction must succeed for the transaction to be committed; otherwise, the entire transaction must be rolled back.

Understanding Transactions

Transactions are governed by the ACID properties:

• Atomicity: Ensures that a transaction is treated as a single, indivisible unit. Either all operations within the transaction are performed, or none are.
• Consistency: Guarantees that a transaction brings the database from one valid state to another. It prevents invalid data from being written to the database.
• Isolation: Dictates that concurrent transactions do not interfere with each other. Each transaction appears to execute in isolation from others.
• Durability: Ensures that once a transaction has been committed, it remains committed even in the event of system failures (e.g., power outages, crashes).

Key Transaction Statements

Most SQL database systems support the following core transaction control statements:

BEGIN TRANSACTION / START TRANSACTION

This statement initiates a new transaction. Subsequent SQL statements are considered part of this transaction until a COMMIT or ROLLBACK is issued.

BEGIN TRANSACTION;
-- Or for some systems:
-- START TRANSACTION;

COMMIT

This statement saves all changes made within the current transaction. Once committed, the changes are permanent and visible to other transactions.

COMMIT;

ROLLBACK

This statement undoes all changes made within the current transaction. If the transaction has not yet been committed, all operations performed since the BEGIN TRANSACTION will be discarded, and the database will be restored to its state before the transaction began.

ROLLBACK;

SAVEPOINT

SAVEPOINT allows you to set intermediate points within a transaction. You can then roll back to a specific savepoint without undoing the entire transaction.

BEGIN TRANSACTION;
INSERT INTO Products (Name, Price) VALUES ('Gadget', 19.99);
SAVEPOINT MySavePoint;
INSERT INTO Orders (ProductID, Quantity) VALUES (1, 5);
-- If something goes wrong here, you can roll back to MySavePoint:
-- ROLLBACK TO SAVEPOINT MySavePoint;
-- Otherwise, commit the whole transaction:
COMMIT;

Example Scenario

Consider a simple e-commerce scenario where you need to deduct an item from inventory and create an order. Both operations must succeed or fail together.

BEGIN TRANSACTION;

-- Check if enough stock is available
DECLARE @StockCount INT;
SELECT @StockCount = StockQuantity FROM Products WHERE ProductID = 101;

IF @StockCount >= 1
BEGIN
    -- Deduct from inventory
    UPDATE Products
    SET StockQuantity = StockQuantity - 1
    WHERE ProductID = 101;

    -- Create the order
    INSERT INTO Orders (OrderID, ProductID, Quantity, OrderDate)
    VALUES (1001, 101, 1, GETDATE());

    -- Commit the transaction if both operations were successful
    COMMIT TRANSACTION;
    PRINT 'Order placed successfully and inventory updated.';
END
ELSE
BEGIN
    -- Rollback if not enough stock
    ROLLBACK TRANSACTION;
    PRINT 'Insufficient stock. Transaction rolled back.';
END;

Transaction Isolation Levels

Database systems provide different isolation levels to control how transactions interact with each other. Higher isolation levels provide stronger guarantees but can sometimes lead to performance issues due to increased locking. Common levels include:

• READ UNCOMMITTED: Allows transactions to read uncommitted data from other transactions (lowest isolation).
• READ COMMITTED: Ensures that transactions only read data that has been committed. This is the default level in many RDBMS.
• REPEATABLE READ: Guarantees that if a transaction reads a row multiple times, it will see the same data each time, as long as the row hasn't been updated by another committed transaction.
• SERIALIZABLE: The highest isolation level, ensuring that concurrent transactions execute as if they were run serially (one after another).

You can typically set the isolation level for a specific session or transaction:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Best Practices

• Keep transactions as short as possible to minimize the time locks are held.
• Handle potential errors gracefully and always include ROLLBACK logic.
• Choose the appropriate isolation level for your application's needs.
• Avoid long-running operations (like user input or network calls) within a transaction.