Normally when I tell people about SQL Server’s optimistic concurrency isolation levels, Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI), I have to give them a little speech about how they need to test their queries because the results can change.

Recently, though, I was working with a client who was getting the wrong query results under the default pessimistic isolation level – and we switched to RCSI in order to fix it! I’m not going to explain RCSI or SI here – use the link above for a tutorial on the basics – but rather I’m going to focus on a demo script I wrote up to show the issue they were having, and how RCSI fixed it.

Let’s say we need to track driver rankings in a race to know who’s in first, who’s in second, and so forth. We’ve built a leaderboard table with a row for each driver, showing their position. I’m going to use

To check driver standings for our leaderboard, we run this query:

And the results make sense:

Whenever one driver passes another, we need to make two changes: we need to move the faster driver UP one position, and the slower driver DOWN one position. We don’t have a concept of ties in our race. So our update transaction looks like this:

(Yes, you could do this with plus-one-minus-one, and with parameter-driven driver names, but I’m keeping this simple.)

Our users report that sometimes during a race, they see something they shouldn’t: a tie.

The business users say, “Wait a second: we’re doing everything in a transaction. It’s all gotta roll forward together, or roll backwards together, right? We’re not using NOLOCK, so how are we seeing inaccurate results?!?”

To see the problem:

1. Start the transaction and run the first update statement, setting Lewis to 3rd place.
2. In ANOTHER window/session, run the leaderboard select. It will appear to be blocked.
3. Go back to the transaction window/session and run the second update statement and the commit, setting Charles to 4th place.
4. The leaderboard select window will show both Charles and Lewis in 3rd place.

When our table is first populated, our clustered index looks like this, sorted by Id:

After our first update query runs, the table changes to this:

Here’s the tricky part: row Id #4 is now locked, because we’ve updated Lewis to 3rd place. It doesn’t move where his row is in the table – his row is still last – but right now, it’s locked.

Next, if the select runs in our default pessimistic (Read Committed) isolation level:

SQL Server starts reading from the beginning of the table. (We don’t have an index – that makes the demo even more complicated, and I wanna keep this simple for starters – you can explore how indexes and different key orders affect this later on your own if you’d like. I can lead you to water but I can’t make you a margarita. Go play with indexes, indexed views, columnstore, In-Memory OLTP, and MongoDB on your own time. I’m trying to get this blog post done in under 1,000 words, in an airport terminal. Yes, the client had indexes, and that made the situation even more fun to diagnose. No, I’m not being sarcastic about “fun”, and yes, I have a twisted definition of fun. Clients get nervous when I jump and clap for joy at their problems.)

It reads from the beginning of the table, and reads rows 1, 2, and 3 because those rows aren’t locked yet. That means its reads INCLUDE the currently UNLOCKED Charles, and the select sees him in third place. However, the query now pauses at row 4, unable to read it yet, blocked, because we’ve updated row 4.

The second query in our update transaction now needs a lock on row 3, Charles, and it can get it. The select doesn’t hold out a lock on that row – it’s already done reading it, and the select has already seen Charles at 3rd place. The update locks Id 3 (Charles), sets his place to 4th, commits the transaction, and releases its lock on all rows – allowing our select to finally read row 4, Lewis – but he’s now in 3rd place!

Read Committed Snapshot Isolation turns on a version store that allows read queries to see a previous version of locked rows, before changes are made. This changes what happens when we step through the process:

1. Start the transaction and run the first update statement, setting Lewis to 3rd place.
2. In ANOTHER window/session, run the leaderboard select. It will not be blocked this time, and will be able to read the previous version of Lewis’s row, showing him in 4th place. The leaderboard query finishes, showing a result that is technically true because the update hasn’t committed yet.

You could argue, “Brent, that’s not the right leaderboard, because Lewis just passed Charles!” I hear you, but in a fast-and-furious situation like this, I’d rather show a set of leaderboard results that were technically true at some moment in time, rather than showing an impossible set of results that were never true in any moment of time.

If you were really gonna do it right, you’d lock both rows in a single update statement:

That way you lock both rows in a single statement, no separate transaction is required, and read queries will see the right results regardless of what isolation level we use. (Unless you’re using NOLOCK, aka YOLO results mode, but that’s for another post.)