A Little About Why IMPLICIT TRANSACTIONS Are Horrible In SQL Server
Video Summary
In this video, I delve into why implicit transactions are considered terrible, horrible, no good, very bad things in SQL Server management and development. Exploring the default settings of Microsoft’s JDBC driver and Python driver for SQL Server, which enable auto-commit by default, I highlight how this can lead to significant issues. The video examines the implications of using read committed isolation level as the default setting in SQL Server databases, except Azure SQL DB, where it’s a rare positive exception. Through practical examples, I demonstrate how implicit transactions can exacerbate blocking and deadlocking problems, making debugging and performance tuning much more challenging. By emphasizing the importance of switching to read committed snapshot isolation or using optimistic isolation levels, I aim to help viewers avoid common pitfalls and improve their SQL Server environments.
Full Transcript
Erik Darling here once again with Darling Data. And in this video, we’re going to talk about why implicit transactions are terrible, horrible, no good, very bad things. Now, part of why they are no good, horrible, very bad things is because they are the default auto commit option for the Microsoft JDBC driver and the Python driver for SQL Server. Whenever I talk to a client who is using either one of those, I know that I’m in for a long day of explaining the auto commit feature, the auto commit, I don’t know, connection string, element, whatever you call it. I’m not a programmer. I don’t know the names of these things. Flag. I don’t know. I don’t care that much. Call it whatever you want. But part of why that is a poorly chosen default is because of Microsoft’s poorly chosen default of read committed, the garbage isolation level, is the default isolation level for SQL Server databases, except Azure SQL DB, which is the one good thing I’ll say about Azure SQL DB. The rest of it is a waste of time and money.
Yeah. So because Microsoft made a big, big oopsie daisy back in like 2005, we have lived with the consequences and the consequences of billions and billions of NOLOCK ends ever since. So happy birthday, NOLOCK. So what I’m going to do is in this window, I am going to step through this script one thing at a time, and then we’re going to go look at a different window. I’m going to set implicit transactions on. We are going to run this update, and then we are going to run this select, and all that’s going to go fine.
And then in this window, we are going to go fine. And then in this window, we are going to set implicit transactions on. We are going to run this select, and we are going to wait for all eternity. Now, more recent versions of SP who is active, and I didn’t actually mean to close that, have added details about implicit transactions to the output. If you use the at get transaction info parameter, and set that to one. There we go. So you set that to one, and you will get a column a little bit further over in the results, called implicit tram. And we will see that implicit transactions are open for both of these queries.
And if we come back a little bit, we will have what appears to be a select query against the users table. I’m going to try to get my head right between these two. We have a select query against the users table, block what looks like blocking a select query against the post table. But that’s because we made a connection, we ran one query, and then we ran another query.
And because of the implicit transactions, SQL Server ain’t telling us the whole story. Now, the real crappy thing is, I mean, the deadlock XML and the block process report can already be misleading enough on their own, like capturing whatever query is in the buffer at the time that a deadlock or the blocking, blocking block process monitor loop goes through and find some blocking.
But this makes it worse because whatever query in the batch that was part of the implicit transaction was open, will show up in the block process report deadlock XML. And if you’re using a tool that, you know, like SP who is active that shows you what’s currently running, this will look very, very strange to you.
So implicit transactions cause a lot of additional blocking problems, at least, you know, from everything that I’ve ever seen with people using them. And they also make, well, actually blocking and deadlocking problems.
Part of that is because Microsoft made a bad choice with the default isolation level for SQL Server. And part of that is because implicit transactions are just a terrible idea. So if you are using the Microsoft JDBC driver, if you are using the Python driver to connect to SQL Server, I would strongly advise you to use read committed snapshot isolation as your isolation level, because you will avoid a lot of the really crappy blocking, deadlocking, and very confusing reports for analyzing locking and blocking.
So if I had to rate things, I would say user and optimistic isolation level, flat out, period, do it. Don’t use implicit transactions, flat out, period, don’t do it. If you’re going to use implicit transactions, because you’re too lazy to change your code, then you absolutely need to use an optimistic isolation level with SQL Server.
Otherwise, you will be creamed by the locking and blocking that goes on. Or you can do what everyone else does, slather your code in no lock hints, and just hope and pray for the best.
Hope and pray that you return correct data to your clients and nothing is ever wrong, and you never catch a weird update in the middle of anything. Because, Lord knows, that never happens.
Anyway, thank you for watching. I appreciate your time. I hope you enjoyed yourselves. I hope you learned something. I hope that you will choose to like and subscribe to this channel full of SQL Server wit and wisdom. And, again, I don’t know, I’ll see you in the next video.
And, again, thank you for watching. Thank You How Six Years man Shadow
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.