Moved from: bobsql.com
I have written on this subject before but it seems to come up from time to time, as it did again this week. The Session (SPID) = –2 is just a place holder used by SQL Server to indicate that the DTC transaction is still active but there are no sessions enlisted/propagated into the transaction.
Here is how to reproduce the scenario.
- Client creates and owns a DTC Transaction
- Client connects to SQL Server, and enlists in the DTC transaction.
- Connection does some work under the DTC transaction umbrella
- Client disconnects (or enlists in NULL)
The current state of the DTC transaction is still active, the client has not committed and retains primary control over the the scope of the DTC transaction.
SQL Server is not allowed to abort or commit the transaction just because the connection(s) have been closed. The client could have enlisted another resource manager (file system, another database server, etc.) and still be doing work. When you query the SQL Server and it has an active DTC transaction (UOW) but no longer has a connection associated with the transaction the session id (SPID) reported is –2.
Now that I have described the scenario steps let’s step through how the DTC transaction flow works.
Client creates and owns a DTC transition
Using one of the provided DTC interfaces the client creates an ITransaction object. Creating an ITransaction entails a connection to a DTC manager and the ITransactionDispenser->BeginTransaction call. In .NET this can be provided by the TransactionScope object.
Client connects to SQL Server and enlists in the DTC transaction
The client establishes the connection to the SQL Server. In ODBC SqlDriverConnect, in .NET SqlConnection.Open or such action.
Now the client enlists/propagates the SQL Server connection into the DTC transaction. There are various ways to accomplish this, in ODBC the SQLSetConnectAttr is used to bind the ITransaction to the connection. This tells the SQL Server client to perform synchronization with the DTC manager(s) and SQL Server.
From the ITransaction the client driver uses the ITransactionExport interface to export a transaction cookie. The cookie is a binary value indicating information about the transaction UOW as well as the DTC manager endpoint, which could be different than the DTC manager being used by the SQL Server instance. The cookie is sent to SQL Server (Trace Event = DTC::Enlist or DTC::Propagate when looking at XEvent information flow to the SQL Server.)
SQL Server can register with the DTC manager as a transaction resource manager. SQL Server takes the cookie provided and leverages the ITransactionImport interface to import an ITransaction object representing the transaction. At this juncture the client has a ITransaction with a reference to the transaction and SQL Server has an ITransaction with a reference to the same transaction. SQL Server stores the ITransaction with a transaction object that you can now see in the various transaction DMV outputs.
Connection does some work
All transaction activity is properly associated with the DTC transaction entity. At this juncture the client and SQL Server still have ITransaction references, the connection remains bound to the DTC transaction and the locks remain held on changed rows.
Client Disconnects
The client can disconnect the connection from SQL Server. During the disconnect (or propagate into a NULL transaction) SQL Server unhooks the connection from the DTC transaction it is tracking. The transaction object still remains in SQL Server’s transaction list because it is still active. The DTC manager has not indicated an abort or commit.
This is the point in time that SQL Server does not have a session bound to the DTC transaction. When you issue a DMV query SQL Server attempts to lookup the session associated with the transaction object. If no session is found and this is a DTC transaction –2 is used to indicate an active DTC transaction without associated connections exists.
The client can do additional work and then issue the ITransaction->Commit or Abort. The commit or abort is handled by the DTC manager, contacting all endpoints for the same ITransaction (UOW) and indicating to them the action to carry out. SQL Server receives the commit or abort from the DTC manager and takes the appropriate steps to complete the request. Once the request is complete SQL Server releases the ITransaction and any internal tracking structures.
You can trace the commit or aborts with additional DTC events such as Prepare, Prepared, Abort, Aborting, etc.
Posted at https://sl.advdat.com/353xYPChttps://sl.advdat.com/353xYPC