The Champ Is Here
For developers who have worked with, or who write applications that work across multiple database platforms, working with SQL Server can often be a jarring experience.
Problems that you don’t face on products that use Multi-Version Concurrency Control by default are all of a sudden the bane of your existence.
But of course, for developers who only work with SQL Server, the innate fear of commitment is one of the first things they learn.
SQL Server has two options for this that I’ve blogged about before. Head over here if you need background.
A lot of the reasoning for not using an optimistic isolation level that I’ve heard from people over the years just doesn’t hold up well anymore.
Excusify
Let’s talk through some of them.
We’re worried about tempdb contention from the additional row versioning going up there
That’s fair, but if you don’t currently have tempdb contention, it’s unlikely that it’s going to body slam you unless your application currently has problems with:
- Modifying huge chunks of data at once (not counting inserts)
- Long running modification transactions
- Implicit transactions that don’t always commit
- Updating a lot of LOB data all the time
Most people don’t have such severe problems with that stuff that things go pear shaped from the version store. When tempdb is properly set up, it can take a pretty good beating before you see the type of contention that causes performance issues.
On top of that, the version store and other temporary objects don’t use exactly the same structures in tempdb.
- In SQL Server 2019 and up where the local version store in use by Accelerated Database Recovery is used instead of tempdb
- In all prior releases, the row versions are sent to special append-only storage units which aren’t governed or exposed by normal system views, nor do they generate transaction log record. Those units may contain many rows, and are cleaned up in at the unit level, not the row level.
- Online index rebuilds use a separate version store from the one used by triggers, updates, deletes, MARS, etc.
We’re worried about tempdb growing out of control from the version store
Sure, you increase the risk surface area for long running modifications/transactions creating this situation, but either of those things would cause a pretty big headache if they happened without an optimistic isolation level enabled.
I’ve seen plenty of people end up there, do something goofy like kill the long running modification without looking at how much work it had done, and then get stuck in an even longer running rollback.
And guess what? Restarting SQL Server doesn’t fix it. Accelerated Database Recovery is helpful for that, if you’re on SQL Server 2019+, but that probably ain’t you.
The “good” news is that if tempdb runs out of space, SQL Server will start shrinking the version store.
We want our product to work out of the box, and SQL Server’s tempdb needs additional configuration to handle load
Boy, have I got news for you from 2016:
- Trace flag 1117 is the default behavior
- Trace flag 1118 is the default behavior
- The setup GUI lets you create multiple data files
- The setup GUI lets you turn on instant file initialization
If that setup doesn’t keep tempdb woes at bay from enabling an optimistic isolation level, it might be your fault.
We can’t test all our code to see if there will be race conditions with optimistic locking
I hate this, because it says to me that you don’t know your own code base well enough to know if you rely on locking to queue things, etc. It’s also a very lazy because…
I see you using gobs of NOLOCK hints everywhere. You’re not gonna get the right results for that type of code, anyway. Why be afraid of working with the last known good version of a row?
If you’re doing something like generating sequences, and you’re using a homegrown sequence table instead of the sequence objects that SQL Server has had since 2012, I question your sanity.
I mean, unless you’re using the code in the post I linked. Then you’re the coolest and smartest.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.