SQL Transaction Management

This document provides a comprehensive guide to understanding and implementing transaction management in SQL Server. Transactions are fundamental for ensuring data integrity and consistency in database operations.

What is a Transaction?

A transaction is a sequence of one or more SQL operations performed as a single, atomic unit of work. Either all operations within a transaction are successfully completed, or none of them are. This principle is often referred to as ACID properties:

• Atomicity: Ensures that all operations within a transaction are completed successfully. If any operation fails, the entire transaction is rolled back.
• Consistency: Guarantees that a transaction brings the database from one valid state to another, preserving all database rules and constraints.
• Isolation: Dictates that concurrent transactions do not interfere with each other. Each transaction appears to execute in isolation.
• Durability: Ensures that once a transaction is committed, its changes are permanent and will survive system failures, such as power outages.

Controlling Transactions

SQL Server provides Transact-SQL (T-SQL) statements to explicitly manage transactions.

BEGIN TRANSACTION

Starts a new transaction. Any subsequent Data Manipulation Language (DML) statements are part of this transaction until a COMMIT TRANSACTION or ROLLBACK TRANSACTION is issued.

BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'Sales';
INSERT INTO AuditLog (Message, Timestamp) VALUES ('Sales salary increased', GETDATE());
-- Potential further operations...

COMMIT TRANSACTION

Makes all changes performed within the current transaction permanent. If the transaction was implicitly started, COMMIT TRANSACTION will commit it.

COMMIT TRANSACTION;

ROLLBACK TRANSACTION

Undoes all changes made since the beginning of the transaction or since the last savepoint. This is crucial for error handling.

ROLLBACK TRANSACTION;

SAVE TRANSACTION

Allows you to create savepoints within a transaction. You can then roll back to a specific savepoint without rolling back the entire transaction.

BEGIN TRANSACTION;
UPDATE Products SET StockQuantity = StockQuantity - 1 WHERE ProductID = 101;
SAVE TRANSACTION UpdateStock;
UPDATE Orders SET Status = 'Processing' WHERE OrderID = 5001;
-- If a subsequent operation fails:
-- ROLLBACK TRANSACTION UpdateStock; -- This would only undo the UPDATE Products statement
-- COMMIT TRANSACTION; -- This would commit the successful UPDATE Orders statement if it was the only one after the savepoint

Transaction Isolation Levels

Isolation levels define the degree to which one transaction must remain isolated from the data modifications made by other concurrent transactions. Choosing the appropriate isolation level is a balance between data consistency and concurrency.

• READ UNCOMMITTED: The lowest isolation level. Transactions can read rows that have been modified by other transactions but not yet committed. This can lead to reading "dirty" data.
• READ COMMITTED: The default isolation level in SQL Server. Transactions can only read data that has been committed. This prevents dirty reads but can still experience non-repeatable reads and phantom reads.
• REPEATABLE READ: Guarantees that if a transaction reads a row, subsequent reads of that same row will return the same data. This prevents dirty reads and non-repeatable reads but can still allow 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 reduce concurrency.

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

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

BEGIN TRANSACTION;
-- Operations within this transaction will use READ COMMITTED
COMMIT TRANSACTION;

-- Set for a specific transaction
BEGIN TRANSACTION LevelSnapshot;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
-- Operations within this transaction will use SNAPSHOT
COMMIT TRANSACTION LevelSnapshot;