SQL Server 2012 : Local Transaction Support in SQL Server (part 2) - Implicit Transaction Mode, Batch-Scoped Transaction Mode

3. Implicit Transaction Mode

When you connect to a database using SQL Server Management Studio (SSMS) or SQL Server Data Tools (SSDT) and execute a DML query, the changes are automatically saved. This occurs because the connection is in autocommit transaction mode by default, as already mentioned. If you don’t want changes committed unless you explicitly ask them to be committed, set the connection to implicit transaction mode. You can set the database connection to implicit transaction mode (or unset it) by calling the SET IMPLICIT_TRANSACTIONS T-SQL statement, as shown here:

SET IMPLICIT_TRANSACTIONS {ON | OFF}

When a connection is set in the implicit transaction mode and the connection is not currently in a transaction, a transaction is automatically started for you when you issue any one of the following statements: ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE, or UPDATE.

Note

The term implicit refers to the fact that a transaction is implicitly started without an explicit BEGIN TRANSACTION statement. Thus, it is always necessary for you to explicitly commit the transaction afterward to save the changes (or roll it back to discard them).

With implicit transaction mode, the transaction that starts implicitly does not get committed or rolled back unless you explicitly request to do so. This means that if you issue an UPDATE statement, SQL Server will maintain a lock on the affected data until you issue a COMMIT or ROLLBACK. If you do not issue a COMMIT or ROLLBACK statement, the transaction is rolled back when the user disconnects.

In practical terms, you should avoid setting a connection to use implicit transaction mode on a highly concurrent database. For example, while administering a database through SSMS or SSDT, you will implicitly start transactions by issuing interactive queries, and might unwittingly end up locking database resources that can incapacitate the entire system. One example of the appropriate use of implicit transaction mode is on a data warehouse where the reports need just read-only access to the data and can be run under isolation levels that avoid blocking (as we’ll discuss shortly) and where you want to be very careful not to inadvertently modify the data.

4. Batch-Scoped Transaction Mode

Since SQL Server 2005, multiple active result sets (MARS) are supported on the same connection. Note that we said multiple active results, not parallel execution of commands. The command execution is still interleaved with strict rules that govern which statements can overstep which other statements.

Connections using MARS have an associated batch execution environment. The batch execution environment contains various components—such as SET options, security context, database context, and execution state variables—that define the environment under which commands execute. When MARS is enabled, you can have multiple interleaved batches executing at the same time, so all changes made to the execution environment are scoped to the specific batch until the execution of that batch is complete. Once the execution of the batch completes, the execution settings are copied to the default environment.

Thus, a connection is said to be using batch-scoped transaction mode if it is running a transaction, has MARS enabled on it, and has multiple interleaved batches running at the same time.

MARS and Transactions

MARS lets you execute multiple interleaved batches of commands. However, MARS does not let you have multiple transactions on the same connection, only multiple active result sets. Transactions and MARS are an interesting mix, but to understand how transactions work in MARS, you must first understand the command interleaving rules.

In MARS, a command that reads results (such as SELECT, FETCH, or READTEXT) can, generally speaking, be interleaved freely or interrupted by a command that attempts to modify data (such as UPDATE or INSERT). Thus, a write operation can block a read operation, but a read operation cannot block a write operation. Read operations ensue once the write operation has finished. Also, if two writes show up together, they are serialized in the order of execution. Remember that command execution in SQL Server is always sequential, never parallel, even in multithreaded environments. Last, BULK INSERT statements block all other read and write operations.

So theoretically, BULK INSERT will block INSERT, UPDATE, and DELETE, which in turn will block all read operations. The problem, however, is that in most practical scenarios, you cannot accurately predict which command actually blocks which other command. This is because your read operation might have finished before the write operation interjected. You also cannot predict exactly when the read operation finished and the write operation started because the read operation depends on a number of factors—CPU speeds, network speeds, packet size, network traffic, and so on. It is thus impossible to predict whether the read data was “put on the wire” before the write operation was requested.

What this means in terms of transactions is that if you are running a transaction that inserts a row, which in turn fires a trigger, and there is a SELECT statement in the trigger, the MARS interleaving rules will dictate that your trigger’s SELECT statement will be blocked by the original INSERT statement. Also, because of different command execution times, this behavior is impossible to predict reliably. If this ever happens, the SQL Server deadlock monitor will detect this condition and fail the SELECT statement. As a result, you might end up with a system that will work on a low-load developer’s machine but fail in production where the load is greater. These are the most difficult types of problems to troubleshoot. Thus, when you use MARS, you must consider interleaving rules and multiple batches in your design.

MARS and Savepoints

Because you have multiple interleaved commands all working in the same transaction, the commands that issue savepoints can easily confuse each other’s logic. Imagine a situation in which two interleaved batches issue a rollback to a named savepoint, and it just happens that the savepoint name is the same in both batches. You cannot predict which rollback occurred first, so which savepoint should SQL Server roll back to? In addition, because you cannot accurately predict which statement ended up interleaving which other statement, you can’t really be sure if the savepoint was ever created before you issued a rollback to it.

For these reasons, if multiple serialized commands are executing, MARS allows you to set a savepoint, but as soon as commands begin to get interleaved, any request to BEGIN TRANSACTION will fail. Because you cannot accurately predict the exact interleaving order of commands, you cannot know for certain whether your BEGIN TRANSACTION statement will succeed or fail. Considering this unpredictable behavior, it is best to stay away from savepoints on a MARS connection.

Transaction Terminology

Before advancing beyond the basics of transactions, let’s review some common terminology.

• Beginning a transaction Specifying that all subsequent operations that occur after a transaction begins are assumed to lie within the transaction.

• Rolling back a transaction Undoing operations that have occurred since a transaction began, thus restoring the affected data to its original state. This is done in the event of failure.

• Committing a transaction Making permanent all operations that have occurred since a transaction began. A transaction is committed in the event of success.

• Dirty read The operation of reading data that is yet to be committed. This occurs, for example, when transaction B is being blocked by transaction A, but because you have tweaked the isolation behavior to permit dirty reads, transaction B ends up reading transaction A’s changes even though they have not been committed.

• Nonrepeatable read A condition where transaction B modifies the data that transaction A was working with, during the lifetime of transaction A. As a result, transaction A reads modified data, and the original read cannot be repeated.

• Phantom read Like a nonrepeatable read, except that the number of rows changes between two reads within the same transaction. The rows that differ between the two reads are referred to as phantom rows.