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

DROP DATABASE IF EXISTS [IsolationLevelDemo];
GO
CREATE DATABASE [IsolationLevelDemo]
GO
ALTER DATABASE [IsolationLevelDemo] SET READ_COMMITTED_SNAPSHOT OFF WITH NO_WAIT
GO
USE [IsolationLevelDemo];
GO


DROP TABLE IF EXISTS dbo.F1Standings;
GO
CREATE TABLE dbo.F1Standings
(
    Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    DriverName varchar(50) NOT NULL,
    Position int NOT NULL
);

INSERT dbo.F1Standings (DriverName, Position)
VALUES
('Kimi Antonelli', 1),
('George Russell', 2),
('Charles Leclerc', 3),
('Lewis Hamilton', 4);
GO

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

SELECT DriverName, Position
FROM [IsolationLevelDemo].dbo.F1Standings
ORDER BY Position;

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:

BEGIN TRAN;

UPDATE dbo.F1Standings
SET Position = 3
WHERE DriverName = 'Lewis Hamilton';

UPDATE dbo.F1Standings
SET Position = 4
WHERE DriverName = 'Charles Leclerc';

COMMIT;

(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.

Here’s why SQL Server is showing the “wrong” results by default.

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:

SELECT DriverName, Position
FROM [IsolationLevelDemo].dbo.F1Standings
ORDER BY Position;

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 3.

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!

To fix that, we turned on RCSI.

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:

UPDATE dbo.F1Standings
SET Position = CASE 
    WHEN DriverName = 'Lewis Hamilton' THEN 3
    WHEN DriverName = 'Charles Leclerc' THEN 4
    END
WHERE DriverName IN ('Charles LeClerc', 'Lewis Hamilton');

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.)

I’ve had clients in this exact situation! Here are some of my favorite gotchas from those experiences.

Buying hardware is really hard right now. AI companies have sucked up all of the available cheap hardware, and the remaining stuff is expensive, hard-to-get, and often delayed. You’re probably used to provisioning hardware in minutes – boy, are you gonna be in for a rough time if you need to get your hands on a bigger server, fast.

Calm outage troubleshooting may be a lost art. In the cloud, when there’s a major outage, you can shrug and point your fingers up at a giant vendor with a status page. On-prem, the executives are going to call you into a conference call and expect you to continue clearly communicating while simultaneously solving the problem. That’s a set of skills that a lot of us never had in the first place. The first major on-prem outage is a sad and eye-opening experience.

All it takes to stop the move is one dependency. If, after the migration TO the cloud, just one developer or sysadmin added a call to a cloud service that isn’t available on-prem, like a proprietary service from one cloud provider, you can be in a world of hurt. The entire migration back on-premises may hinge on finding a good self-hostable alternative to that service. It’s especially painful if the cloud vendor provides the service very inexpensively, but it’s very hard to maintain on your own.

Here’s what we covered:

• 00:00 Start
• 00:52 chris: After your time at SQLBits you’d posted about the number of SQL Server consultants who were very opposed to AI. What was the feedback you were getting on why folks are against this technology?
• 03:50 DBAInAction: Hi Brent, what’s your recommended rollback policy for SQL upgrades while maintaining same compatibility? how do you address the data gap that may occur after the go-live and if we need to rollback ? If Leadership requests a formal strategy, how do you structure your response?
• 05:43 Michael J Swart: You linked to a Tested Q&A: The number of times I’ve shown a client a gray spray painted piece And I’ve said this is not for color it’s only for form Then the first thing the client says is yeah I just don’t like the color What questions like that do you hear too often?
• 10:35 Niktrs: Hi Bent. Thank you for the spring training. Because time zone difference, I would appreciate it, if you could leave the video recordings available for one day. Thanks in advance
• 11:30 Hany: Hello Brent, In your website (the Anniversary Sale page) I noticed Google is one of your customers, just wondering, Google doesn`t have a professional database team to fix their issues themselves?
• 14:04 TipsyManager: Hola Brent! You do a good amount of traveling; any local food (and booze!) that you’ve particularly enjoyed? Or particularly hated?
• 16:05 Will: I know you’ve stated in the past that you dont like current microsoft SQL certifications, such as the dp-300. Are there any non-microsoft SQL certifications that are respected by employers and actually help certify your knowledge vs just being a marketing gimmick?
• 17:35 benlinuxguy: Hi Brent. Are you planning to do any Day of Data (former SQL Saturday) at any point in the future, or was last year the last time you did these?
• 20:42 Luca Carraro (DBA – Doing Business As): Hi Brent, have you ever experienced any performance issues related to the proliferation of DB objects, especially Stored Procedures?