SQL Transactions

A SQL transaction is a sequence of one or more SQL operations that are performed as a single, indivisible unit of work. Transactions are fundamental to maintaining data integrity and consistency in relational database systems. They ensure that a set of operations either all succeed or all fail, preventing partial updates that could leave the database in an inconsistent state.

ACID Properties

SQL transactions adhere to the ACID properties:

• Atomicity: Ensures that all operations within a transaction are completed successfully. If any operation fails, the entire transaction is rolled back, and the database remains unchanged.
• Consistency: Guarantees that a transaction brings the database from one valid state to another. It ensures that all database constraints (like primary keys, foreign keys, and check constraints) are met after the transaction is committed.
• Isolation: Ensures that concurrent transactions do not interfere with each other. Each transaction appears to execute as if it were the only transaction running on the system. This is typically achieved through locking mechanisms and various isolation levels.
• Durability: Guarantees that once a transaction has been committed, its changes are permanent and will survive any subsequent system failures, such as power outages or crashes. This is usually achieved through transaction logging.

Managing Transactions

The primary SQL statements for managing transactions are:

• BEGIN TRANSACTION (or START TRANSACTION): Marks the beginning of a transaction.
• COMMIT TRANSACTION: Saves all changes made during the transaction.
• ROLLBACK TRANSACTION: Undoes all changes made during the transaction.

Example: Transferring Funds

Consider a common scenario: transferring money from one bank account to another. This requires two operations:

1. Debit the source account.
2. Credit the destination account.

These operations must be performed atomically. Here's how it might look in SQL:

BEGIN TRANSACTION;

-- Debit the source account
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 101;

-- Credit the destination account
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 202;

-- Check if the operations were successful (optional, can be done via error handling)
IF @@ERROR = 0
BEGIN
    COMMIT TRANSACTION;
    PRINT 'Transaction committed successfully.';
END
ELSE
BEGIN
    ROLLBACK TRANSACTION;
    PRINT 'Transaction rolled back due to an error.';
END
            

Transaction Isolation Levels

Database systems provide different transaction isolation levels to balance concurrency and data consistency. The choice of isolation level affects how transactions interact and the potential for concurrency issues like dirty reads, non-repeatable reads, and phantom reads.

Common Isolation Levels (SQL Server):

• READ UNCOMMITTED: Transactions can read uncommitted data from other transactions (prone to dirty reads).
• READ COMMITTED: Transactions can only read data that has been committed by other transactions. This is often the default level.
• REPEATABLE READ: Guarantees that if a transaction reads a row multiple times, it will always see the same data. Prevents dirty reads and non-repeatable reads, but not phantom reads.
• SERIALIZABLE: The highest isolation level. Transactions are executed as if they were run one after another (serially), preventing all concurrency phenomena. This can significantly impact performance.
• SNAPSHOT: A newer isolation level that uses row versioning to provide consistent reads without locking. Transactions read the version of the data as it existed when the transaction began.

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

-- Set isolation level for the current session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;
    -- Transaction operations...
COMMIT TRANSACTION;
            

Savepoints

Savepoints allow you to roll back a transaction to a specific point within its execution, rather than rolling back the entire transaction. This is useful for complex transactions where you might want to undo only a portion of the work.

BEGIN TRANSACTION;
    -- First set of operations
    UPDATE SomeTable SET Value = 'A' WHERE ID = 1;

    -- Create a savepoint
    SAVE TRANSACTION MySavePoint;

    -- Second set of operations
    UPDATE AnotherTable SET Status = 'Processing' WHERE ID = 5;

    -- If an error occurs in the second set, rollback to the savepoint
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION MySavePoint;
        PRINT 'Rolled back to MySavePoint.';
    END

    -- If second set was successful, potentially continue or commit
    COMMIT TRANSACTION;