While a lot of people speak highly of optimistic locking because it cuts down on blocking issues, I think it’s of equal or higher value that dirty reads are prevented.
“Imagine” a situation where someone ran a query they shouldn’t have.
Either forgetting a join, a where, or some other limiting condition.
You know, while they’re continuously integrating something.
Out, damn spot
BEGIN TRAN; UPDATE h SET h.Score = 2147483647 FROM dbo.HighQuestionScores AS h WHERE 1=1;
This will update every row in the table. I know this because once upon a time, I wrote a similar query and learned the importance of backups.
I also learned that tools that throw a warning when modification queries are missing WHERE clauses think this one is just fine.
But now if I go into another window, this query will return wrong results.
SELECT TOP 10 * FROM dbo.HighQuestionScores AS hqs WITH(NOLOCK) ORDER BY hqs.Id DESC;
In fact, any query that runs with a nolock hint will show incorrect values here.
- Boss runs a report? Wrong.
- Trigger fires based on a score change? Wrong.
- User checks their account? Wrong.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; SELECT TOP 10 * FROM dbo.HighQuestionScores AS hqs ORDER BY hqs.Id DESC;
This query uses the snapshot isolation level.
It would do the same thing under read committed snapshot isolation, but without needing to set the isolation level.
But now we don’t get someone’s accident in our query results.
Remember folks: yeah, blocking sucks, but incorrect data is usually way, way worse.
Thanks for reading!