Transaction Isolation Level

SQL Server Concurrency Problems

Problem	Explanation
Dirty Read	Reading uncommitted changes from another transaction.
Non-Repeatable Read	Reading the same row twice and getting different values.
Phantom Read	Repeating a query returns additional or missing rows due to concurrent inserts or deletes.
Lost Update	One transaction overwrites another transaction's update.
Write Skew	Transactions read the same state and update different rows, jointly violating a business constraint.
Read Skew	Reading related data at different times, resulting in an inconsistent combination.
Dirty Write	Overwriting data modified by another uncommitted transaction.
Deadlock	Transactions wait for each other in a circular dependency. SQL Server aborts one victim.
Livelock	A transaction repeatedly loses opportunities to proceed due to competing transactions.
Starvation	A transaction is continually denied resources or access.
Lock Escalation	SQL Server replaces many fine-grained locks with a table-level or partition-level lock.
Hot Spot	Many transactions compete for the same frequently accessed row or resource.

SQL Server Isolation Levels

Problem	Read Uncommitted	Read Committed	Repeatable Read	Snapshot	Serializable
Dirty Read	Possible	No	No	No	No
Non-Repeatable Read	Possible	Possible	No	No	No
Phantom Read	Possible	Possible	Possible	No	No
Lost Update	Possible	Possible*	Possible*	No**	No
Write Skew	Possible	Possible	Possible	Possible	No
Read Skew	Possible	Possible	Possible	No	No

Notes:

• READ COMMITTED is the default SQL Server isolation level.
• READ_COMMITTED_SNAPSHOT uses row versioning for Read Committed.
• SNAPSHOT provides transaction-level consistent reads.
• SNAPSHOT detects conflicting updates to the same row.
• SERIALIZABLE uses range locks to prevent phantom inserts and serialization anomalies.
• * Lost updates depend on the read-modify-write pattern and locking strategy.
• ** SNAPSHOT aborts one transaction when both transactions attempt conflicting updates to the same row.