A Little About Locking And Isolation Levels In SQL Server
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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I’ve most recently been hit with a double whammy. A port from Oracle by the vendor normally will not have ID columns. Oracle’s own native isolation level is even more advanced than RCSI but porting it over can’t port over the isolation level. So your databases are set at the default trash isolation level and begets locks against SELECT statements.
PS: I had wondered if you were either Canuckistani or Northern Wisconsinati but was too polite to ask. Enjoy your vacation, dammit.
Ah, no. Purely northeast regional.
That sounds like a fun situation. How far does RCSI get you?
Dammit I dammit, dammit.
Pulls all the blocking out for the SELECTS for one. Of course that just has their trash queries fail at a different point.
But your point that RCSI should be the default for user databases isn’t wrong. The fun is arguing with my own in-house application owners and the vendor’s Oracle DBAs that the SQL Server databases need to be at an equivalent isolation level in either the migration software, their installer(s) or in their documentation.
It comes down to the sad fact that it’s easier to say “no” to understanding than to try to understand. I think the monster in “Frankenstein” had much the same issue.
Gotta go, I think I see some pitchforks on the horizon and they look backlit.
🧌🧌🧌