Implicit transactions are a strange thing in SQL Server.
It’s very important to understand how transactions work in Microsoft SQL Server and to understand the different tools available to us when writing transactions. One such tool is the “IMPLICIT_TRANSACTIONS” mode.
But beware. Enabling this feature can have unforeseen consequences!
In this very brief tutorial, we’ll talk about what Implicit Transaction mode is and why we need to be careful when using it.
If you need to understand how transactions work in the first place, you should start by reading the full beginner-friendly tutorial:
SQL Server Transactions: An Introduction for Beginners
You should also download the following FREE Ebook:
FREE Ebook on SQL Server Transactions!
This FREE Ebook discusses everything you need to know about creating and using transactions within SQL Server. It will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!
Without further ado…
Understanding IMPLICIT_TRANSACTIONS mode:
What does “implicit transaction” mean? It means that any transactional statement will be implicitly put in an open transaction for you. It’s as if an invisible BEGIN TRANSACTION statement was ran before your transactional statement.
You need to enable implicit transaction mode in SQL Server to begin writing implicit transactions. You enable implicit transaction mode by running SET IMPLICIT_TRANSACTIONS ON.
The dangerous thing about implicit transaction mode is that you are responsible for closing the transaction, whether that means committing it or rolling it back. The step of closing the transaction is not done for you automatically.
Let’s go ahead and turn on implicit transaction mode in a SQL connection:
Take a look at the following Books table:
We’ll run an UPDATE statement against this table that does not have a BEGIN TRAN statement or anything else with it:
Great, but here’s the kicker: Since this is a transactional statement, and IMPLICIT_TRANSACTIONS is ON, we are presently in an open transaction. We can use the handy @@TRANCOUNT system function to see how many open transactions there are in our database:
Yep, we’re in an open transaction.
Folks, we are responsible for closing this open transaction. I’ll go ahead and commit the changes, then show you we aren’t in an open transaction anymore:
Then we’ll make sure the data is updated:
This begs the question, “What statements are considered transactional“? It’s the following statements, pulled directly from the Microsoft Documentation:
- ALTER TABLE
- FETCH
- REVOKE
- BEGIN TRANSACTION
- GRANT
- SELECT – Except when not querying a table. For example,
SELECT SYSDATETIME() - CREATE
- INSERT
- TRUNCATE TABLE
- DELETE
- MERGE
- UPDATE
- DROP
- OPEN
It is not recommended that you enable Implicit Transaction mode
If Implicit Transaction mode seems weird and dangerous, that’s because it is. It greatly increases the chance that you will leave an open transaction sitting out there, which is not good.
In fact, before we go any further, let’s disable implicit transaction mode: SET IMPLICIT_TRANSACTIONS OFF
I suppose Implicit Transaction mode has it’s time and place, but Microsoft themselves say it’s not a popular thing to enable.
Autocommit mode is the norm
When Implicit Transaction mode is disabled, SQL Server is put into a more common autocommit mode. In autocommit mode, transactional statements are automatically put into an open transaction and then automatically committed.
It’s as if your transactional statement were wrapped around an invisible BEGIN TRANSACTION…..COMMIT block.
Autocommit mode isn’t perfect either. As I said, it will COMMIT whatever transactional statement you execute, even if that transaction statement did more than you intended it to do. For example, maybe you ran an UPDATE statement without a WHERE clause (yikes!).
The point is, if we want to make our transactional statements bulletproof, we should write our statements in explicit transactions and maybe use some of the handy error handling tools available to us in Microsoft SQL Server, such as @@ROWCOUNT (tells us how many rows were modified in the previous statement) or @@ERROR (tells us if the previous statement encountered any errors).
Check out the list of tutorials I have on error handling to learn more:
www.simplesqltutorials.com/category/error-handling
Next Steps:
Leave a comment if you found this tutorial helpful!
Everything discussed in this tutorial can be found in the following FREE Ebook:
FREE Ebook on SQL Server Transactions!
This FREE Ebook discusses everything you need to know about creating and using transactions within SQL Server. It will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!
Do you understand these 6 rules about transactions? Check out the tutorial to find out!
If you are just starting out and need to know what transactions are in Microsoft SQL Server, take a look at the full beginner-friendly tutorial:
SQL Server Transactions: An Introduction for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
