I saw an article recently about implicit transactions and coincidentally, I had a friend get caught by this. A quick post to show the impact of this setting.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
The Scenario
You run this code:
Everything looks good. I ran an insert and I see the data in the table. I’m busy, so I click “close” on the tab and see this.
I’ve gotten so used to these messages, and annoyed by them in SSMS, I click “No” to get rid of it and close the window.
The Problem
A short while later I open a query window to do something related and check my data. I don’t see it.
What happened? I had implicit transactions set. This might happen if you mis-click this dialog. Ths option is close to the ANSI_NULL_DFLT_ON option.
You could also, or someone could in your terminal (as a poor joke) run this:
SET IMPLICIT_TRANSACTIONS ON
In either case, this means that instead of that insert running as expected, it really behaves like this:
BEGIN TRANSACTION
INSERT dbo.CityName
(
CityName
)
VALUES
(‘Parker’)
If I don’t explicit commit (or click “Yes”) then this isn’t committed.
Be wary of implicit transactions. It’s a setting that goes against the way many of us work and can cause lots of unexpected problems. This is a code smell I would never want in my codebase.
SQL New Blogger
When I ran into this twice in a week, I decided to spend 10 minutes writing this post. It’s a chance to explain something and give a recommendation. Something every employer wants.
Voice of the DBA 
Implicit transactions is something nobody knows about, until they HAVE to. It is extremely dangerous. It was created so that sql server could behave like Oracle, if that’s what you want. I HATE THIS.
In Oracle, if you run
UPDATE tbl SET ….
…it will NEVER be committed until you run
COMMIT;
There is no BEGIN TRAN statement in Oracle like there is in SQL Server. So, in Oracle a transaction is always IMPLICIT, and the COMMIT is explicit. This is likely the biggest stumbling block for tsql developers learning pl/sql ….you gotta remember the “COMMIT;” Always. No exceptions.
The problem becomes when you use JDBC or Spring or many other drivers/ORMs. Since they are written to support many RDBMSs you have to read the fine print as to how implicit transactions work. JDBC and Spring are Java based and thus semantically work closer to how an oracle developer would think.
And therein is the danger. In the .net/ODBC world if I run a command like UPDATE tbl SET… it’s a safe assumption to say that when the command executes the transaction is committed. Not so in JDBC land, which requires the transaction to be committed from JDBC/Spring/Java. That results in an extra db call. Whenever I have an extra db call to commit the work I risk blocking (if using read committed isolation) or minimally what I call “orphaned transactions” (transactions that are started but can NEVER be committed) if the app/middleware dies unexpectedly.
Common sense dictates a simple transaction should never span multiple db calls if it’s not necessary, yet that’s exactly what the DEFAULTS do in things like JDBC/Spring. This leads to all kinds of Heisenbugs. Very few sql server pros know this.
the fact that microsoft created this decades ago (maybe it was sybase) to make the product work more like oracle (if that’s what you really want) is infuriating. While it accomplishes that task, I’ll bet if you ask 100 tsql experts exactly what IMPLICIT_TRAN ON does, very few can describe it. It’s esoteric. It leads to subtle bugs. And the biggest problem is it’s REALLY hard to see if the setting is ON or OFF from a given connection/jdbc/pool/etc.
And let’s face it, there’s enough weirdness with transaction handling in sql server, do we really need more of it?
LikeLike
Yep, another poor architectural decision for sales. At least it’s not the default and you have to change something to shoot yourself in the foot.
LikeLike
The spring/java thing seems very hokey to me. I know a lot of people use it, but seems like causes no shortage of problems, though I suppose that’s an ORM thing. People want code quick and it helps you get going, but as you move from greenfield to brownfield, it isn’t always helpful. And some of the problems, like transactions, data types, etc., become performance issues.
LikeLike
It’s not the default for SQL Server, but it IS the default for JDBC/Spring/others. I worked on a big healthcare app that leveraged Spring years ago, pre-RCSI. We released some buggy code and all of a sudden the db server would just “lock up”. Blocking everywhere. The only solution was to kill spids. We found the buggy code and it was doing a simple UPDATE statement. Why would that cause blocking? Why wasn’t it getting committed?
It took weeks of looking at profiler and DMVs. The former told us we had IMPLICIT_TRAN ON (b/c we could see the extra round trip, in rapid succession after the UPDATE call) and we eventually found the DMV (iirc dm_exec_connections) that showed this too. But why was the app _occassionally_ not sending the second call to COMMIT?
We found it eventually. The java app was experiencing a deadlock and would die BETWEEN the two db calls (not a db deadlock, a deadlock on java resources). The app developer says to me, “It’s working as we expect. App deadlocks happen, the app simply restarts, the JVM gets cleaned, and we continue.” Me: “No, dumbass, it does NOT work that way. SQL Server is waiting for that connection to send a COMMIT or ROLLBACK. I want IMPLICIT_TRAN OFF now or fix your damn code.” His response, “Or you could just turn on RCSI Dave”. Me: “No, we still have a dangling transaction, your code is still broken, and eventually SQL Server will die b/c of this.” Him: “Maybe we should use Oracle then.” Me: “Oracle works the same way…at some point, your snapshot isolation will run out of memory. Fix up your code or fix up your resume. Your decision.”
LikeLike