Azure SQL/SQL Server Transaction Isolation Levels

Control how one transaction is affected by others executing concurrently, balancing concurrency and data consistency.

Artifacts in read data due to isolation level:

Dirty Read: Reading uncommitted changes from other transactions that could be rolled back later.
Non-Repeatable Read: Getting different values when re-reading the same row due to updates by other transactions.
Phantom Read: Seeing new or missing rows when re-reading a range due to inserts/deletes by other transactions.

Isolation Level	Prevents Dirty Reads?	Prevents Non-Repeatable Reads?	Prevents Phantom Reads?	Degree of Concurrency	Additional I/O & TempDB Usage	Use-Cases & Remarks
READ UNCOMMITTED						Example Use: Generating approximate reports in real-time dashboards.
READ COMMITTED (w/READ_COMMITTED_SNAPSHOT on)						Example Use: Typical OLTP workloads. The default isolation level in Azure SQL. Uses row versioning to prevent dirty reads without blocking.
READ COMMITTED (w/READ_COMMITTED_SNAPSHOT off)						Example Use: OLTP workloads where increased I/O & TempDB usage is a problem. The default isolation level in SQL Server. Uses locks to prevent dirty reads.
REPEATABLE READ						Example Use: Financial applications calculating intermediate results based on multiple reads.
SNAPSHOT						Example Uses: Reporting and analytics workloads requiring a consistent snapshot of data. Ensuring application sees stable/coherent snapshots when querying change-tracked data. Uses versioning to provide a consistent view of data from start of transaction without blocking.
SERIALIZABLE						Example Use: Financial applications where transactions involve critical integrity constraints. Behaves like only one transaction can access data at a time.

Test your knowledge:

READ UNCOMMITTED

Prevents Dirty Reads?:

Source: https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide

ScholarChart.com

v4 (Dec. 29, 2024)