Multiple Active Result Sets (MARS) – Transactions and Debugging

By Mladen Prajdić on 17 June 2007 | Tags: Application Design

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.

Transactions in MARS

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.

For example:

BEGIN TRAN
-- create a table
CREATE TABLE t1 (id INT, title VARCHAR(20) )
-- insert some data
INSERT INTO t1
SELECT 1, 'name 1' UNION ALL
SELECT 2, 'name 2' UNION ALL
SELECT 3, 'name 3' 

SELECT * FROM t1

-- save transaction to a savepoint 
SAVE TRAN savepoint1

-- insert some more data
INSERT INTO t1
SELECT 5, 'name 5'
SELECT * FROM t1
-- whoops, we don't want that nasty 5 in there, 
-- roll it back to the savepoint 
ROLLBACK TRAN savepoint1

-- insert a nice 4
INSERT INTO t1
SELECT 4, 'name 4'

SELECT * FROM t1

COMMIT

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.

string connString = @"server=MyServer; database=testDB; 
  trusted_connection=yes; 
  MultipleActiveResultSets=true";

using (SqlConnection conn = new SqlConnection(connString))
{
    // Command 1 represents the First Request/Batch
    SqlCommand cmd1 = new SqlCommand();
    cmd1.Connection = conn;                
    cmd1.CommandText = @"INSERT INTO t1 
  SELECT 1, 'name 1' UNION ALL 
  SELECT 2, 'name 2' UNION ALL 
  SELECT 3, 'name 3'; 
  Select * from t1;";

    // Command 2 represents the Second Request/Batch
    SqlCommand cmd2 = new SqlCommand();
    cmd2.Connection = conn;                
    cmd2.CommandText = "UPDATE t1 SET title = 'other name 2' WHERE id = 2";
    
    conn.Open();
    
    // Start the transaction
    // Both request run under the same transaction
    SqlTransaction tran = conn.BeginTransaction("mainTran");
    cmd1.Transaction = tran;
    cmd2.Transaction = tran;
    
    try
    {
        // Time T1 – run the insert and the select
        SqlDataReader rdr = cmd1.ExecuteReader();
        while (rdr.Read())
        {
            // Time T2
            // The execution will fail at this point because Transaction Savepoints aren't
            // allowed in MARS'ed environment                        
            tran.Save("savepoint1");
            cmd2.ExecuteNonQuery();
        }

        // Time T3 - executes in the first batch
        cmd1.CommandText = "INSERT INTO t1 SELECT 4, 'name 4';";

        // Time T4 - this will fail.
        cmd2.CommandText = "UPDATE t1 SET id = 'other name 5' WHERE id = 5;";                        
        // run the statements
        cmd1.ExecuteNonQuery();                        
        cmd2.ExecuteNonQuery();
 
        tran.Commit();
    }
    catch (Exception ex)
    {
        // this is the error message we get when trying to set the Transaction Savepoint: 
        // The transaction operation cannot be performed because there 
        // are pending requests working on this transaction.
        Console.WriteLine(ex.Message);
    }
}

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.

In .Net only one transaction can be set per connection. This means that this kind of code isn't possible:

private void MarsConcurrentTransactions()
{
    string connString = @"server=MyServer; 
  database=testDB; 
  trusted_connection=yes; 
  MultipleActiveResultSets=true";

    using (SqlConnection conn = new SqlConnection(connString))
    {
        // Command 1 represents the First Request/Batch
        SqlCommand cmd1 = new SqlCommand();
        cmd1.Connection = conn;
        cmd1.CommandText = "SELECT * FROM t1 WHERE id IS NULL";

        // Command 2 represents the Second Request/Batch
        SqlCommand cmd2 = new SqlCommand();
        cmd2.Connection = conn;
        cmd2.CommandText = " SELECT * FROM t1 WHERE id IS NOT NULL";

        conn.Open();

        // Start the transactions                
        SqlTransaction tran1 = conn.BeginTransaction("tran1");

        // this fails - can't have 2 concurrent transaction on the same connection
        SqlTransaction tran2 = conn.BeginTransaction("tran2");

        cmd1.Transaction = tran1;
        cmd2.Transaction = tran2;

        // ... more code ...
    }
}

Nor is this one:

string connString = @"server=MyServer; 
  database=testDB; 
  trusted_connection=yes; 
  MultipleActiveResultSets=true";

using (SqlConnection conn = new SqlConnection(connString))
{
    // Command 1 represents the First Request/Batch
    SqlCommand cmd1 = new SqlCommand();
    cmd1.Connection = conn;
    cmd1.CommandText = "SELECT title FROM t1";

    // Command 2 represents the Second Request/Batch
    SqlCommand cmd2 = new SqlCommand();
    cmd2.Connection = conn;
    cmd2.CommandText = "UPDATE t1 SET id = id + 5 WHERE title = @title";
    cmd2.Parameters.Add(new SqlParameter("@title", SqlDbType.VarChar, 20));
    conn.Open();

    // Start the transactions                
    SqlTransaction tran1 = conn.BeginTransaction("tran1");

    cmd1.Transaction = tran1;
    using (SqlDataReader rdr1 = cmd1.ExecuteReader())
    { 
        while (rdr1.Read())
        {
            cmd2.Parameters[0].Value = rdr1["title"].ToString();
            // this will FAIL because we can't mix sql trasaction with 
            // implicit transaction in which the update runs by default.
            cmd2.ExecuteNonQuery();                        
        }
    }
    tran1.Rollback();
}

That's because we still have 2 transactions. One explicit (SqlTransaction) and one implicit (the Sql Server's in which the update runs)

What we can and should do is put all SqlCommands under the same SqlTransaction while not setting savepoints:

string connString = @"server=MyServer; 
database=testDB; 
trusted_connection=yes; 
MultipleActiveResultSets=true";

using (SqlConnection conn = new SqlConnection(connString))
{
    // Command 1 represents the First Request/Batch
    SqlCommand cmd1 = new SqlCommand();
    cmd1.Connection = conn;
    cmd1.CommandText = "SELECT title FROM t1";

    // Command 2 represents the Second Request/Batch
    SqlCommand cmd2 = new SqlCommand();
    cmd2.Connection = conn;
    cmd2.CommandText = "UPDATE t1 SET id = id + 5 WHERE title = @title";
    cmd2.Parameters.Add(new SqlParameter("@title", SqlDbType.VarChar, 20));
    conn.Open();

    // Start the transactions                
    SqlTransaction tran1 = conn.BeginTransaction("tran1");
    
    cmd1.Transaction = tran1;
    cmd2.Transaction = tran1;
    using (SqlDataReader rdr1 = cmd1.ExecuteReader())
    { 
        while (rdr1.Read())
        {
            cmd2.Parameters[0].Value = rdr1["title"].ToString();
            cmd2.ExecuteNonQuery();                        
        }
    }
    tran1.Commit();
}

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.

Debugging and monitoring MARS

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.

sys.dm_exec_sessions

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.

sys.dm_exec_connections

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.

sys.dm_exec_requests

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.

When debugging this query might come in handy:

SELECT  r.session_id, r.request_id, 
        c.connection_id, c.parent_connection_id, c.connect_time, c.net_transport,
        s.HOST_NAME, s.program_name, s.nt_domain, s.login_name,
        s.last_request_start_time, s.last_request_end_time, s.transaction_isolation_level
FROM    sys.dm_exec_requests r 
        JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 
        JOIN sys.dm_exec_connections c ON s.session_id = c.session_id

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.

Conclusion

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.