T-SQL Transactions

Transactions are fundamental to ensuring data integrity and consistency in SQL Server. A transaction is a sequence of operations performed as a single logical unit of work. It must be atomic, meaning all operations within the transaction are completed successfully, or none of them are. This principle is often referred to as ACID properties (Atomicity, Consistency, Isolation, Durability).

What is a Transaction?

In SQL Server, a transaction begins when the first data modification statement (like INSERT, UPDATE, or DELETE) is executed. The transaction remains active until it is explicitly ended by a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement, or if the connection is lost.

ACID Properties

• Atomicity: Ensures that all operations within a transaction are treated as a single, indivisible unit. If any part of the transaction fails, the entire transaction is undone, leaving the database in its state before the transaction began.
• Consistency: Guarantees that a transaction brings the database from one valid state to another. It ensures that all database rules, constraints, and triggers are satisfied.
• Isolation: Dictates that concurrent transactions do not interfere with each other. Each transaction appears to be executed in isolation, as if it were the only transaction running. This is managed through various isolation levels.
• Durability: Ensures that once a transaction has been committed, its changes are permanent and will survive any subsequent system failures, such as power outages or crashes.

Managing Transactions

You can manage transactions in T-SQL using the following commands:

BEGIN TRANSACTION

Explicitly starts a transaction. While SQL Server automatically starts transactions implicitly, BEGIN TRANSACTION allows for explicit control, especially when chaining multiple operations.

BEGIN TRANSACTION;
-- Perform data modifications here
COMMIT TRANSACTION;

COMMIT TRANSACTION

Ends the current transaction and makes all its changes permanent in the database.

COMMIT TRANSACTION;

ROLLBACK TRANSACTION

Ends the current transaction and undoes all changes made since the transaction began. This is crucial for error handling and maintaining data integrity.

ROLLBACK TRANSACTION;

SAVE TRANSACTION

Creates a save point within a transaction. This allows you to roll back to a specific point within a larger transaction without undoing the entire transaction.

BEGIN TRANSACTION;
-- First set of operations
SAVE TRANSACTION SavePoint1;
-- Second set of operations
-- If an error occurs after SavePoint1:
-- ROLLBACK TRANSACTION SavePoint1; -- Rolls back to SavePoint1
-- If everything is fine:
-- COMMIT TRANSACTION; -- Commits the entire transaction

Transaction Isolation Levels

Isolation levels control how transactions are isolated from each other and how data modifications performed by one transaction are visible to other concurrent transactions. Choosing the right isolation level is a trade-off between data consistency and performance.

Isolation Level	Description	Dirty Reads	Non-repeatable Reads	Phantom Reads
READ UNCOMMITTED	Lowest level. Transactions can see uncommitted data from other transactions.	Allowed	Allowed	Allowed
READ COMMITTED	Default level. Transactions can only see data that has been committed.	Not Allowed	Allowed	Allowed
REPEATABLE READ	Guarantees that if a transaction reads a row, it will re-read the same row with the same values.	Not Allowed	Not Allowed	Allowed
SERIALIZABLE	Highest level. Transactions are serializable, meaning they are executed one after another, as if they were performed serially.	Not Allowed	Not Allowed	Not Allowed
SNAPSHOT	Uses row versioning to provide consistent reads without locking.	Not Allowed	Not Allowed	Not Allowed

You can set the isolation level for a session using the following command:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Example: Transferring Funds

A common scenario for transactions is transferring funds between two accounts. This involves debiting one account and crediting another. Both operations must succeed or fail together.

-- Assume AccountA and AccountB tables exist with a 'Balance' column.
-- Assume AccountID1 = 101, AccountID2 = 102, TransferAmount = 50

BEGIN TRANSACTION;

DECLARE @TransferAmount DECIMAL(10, 2) = 50.00;
DECLARE @AccountID1 INT = 101;
DECLARE @AccountID2 INT = 102;

BEGIN TRY
    -- Debit Account 1
    UPDATE Accounts
    SET Balance = Balance - @TransferAmount
    WHERE AccountID = @AccountID1;

    -- Check if debit was successful (e.g., insufficient funds)
    IF @@ROWCOUNT = 0 OR (SELECT Balance FROM Accounts WHERE AccountID = @AccountID1) < 0
    BEGIN
        THROW 51000, 'Debit failed. Insufficient funds or invalid account.', 1;
    END

    -- Credit Account 2
    UPDATE Accounts
    SET Balance = Balance + @TransferAmount
    WHERE AccountID = @AccountID2;

    -- Check if credit was successful
    IF @@ROWCOUNT = 0
    BEGIN
        THROW 51000, 'Credit failed. Invalid account.', 1;
    END

    -- If both operations are successful, commit the transaction
    COMMIT TRANSACTION;
    PRINT 'Fund transfer successful!';

END TRY
BEGIN CATCH
    -- If any error occurred, rollback the entire transaction
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    PRINT 'Fund transfer failed. Transaction rolled back.';
    -- Log the error details if necessary
    PRINT ERROR_MESSAGE();
END CATCH;

For more detailed information, refer to the official Microsoft T-SQL Transactions documentation.