A dirty read retrieves data changed by another transaction before that change is committed. The result may describe a value that is later rolled back, so the same query can return a different answer when run again. section 10.10
Mistake: treating Read Uncommitted as a performance switch
A data steward updates BidPrice for ImpressionID 100 in MarketingArchive but leaves the transaction open. Meanwhile, a reporting user runs a SUM query under Read Uncommitted and sees the temporary value. When the steward realizes the mistake and rolls back, the report contains data that was never committed. section 10.10
Listing 10.22 Simulating a reporting query
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED #1
BEGIN TRANSACTION
SELECT SUM(BidPrice)
FROM dbo.ImpressionsArchive
WHERE CampaignID = (
SELECT
CampaignID
FROM dbo.ImpressionsArchive
WHERE ImpressionID = 100
) ;
COMMIT
Run the reporting query in a second query window while the update transaction remains uncommitted, then compare its result after rollback. section 10.10
Use these rules when choosing transaction isolation:
- Do not use Read Uncommitted as a general performance optimization on tables that accept updates; reserve it for read-only filegroups. section 10.10
- Treat NOLOCK similarly: avoiding locks can produce nondeterministic results and dirty reads. section 10.10 section 10.14
- Prefer the default Read Committed level in conventional situations because it protects against dirty reads without routinely imposing stronger locking. section 10.11
