T-SQL Transactions in SQL Server

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. SQL Server guarantees the ACID properties (Atomicity, Consistency, Isolation, Durability) for transactions.

ACID Properties

• Atomicity: All operations within a transaction are completed successfully, or none of them are. If any operation fails, the entire transaction is rolled back to its original state.
• Consistency: A transaction brings the database from one valid state to another. It ensures that all database rules and constraints are maintained.
• Isolation: Concurrent transactions do not interfere with each other. Each transaction appears to execute in isolation from others.
• Durability: Once a transaction is committed, its changes are permanent and will survive system failures, such as power outages or crashes.

Transaction Control Language (TCL)

T-SQL provides commands to manage transactions:

BEGIN TRANSACTION

Initiates a new transaction. You can optionally provide a transaction name for easier reference.

BEGIN TRANSACTION MyTransaction;
-- SQL statements here
GO

COMMIT TRANSACTION

Marks the end of a transaction and makes all its changes permanent in the database. This operation commits the transaction.

COMMIT TRANSACTION MyTransaction;
GO

ROLLBACK TRANSACTION

Undoes all changes made during the current transaction since the last BEGIN TRANSACTION. The database is returned to its state before the transaction began.

ROLLBACK TRANSACTION MyTransaction;
GO

SAVE TRANSACTION

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

BEGIN TRANSACTION OuterTransaction;
    -- Some operations
    SAVE TRANSACTION SavePoint1;
    -- More operations
    -- If something goes wrong here, we can roll back to SavePoint1
    ROLLBACK TRANSACTION SavePoint1;
    -- Continue with other operations or rollback the entire transaction
    COMMIT TRANSACTION OuterTransaction;
GO

Transaction Isolation Levels

Isolation levels control how one transaction's modifications are visible to other concurrent transactions. Choosing the appropriate isolation level is crucial for balancing data consistency and concurrency performance.

Default Isolation Level

The default isolation level in SQL Server is READ COMMITTED.

Common Isolation Levels

• READ UNCOMMITTED: The lowest level. Transactions can read uncommitted data ("dirty reads").
• READ COMMITTED: Prevents dirty reads. Transactions only see data that has been committed.
• REPEATABLE READ: Guarantees that if a transaction reads a row multiple times, it will see the same data each time. Prevents non-repeatable reads.
• SERIALIZABLE: The highest level. Transactions are fully isolated from each other. Prevents dirty reads, non-repeatable reads, and phantom reads.

Setting Isolation Levels

You can set the isolation level for the current session using the SET TRANSACTION ISOLATION LEVEL statement.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;
    -- Transaction logic
COMMIT TRANSACTION;
GO

Implicit vs. Explicit Transactions

• Explicit Transactions: You explicitly start a transaction using BEGIN TRANSACTION and end it with COMMIT TRANSACTION or ROLLBACK TRANSACTION. This is the recommended approach for most scenarios.
• Implicit Transactions: When IMPLICIT_TRANSACTIONS is ON, each DML statement automatically starts a transaction. You must then explicitly commit or roll back. This can be less predictable.

Best Practices

• Keep transactions as short as possible to minimize blocking.
• Use savepoints for complex operations where partial rollbacks might be needed.
• Choose the lowest isolation level that meets your application's consistency requirements.
• Handle errors and exceptions properly to ensure transactions are rolled back when necessary.