Mistake: choosing a stronger transaction isolation level simply to prevent every possible anomaly. Stronger levels protect against more anomalies, but they can make concurrent operations wait for incompatible locks, reducing performance and potentially causing deadlocks. section 10.11
The reporting transaction that blocks work
A reporting user begins a transaction and runs a query against ImpressionsArchive. While that transaction remains active, a data steward updates or deletes rows in the same table. Under a strong isolation level, the operations can wait on each other; in a deadlock, SQL Server rolls back the transaction it considers least expensive. section 10.11
Use these rules when lock contention affects performance:
- Use Read Committed unless a legitimate requirement, such as preventing phantom reads during an actuarial calculation, demands stronger isolation. section 10.11
- Consider Read Committed Snapshot or Snapshot when contention causes performance problems, provided TempDB has sufficient free space and is not I/O bound. section 10.14
- Diagnose the root cause before adding hardware, and avoid treating extra capacity as a substitute for correcting the workload or isolation choice. section 10.14
