Database Reference
In-Depth Information
When MARS is enabled, you can have multiple interleaved batches executing at the same time, so all
the changes made to the execution environment are scoped to the specific batch until the execution of
the 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 batches running at the same time.
MARS allows executing multiple interleaved batches of commands. However, MARS does not let
you have multiple transactions on the same connection; it allows only having Multiple Active Result
Sets.
Distributed Transactions in SQL Server 2012
In contrast to local transactions, which are restricted to a single resource or database,
distributed
transactions
span two or more servers, which are known as
resource managers.
Transaction
management needs to be coordinated among the resource managers via a server component known as a
transaction manager
or
transaction coordinator.
SQL Server can operate as a resource manager for
distributed transactions coordinated by transaction managers such as the Microsoft Distributed
Transaction Coordinator (MS DTC).
A transaction with a single SQL Server that spans two or more databases is actually a distributed
transaction. SQL Server, however, manages the distributed transaction internally.
At the application level, a distributed transaction is managed in much the same way as a local
transaction. At the end of the transaction, the application requests the transaction to be either
committed or rolled back. A distributed commit must be managed differently by the transaction
manager to minimize the risk that a network failure might lead you to a situation when one of the
resource managers is committing instead of rolling back the transactions because of failure caused by
various reasons. This critical situation can be handled by managing the commit process in two phases,
also known as
two-phase commit:
•
Prepare phase
: When the transaction manager receives a commit request, it sends
a prepare command to all of the resource managers involved in the transaction.
Each resource manager then does everything required to make the transaction
durable, and all buffers holding any of the log images for other transactions are
flushed to disk. As each resource manager completes the prepare phase, it returns
success or failure of the prepare phase to the transaction manager.
•
Commit phase
: If the transaction manager receives successful prepares from all of
the resource managers, it sends a
COMMIT
command to each resource manager. If
all of the resource managers report a successful commit, the transaction manager
sends notification of success to the application. If any resource manager reports a
failure to prepare, the transaction manager sends a
ROLLBACK
statement to each
resource manager and indicates the failure of the commit to the application.
Guidelines to Code Efficient Transactions
I recommend you use the following guidelines while coding transactions to make them as efficient as
possible:
