Serializable prevents all consistency anomalies, including phantom reads, but applying it by default can be an unnecessary mistake. Strong isolation may cause lock contention and deadlocks when Read Committed is sufficient. section 10.11 section 10.14
Mistake: protecting every report with Serializable
A reporting user counts ImpressionsArchive rows matching an operating system while a data steward deletes three matching rows and commits. Repeating the report can return a different count under weaker isolation; Serializable is reserved for cases that genuinely require this protection, such as actuarial calculations. section 10.11
Listing 10.27 Beginning the reporting user’s transaction
BEGIN TRANSACTION #1
SELECT COUNT(*)
FROM dbo.ImpressionsArchive
WHERE OperatingSystemID = (
SELECT OperatingSystemID
FROM dbo.ImpressionsArchive
WHERE ImpressionID = 100
) ;
Run the reporting transaction in one query window and the data steward's delete in another, then compare the repeated counts shown in the paired listings. section 10.11
Apply these checks before choosing Serializable:
- Keep Read Committed for conventional requirements unless stronger consistency is necessary. section 10.11
- Consider optimistic isolation when performance suffers from lock contention, provided TempDB has capacity. section 10.14
