Implicit transactions are a hell of a bad idea in SQL Server: they require you to micromanage your transactions, staying on top of every single thing in code. If you miss just one little DELETE/UPDATE/INSERT operation and don’t commit it quickly enough, you can have a blocking firestorm.
The ideal answer is to stop using implicit transactions, and only ask for transactions when you truly need ’em. (Odds are, you don’t really need ’em.)
But if you can’t change the code, you need the delicious goodness of Read Committed Snapshot Isolation. Here’s a video demo showing why:
You can learn more about RCSI, its gotchas, and how to implement it here.
8 Comments. Leave new
Good advice!
It seems to me that when creating a new general purpose database, RCSI should be the default. I believe it is the default in Azure SQL database. The cheat sheet near the top of this page is good:
https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/
I agree, but Microsoft is pretty hesitant to change the defaults. We still have Cost Threshold = 5, MAXDOP = 0 on SQL Server 2017, and even Microsoft has known those numbers to be wrong for a decade or more.
This is interesting – we have a few jobs that use implicit SQL that bring in data from a number of extremely unreliable data sources. We use dynamic SQL because unless every statement in these jobs complete successfully, the data ends up gibberish and its better for use to use the old data than the gibberish data. Every once in a while something will fail in a job which will completely lock up the SQL server and can’t even get hardly any troubleshooting data out of it or even view object definitions. We know its some sort of blocking problem but haven’t been able to figure out what, since when it happens, the server is basically toast and has to be restarted .
Wonder if RCSI would resolve this. Am a little worried about it blowing up the tempDB or creating too much a mess that has to be committed at the end of the job.
I have schema locking to. Because of interesting frontend implementation, a lot of unreasanable calls to backend, and huge usage of „select into” statements into physical tables. Will test this setting. Thanks for idea!
When I said dynamic SQL, I meant implicit SQL*
I’d like to see a new RUCSI setting – this would force all NOLOCK & Read Uncommitted transaction isolation level code to run using snapshot isolation – IMO this would be even more useful than RCSI given the gigantic quantities of DBs which are still coded using NOLOCK like confetti
I agree, so many people do not know the dangers of NOLOCK…
The way Oracle rolls 🙂