By default, when you run a query in SQL Server, your delete/update/insert (DUI) is finished as soon as your query finishes. That’s because the default behavior of SQL Server is IMPLICIT_TRANSACTIONS OFF – meaning, SQL Server doesn’t hold your queries open waiting for a COMMIT or ROLLBACK.
As an example, I’ll connect to the StackOverflow database and give myself some reputation points:
|
1 2 3 |
UPDATE dbo.Users SET Reputation = 1000000000 WHERE Id = 26837; |
Take that, Jon Skeet. Now, in another window, I’ll use sp_WhoIsActive to see what locks are being held:
|
1 |
sp_WhoIsActive @get_locks = 1; |
No rows come back – because no sessions are open that are currently holding locks.
Now, let’s try that with IMPLICIT_TRANSACTIONS ON.
Change that horrific setting, and run the update:
|
1 2 3 4 5 6 |
SET IMPLICIT_TRANSACTIONS ON; GO UPDATE dbo.Users SET Reputation = 1000000000 WHERE Id = 26837; GO |
It still finishes instantly, and as far as that user is concerned, the behavior was no different. But now check sp_WhoIsActive again in another window:
And whaddya know, now a row shows up. Our UPDATE – even though it finished – is still hanging out. If you scroll across to the right, you’ll see open_tran_count = 1.
SQL Server started a transaction for you without you asking for one.
Click on the locks column in sp_WhoIsActive to see the locks being held by your session:
You now have an exclusive lock on that row, and the lock isn’t going away until you either roll back or commit your transaction.
How to tell if you’re having this problem
The first symptom is severe blocking issues, but at first glance, it just looks like people are doing their normal query workloads. The blocking eventually clears up on its own when the app randomly issues a COMMIT, typically for unrelated reasons.
But it’s hard to troubleshoot because the IMPLICIT_TRANSACTIONS option doesn’t show up in sys.dm_exec_plan_attributes, sys.query_context_settings, or sp_WhoIsActive’s @get_additional_info = 1. Erik filed a Microsoft request to fix that, and you can upvote it here.
For now, to catch it, your options are:
- Run sp_BlitzFirst, our free live performance check script. It warns when there’s a long-running query blocking others, plus warns when live queries are using implicit transactions.
- Run sp_Blitz, our free SQL Server health check script. It also warns when there are live queries using implicit transactions, so you can catch it during a regular health check as well.
- Run sp_BlitzWho, our live activity checker, and look at the is_implicit_transaction column.
To track the queries over time, you can also run Profiler or use the existing_connection XE session.
Tara Says: It gets worse! If you run a SELECT, you get a transaction. Don’t forget to commit after your SELECTs.
Erik Says: In Brent’s screencap for the uncommitted transaction, it’s been running for nearly a minute. I wonder what he was doing for the minute between running the query and taking the screenshot.
35 Comments. Leave new
DUI ahahahaha
The OLEDB Provider sends
SET IMPLICIT_TRANSACTIONS ONon Connection.BeginTran so that actual transaction is delayed as much as possible.I thought that a SELECT gets you an implicit transaction only on a snapshot isolated database.
It gets even more fun when you use read committed snapshot isolation with IMPLICIT_TRANSACTIONS ON and the app doesn’t always commit the transactions and then you find yourself with 500GB version store. Good times.
But Brent, this is the default behavior in Oracle clients, like Sql Developer! It also gives you a second chance if you run deletes without where clauses. This is a key barrier between us and catastrophic disaster. It doesn’t matter how many times users run queries against the main application logging table, locking it and spilling versioning to tempdb until the drive is full and the whole server crashes or becomes unresponsive. Its just proof of Sql Server’s inferiority and proof that we should return to Oracle immediately.
I wish I hadn’t heard that before…
Back in the dark ages when I taught SQL 6.5 classes, I told students this was “Oracle Mode.”
Sweating SQL Server locking is like fiddling with manual garbage collection, IMO. By contrast, Oracle handles locks automatically with neither limits nor lock escalation. I even lost a job opportunity due to this! I was interviewing at a company that provides point-of-sale (POS) software, with the customer getting to choose which DBMS as the back-end. The manager of the database team bragged to me during the interview about how they meticulously planned out their “locking strategies” for SQL Server and DB2, and were looking at adding Oracle as an option for their POS. The manager turned to me: what locking strategy do you use in Oracle? I replied: I’ve worked with Oracle for almost twenty years and can’t remember a single example of when I had to worry about locking. Never had to add a NOLOCK hint to an Oracle query; nothing. She quickly shooed me out the door.
Duke – I know, right? I can’t understand why everybody doesn’t use Oracle for everything.
Oh that’s right, I remember now. $47,000 per core for Enterprise.
SQL Server is $7,000 per core for Enterprise.
For $40,000 extra per core, Oracle ought to be self-managing and come with a wine tap.
Brent– I certainly can’t dispute the existence of a cost differential — although I never see it directly since I’m but an individual contributor. I figure any company that can afford a top-end DBMS and a fleet of DBAs can afford me too. I’ll buy my own wine. 🙂
–Duke
Love it!
Huh! I think that’s a lack of knowledge on that manager’s part though …
“Its just proof of Sql Server’s inferiority and proof that we should return to Oracle immediately”
Come on Brendan, proofing is a bit more than arguing about different default settings. SQL Server lets you decide what setting to choose. Every DBA should be aware of that and choose the setting that is best for him.
And for even more fun, if IMPLICIT_TRANSACTIONS is ON and the connection is closed after some data modification, SQL Server automatically rolls back the changes. You didn’t really want that, did you?
ABSOLUTELY!!! YES YOU DID. Unless the process completes the entire atomic operation, you ABSOLUTELY want your transaction rolled back if the connection is lost mid update.
Remark: The current version of the SSMS Boost Addin has (sadly only in the pro version) a feature called Transaction guard, which shows you a warning (a “Post-it” on the screen) when you have an open transaction (not committed / rolled back). This is helpful, when you use implizit or explicit transactions (and forget the COMMIT / ROLLBACK)
Wow, that’s awesome! I love the post-it note idea. Brilliant!
Yep. Been using SSMSBoost for a couple of months now. We have both RedGate Toolbelt Essentials and SSMSBoost. They complement each other. GREAT tools!
Meh! I’m a polyglot DBA, so I’m used to both sides of the transaction argument. When you’ve had to deal with enough users forgetting to write a COMMIT even after they’ve written an explicit BEGIN TRAN, you can begin to see the attraction of training them from Day 1 to always have to explicitly commit. Equally, I’ve seen too many issues in the implicit world. For me the biggest bugbear is not being able to easily see/be warned whether IMPLICIT_TRANSACTIONS is set, or having apps that set it sneakily in the background. It’d be nice to have SSMS have a scrolling news bar stuff with “You have # OPEN transactions on XYZ_DB” to help people remember what they’ve left lying around
have this in my own activity proc;
…
LEFT OUTER JOIN sys.dm_tran_session_transactions st on s.session_id = st.session_id
LEFT OUTER JOIN sys.dm_tran_active_transactions at on st.transaction_id = at.transaction_id
and at.[name] will show you if it’s implicit or not.
I easily catch these all the time like this.
Dan – that’s pretty cool! Thanks for pointing that out.
I definitely want to add that as a check somewhere in the FRK.
Are you on GitHub? Would like to give credit.
Not a problem, not on GitHub, nor publish anything, just continually tweaking my own code to catch stuff like this. I don’t need credit for something like this but thanks for asking!
Dan… is there a way to obtain a copy of your activity proc? Thanks in advance
How about using COMMIT?
What is the difference between a crow and a raven? Well, a raven has one extra feather in its tail called a “pinion” feather which helps it turn while in flight. So one could rightly say the difference between a crow and a raven is a matter of a pinion.
In my “a-pinion”, so is using implicit transactions. Otherwise by definition Oracle (or any other RDBMS that uses implicit transactions by default) is a bad idea.
Bottom line is if you use implicit transactions you MUST remember to roll back or commit at the end IMMEDIATELY or you will cause blocking. If you don’t use implicit transactions you MUST remember to use “begin transaction” if you want the ability to roll back.
As Pinal Dave wrote @ https://blog.sqlauthority.com/2014/11/29/sql-server-customization-of-new-query-in-sql-server-management-studio/
Edit the SQLFile.sql he pointed out and type in
BEGIN TRAN
ROLLBACK
Then you adhere to “First, do no harm…” (Unless you Ctrl+A and Delete, then you’re on your own!
(You can do this while SSMS is running, v17.5 at least allows it, provided you have Local Admin rights, and SSMS immediately picks it up for your next Ctrl+N.)
If only applications did that, heh.
Can’t say I understand this line: “The blocking eventually clears up on its own when the app randomly issues a COMMIT, typically for unrelated reasons.”
We have a huge app that Issues tons of via sp_prepare then execsql runs but for whatever reason we often see orphaned transactions with sp_unprep, and inputbuffer on those all show “set implicit on” .
What sort of “unrelated random commit” would fix those orphans? We always just kill them.
When the app commits something else completely unrelated, like runs another transaction.
Can you even think of a good reason to do this? I’m struggling to find one lol
[…] in which sessions that are yet to be committed end up locking resources and impacting performance. Brent Ozar gives more details on the performance implications of IMPLICIT TRANSACTIONS = […]
Brent:-
As always nice one.
In some cases, the session will be a sleeping session.
Interesting enough, Adam Machanic’s sp_whoIsActive SP includes sleeping sessions.
The parameter definition reads:-
–Controls how sleeping SPIDs are handled, based on the idea of levels of interest
–0 does not pull any sleeping SPIDs
–1 pulls only those sleeping SPIDs that also have an open transaction
–2 pulls all sleeping SPIDs
@show_sleeping_spids TINYINT = 1,
Keeping in mind, Option 1 is default.
On the other hand, dbo.sp_BlitzWho does not “naturally” include the session.
To have the session included one will have to pass along the @ShowSleepingSPIDs with a value of 1. ( @ShowSleepingSPIDs =1 ).
Thanks, glad you liked it!
From my experience, this is the main difference between Oracle and SQL Server implementations. Most Oracle databases are installed with defaults requiring a commit or rollback after each transaction – although there is nothing to commit with a select. With SQL Server, “auto commit” looks like the more popular option. With IMPLICIT_TRANSACTION off, you cannot rollback if there are issue with updates or inserts. With IMPLICIT_TRANSACTION on, you must remember to commit. I work in both environments, and I don’t see how one is better than the other.
“you must remember to commit”
You can’t see the drawback with that?
frankly all this debating merits/demerits is pointless. As operational DBAs we don’t get the choice, the bean counters decide if it is on-prem or not. Oracle/MySQL/PostGres/SQL Server is a technical matter decided at the sales pitch. Our job is the band-aid for the higher ups when they get that penny pinching decision wrong… Expecting anyone to care about this level of minutiae….is in my view just depressing and pointless.
Yes its a jumping of your named bridge…matter 🙂
This article isn’t really for operational DBAs – it’s for developers. Thanks for stopping by though!