SQL Server 2012 : Local Transaction Support in SQL Server (part 1) - Explicit Transaction Mode

12/22/2013 1:18:39 AM

SQL Server, like any industrial-strength database engine, provides built-in support that enables you to wrap one or more queries inside a transaction. Local transactions (those that deal with only one physical database) operate in one of four transaction modes:

  • Autocommit

  • Explicit

  • Implicit

  • Batch-scoped

1. Autocommit Transaction Mode

The autocommit transaction mode is the default transaction mode. Under this mode, SQL Server ensures data sanctity across the lifetime of the query execution, regardless of whether you requested a transaction. For example, if you execute a SELECT query, the data will not change over the execution lifetime of the query. Likewise, if you execute a data manipulation language (DML) query (UPDATE, INSERT, or DELETE), the changes will automatically be committed (if no errors occur) or rolled back (undone) otherwise. The execution of a single DML query will never result in a partial modification of records. The two notable exceptions to this rule are recursive common table expressions (CTEs), for which all of the returned data is not locked in advance, and situations where you explicitly request no transactional sanctity.

2. Explicit Transaction Mode

The autocommit transaction mode enables you to run single queries in a transactional manner, but frequently you’ll want a batch of queries to operate within a single transaction. In that scenario, you use explicit transactions. Under the explicit transaction mode, you explicitly request the boundaries of a transaction. In other words, you specify precisely when the transaction begins and when it ends. SQL Server continues to work under the autocommit transaction mode until you request an exception to the rule, so if you want to execute a number of Transact-SQL (T-SQL) statements as a single batch, use the explicit transaction mode instead.

You specify when the transaction starts by using the BEGIN TRANSACTION statement. After you call BEGIN TRANSACTION on a database connection, the Database Engine attempts to enlist all ensuing operations within the same transaction. The BEGIN TRANSACTION statement uses the following syntax:

[ { transaction_name | @tran_name_variable }
[ WITH MARK [ 'description' ] ]

In this statement, you can specify a name for the transaction by using transaction_name or @tran_name_variable. You can also mark a transaction in the transaction log by specifying a description. This is useful if you want to restore the database to a named mark.

Let’s say that you call BEGIN TRANSACTION and then begin executing a number of DML operations. When you finish, you will want to end your transaction by saving (committing) your changes or undoing them (rolling back) in the event of an error. If you want to make the changes permanent, you execute a COMMIT TRANSACTION statement that uses the following syntax:

COMMIT { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ]

Here’s an example that wraps two DML statements (an UPDATE and an INSERT) inside a single explicit transaction:

UPDATE Table1 SET Column1 = 'One'
INSERT INTO Table2 (Column2) VALUES ('Two')

SQL Server maintains the transaction count, which returns the number of active transactions for the current connection. You can obtain the current transaction count by using the @@TRANCOUNT function. Every time you call BEGIN TRANSACTION, the @@TRANCOUNT value is incremented by 1. You can also have one BEGIN TRANSACTION statement execute after another, which also increases @@TRANCOUNT. Similarly, every time you call COMMIT TRANSACTION, SQL Server decrements @@TRANCOUNT by 1. Until @@TRANCOUNT drops back down to zero, the transaction remains active. When you call BEGIN TRANSACTION within a transaction block, you effectively create a nested transaction. But it isn’t quite as simple as that. Before you can understand the nature of nested transactions in SQL Server, you must also consider the scenario in which you want the changes to be undone (not saved permanently) when an error occurs within the transaction.

If you do not want the changes to be permanent and instead want to restore the database to its previous state, you can roll back the changes with the ROLLBACK TRANSACTION T-SQL statement, which uses the following syntax:

[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ]

ROLLBACK is the opposite of COMMIT. Instead of saving, it undoes all changes made in the transaction. It is important to realize that SQL Server never assumes COMMIT. If you disconnect from SQL Server without explicitly issuing a COMMIT, SQL Server assumes a ROLLBACK. However, as a best practice, you should never leave that decision to SQL Server. You should explicitly tell SQL Server which of the two options you want. The reason for this is connection pooling, a feature that improves performance by maintaining a pool of always available connections to any common data access application programming interface (API). Even if you close a connection, which would automatically cause the rollback if you didn’t commit the transaction, it might take a while before an API such as Microsoft ADO.NET physically closes the connection. SQL Server might have to keep the transaction running and hence block valuable resources for longer than expected.

Another important difference between COMMIT and ROLLBACK is that if a severe error occurs during the execution of a transaction, SQL Server rolls back the transaction. Unfortunately, SQL Server doesn’t make clear its definition of a severe error. An error with a severity level of 11 or higher stops the execution of the current batch and rolls back the transaction. Errors with a severity level of 19 or greater go as far as to terminate the connection. Which one is more severe? In both cases, batch execution stops in some indeterminate state. Because of this ambiguity, it is a good idea to explicitly call ROLLBACK if an error occurs.


Always call ROLLBACK explicitly, and never rely on the API or SQL Server to issue a rollback for you.

There is another important difference between COMMIT and ROLLBACK. COMMIT TRANSACTION decrements @@TRANCOUNT by 1, but ROLLBACK TRANSACTION always reduces @@TRANCOUNT to 0. What does this mean in terms of nested transactions? We’ll explore this topic next.

Nested Transactions

What does all this talk of @@TRANCOUNT and nested transactions mean in practical terms? Let’s look at the following code snippet:


When you run this code (assuming that the TEST table exists), no rows are inserted into the database, even though the inner transaction is committed. This is because the ROLLBACK statement automatically rolls back the entire transaction to the outermost BEGIN TRANSACTION statement, reducing @@TRANCOUNT to 0. So the ROLLBACK overrides the COMMIT, even though COMMIT was called before ROLLBACK. This is a subtle but important transactional processing behavior that is specific to SQL Server.


Another important concept related to transactions is savepoints. Savepoints allow you to temporarily store portions of the transaction, allowing parts of the transaction to be rolled back instead of the entire transaction. They are defined using the SAVE TRANSACTION statement, which uses the following syntax:

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }

By invoking SAVE TRANSACTION during a transaction, you mark a point within the transaction that you can roll back to without losing everything. Consider the following code:


This code inserts a row and sets a savepoint with the name SAVEPOINT1. It then performs another insert, but because you set a savepoint prior to this insert, you can roll back to the savepoint without losing your first insert. As you might have guessed, at the end of the code block, only one row is inserted with TestColumn = 1.

To get a good feel for how these features of transactions work, experiment with various combinations of BEGIN TRANSACTION, SAVE, COMMIT, ROLLBACK, and @@TRANCOUNT on a test table in your database.
Top 10
Extending LINQ to Objects : Writing a Single Element Operator (part 2) - Building the RandomElement Operator
Extending LINQ to Objects : Writing a Single Element Operator (part 1) - Building Our Own Last Operator
3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
3 Tips for Maintaining Your Cell Phone Battery (part 1) - Charge Smart
OPEL MERIVA : Making a grand entrance
FORD MONDEO 2.0 ECOBOOST : Modern Mondeo
BMW 650i COUPE : Sexy retooling of BMW's 6-series
BMW 120d; M135i - Finely tuned
PHP Tutorials : Storing Images in MySQL with PHP (part 2) - Creating the HTML, Inserting the Image into MySQL
PHP Tutorials : Storing Images in MySQL with PHP (part 1) - Why store binary files in MySQL using PHP?
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular Tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS