The Real Value Of Optimistic Locking


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

    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.

Fully Correct

    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.

Big Baby

Remember folks: yeah, blocking sucks, but incorrect data is usually way, way worse.

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *