Read Uncommitted is a transaction isolation level that avoids shared locks during reads. That can reduce lock contention, but it permits dirty reads: results may include changes that are later rolled back and never committed. section 10.10
Mistake: treating fewer locks as a safe performance fix
In the MarketingArchive scenario, a data steward changes BidPrice for ImpressionID 100 while a report calculates a campaign total. Read Uncommitted allows the report to see the temporary value. If the steward rolls back the transaction, the report contains a result based on data that never existed in the committed database, and rerunning it can produce a different total. section 10.10
Listing 10.21 Simulating a data steward update
BEGIN TRANSACTION #1
UPDATE dbo.ImpressionsArchive
SET BidPrice = 1.812
WHERE ImpressionID = 100 ;
Simulate the uncommitted update that creates the dirty-read condition. 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 under Read Uncommitted to observe how it can read an uncommitted BidPrice. section 10.10
Listing 10.23 Rolling back the data steward’s transaction
ROLLBACK ;
Roll back the update and expose why the earlier report result was not dependable. section 10.10
Use Read Uncommitted only with tables stored in read-only filegroups, where data cannot be written. For updateable tables, diagnose lock contention instead and consider optimistic isolation levels such as Read Committed Snapshot or Snapshot. section 10.10 section 10.14
