Multiple Active Result Sets (MARS) – Transactions and Debugging
By Mladen Prajdić
on 17 June 2007
| 0 Comments
| Tags: Application Design
Article Series Navigation:
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.
