Multiple Active Result Sets (MARS) is a new SQL Server 2005 feature that allows the user to run more than one SQL batch on an open connection at the same time. In my previous article about MARS I explained what MARS is and how to use it. In this article I'll discuss how transactions in MARS work and how you can debug MARS connections.
Before MARS transactions were a pretty straight forward thing. You executed a command which was associated with a transaction and that was it. This is not so simple anymore.
A good example are Transaction Savepoints. Transaction savepoints are points in a transaction to which you can partially rollback to.
Under MARS, setting savepoints, rolling back to savepoints and committing transactions isn't allowed when there is more than one request which is actively running under a transaction. Let's see why with some code. Note that both requests are running under the same transaction.
// Time T4 - this will fail.
// are pending requests working on this transaction.
At first glance this code looks OK. But let's examine it closely. Everything is great until the rollback to savepoint1 in the second request. What happens here is 3 statements execute since setting the savepoint. First the update to the table in request 2, then insert into the table in request 1 and finally the update to the table in request 2. But since the second update fails and rolls back to the savepoint, the insert in request 1 will also be rolled back which is unwanted behaviour.
These kinds of problems are hard to find and debug and are the reason why savepoints and committing aren't allowed under MARS when more than one request is run under a transaction.
// implicit transaction in which the update runs by default.
I've shown four ways how a developer might try to use transactions with MARS. However only the one in which all SqlCommands are under one transaction is the correct one as long as you're not setting any transaction savepoints. To truly understand MARS execution a developer must have a good understanding of its possibilities.
With MARS the "old-school" type of monitoring isn't adequate anymore. Why? Because in SQL Server 2000 we could simply say or at least assume that the SPID (SQL Server Process ID) identifies a request (a batch). This way you can simply get the executing SQL Statement for the SPID of your choice. SysProcesses helped with debugging more than once with it's SPIDs and accompanying execution statistics.
All of this has been changed. Of course sysprocesses still shows process information, but with the introduction of Dynamic Management Views it was "replaced" with a few of those. These new DMV's are sys.dm_exec_sessions, sys.dm_exec_connections and sys.dm_exec_requests.
Returns one row per authenticated session on Microsoft SQL Server. A SPID is equal to session_id column. Interesting columns are last_request_start_time and last_request_end_time, which show the begining of the last request including the currently running request and completion time of the last request in a session.
Returns information about the connections established to this instance of SQL Server and the details of each connection. Here we get into the new waters. This view shows us physical and logical connections to the SQL Server. SPID is again equal to session_id column. Logical connections are a kind of virtual connections in a physical connection in which MARS requests run. For logical connections the parent_connection_id is not null. Parent_connection_id identifies the primary physical connection that the MARS requests are using.
Returns information about each request that is executing within SQL Server. SPID is again equal to session_id. Each session can have MARS requests and each of these requests has a unique id under a session in the request_id column. connection_id provides the physical connection on which the MARS request runs on.
Now a SPID is equal to a request_id, so 2 MARS requests on a single connection have 2 different SPID's. SQL Server 2005 also has a new function called current_request_id() which returns the request currently executing under a session.
It show us the needed information for each request in a session, the connection it belongs, the start and end times, transaction isolation level, who ran it, etc.
Introduction of MARS has changed some long standing concepts in SQL Server transaction, connection and session management. To effectively resolve issues that rise up we have to get familiar with these new concepts and techniques. Hopefully I've presented MARS in a way that is easy to understand. And finally: MARS doesn't mean parallel execution. It means multiplexed or interleaved execution at exactly defined points.