SQL Server Transactions

Transactions are fundamental to relational database management systems like SQL Server. They provide a mechanism to ensure data integrity and consistency, especially in concurrent environments. A transaction is a logical unit of work that consists of one or more SQL statements.

Understanding Transaction Concepts

The ACID properties are the cornerstone of reliable transaction processing:

• Atomicity: A transaction is an indivisible unit. Either all of its operations are completed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back, leaving the database in its original state.
• Consistency: A transaction must bring the database from one valid state to another. It ensures that any data written to the database can be read later in the same form. Constraints and rules are maintained.
• Isolation: Concurrent transactions should not interfere with each other. The effect of a transaction should be as if it were executed in isolation from other transactions. SQL Server provides different isolation levels to control this.
• Durability: Once a transaction has been committed, its changes are permanent and will survive any subsequent system failures (e.g., power outages or crashes).

Managing Transactions in SQL Server

SQL Server provides Transact-SQL (T-SQL) statements to control transaction boundaries:

BEGIN TRANSACTION

Marks the beginning of a transaction. All subsequent statements are part of this transaction until a COMMIT TRANSACTION or ROLLBACK TRANSACTION is encountered.

BEGIN TRANSACTION;

COMMIT TRANSACTION

Ends the current transaction successfully. All changes made within the transaction are permanently saved to the database.

COMMIT TRANSACTION;

ROLLBACK TRANSACTION

Ends the current transaction by undoing all changes made since the transaction began. This is typically used when an error occurs or a condition requires discarding the work.

ROLLBACK TRANSACTION;

SAVE TRANSACTION

Creates a point within a transaction to which you can later roll back. This allows for partial rollbacks within a larger transaction.

SAVE TRANSACTION SavePointName;

Example Transaction

Consider a scenario where you are transferring funds between two bank accounts. This operation must be atomic: either both the debit and credit occur, or neither does.

BEGIN TRANSACTION;

            -- Debit from Account A
            UPDATE Accounts
            SET Balance = Balance - 100
            WHERE AccountID = 1;

            -- Credit to Account B
            UPDATE Accounts
            SET Balance = Balance + 100
            WHERE AccountID = 2;

            -- Check for sufficient funds (simplified example)
            IF (SELECT Balance FROM Accounts WHERE AccountID = 1) < 0
            BEGIN
                ROLLBACK TRANSACTION;
                PRINT 'Transaction failed: Insufficient funds in Account A.';
            END
            ELSE
            BEGIN
                COMMIT TRANSACTION;
                PRINT 'Transaction successful: Funds transferred.';
            END;

Transaction Isolation Levels

SQL Server offers several isolation levels that control how transactions interact with each other and how data changes are visible to other concurrent transactions. The default isolation level is READ COMMITTED.

Common Isolation Levels:

• READ UNCOMMITTED: The lowest isolation level. Transactions can read rows that have been modified but not yet committed by other transactions (non-repeatable reads, phantom reads possible).
• READ COMMITTED: (Default) Transactions only read data that has been committed. This prevents dirty reads but can still lead to non-repeatable reads and phantom reads.
• REPEATABLE READ: Ensures that if a transaction reads a row, subsequent reads of the same row will return the same data. It prevents non-repeatable reads but can still suffer from phantom reads.
• SERIALIZABLE: The highest isolation level. Transactions are completely isolated from each other, as if they were executed serially. This prevents dirty reads, non-repeatable reads, and phantom reads, but can significantly impact concurrency.

You can set the isolation level for the current session using:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Implicit and Explicit Transactions

SQL Server supports both explicit and implicit transactions:

• Explicit Transactions: You explicitly start a transaction using BEGIN TRANSACTION and end it with COMMIT TRANSACTION or ROLLBACK TRANSACTION.
• Implicit Transactions: When IMPLICIT_TRANSACTIONS is set to ON, each individual SQL statement is automatically enclosed in its own transaction. You still need to explicitly commit or rollback the transaction. This is generally less common for complex operations.

You can control implicit transactions with:

SET IMPLICIT_TRANSACTIONS ON;
            SET IMPLICIT_TRANSACTIONS OFF;