MARS, Transactions and SQL Error 3997, 3988 or 3983
Mars and Transactions
MARS (multiple active result-sets) does allow multiple commands to be active on the same connection, however there are some limitations on allowing multiple transactions to be active. I received a number of questions around this lately. I'll try to address the common denominator scenario in these questions and how to work around some of the error messages you may receive if MARS does not like what you are trying to do with your transactions.
Scenario:
The scenario in question involves implementing a flow that reads a result-set from SQL Server in a loop and goes back into the database to update some records. The app executes the update(s) in a transaction.
Here is the simplified ado.net code: Note that I am leaving out the cleanup and some of the initialization for compactness and table t1 is defined as "id int primary key, c2 xml not null".
...
SqlConnection mycconnection1 = new SqlConnection(@"...;MultipleActiveResultSets=true");
SqlCommand mycommand1 = new SqlCommand("select * from t1", mycconnection1);
SqlCommand mycommand2 = new SqlCommand("update t1 set c2='<test>123</test>' where id=@id", mycconnection1);
mycommand2.Parameters.Add("@id", SqlDbType.Int, 4);
mycconnection1.Open();
SqlDataReader mydr = mycommand1.ExecuteReader();// SELECT * FROM T1
while (mydr.Read()) //WHILE READING RESULTS
{
(new SqlCommand("begin tran", mycconnection1)).ExecuteNonQuery(); //BEGIN TRAN
int id = mydr.GetInt32(0);
mycommand2.Parameters["@id"].Value = id;
mycommand2.ExecuteNonQuery(); //UPDATE T1 SET C2=... WHERE ID=@ID
(new SqlCommand("commit tran", mycconnection1)).ExecuteNonQuery(); //COMMIT TRAN
}
...
The code will error at runtime. The error message you get back will depend on the method you used to manage your transactions but it is likely going to be one of these;
SQL Error 3997 "A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."
SQL Error 3988 "New transaction is not allowed because there are other threads running in the session."
SQL Error 3983 "The operation failed because the session is not single threaded."
So… What now?
When thinking about transactions it is critical to understand that almost all statements in SQL Server run in a transaction without you having to implicitly or explicitly start one. Most obvious example is : 'delete from t1' will delete all records or none. There is nothing in between when SQL Server executes this statement. In the case above however, starting a new transaction on connection that already has an active atomic command executing is not allowed. Depending on the situation and the behavior you want you may be able to do one of the following;
#1 You can simply encapsulate all commands in a single transaction. Obviously in this case, you get one big transaction and that means you hold on to all your resources until the end of the entire transaction. That could mean more blocking in your application.
SqlConnection mycconnection1 = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=tempdb;Data Source=patates\yukon;MultipleActiveResultSets=true");
SqlCommand mycommand1 = new SqlCommand("select * from t1", mycconnection1);
SqlCommand mycommand2 = new SqlCommand("update t1 set c2='<test>123</test>' where id=@id", mycconnection1);
mycommand2.Parameters.Add("@id", SqlDbType.Int, 4);
SqlTransaction mytransaction1;
mycconnection1.Open();
mytransaction1 = mycconnection1.BeginTransaction();
mycommand1.Transaction = mytransaction1;
mycommand2.Transaction = mytransaction1;
SqlDataReader mydr = mycommand1.ExecuteReader();
while (mydr.Read())
{
int id = mydr.GetInt32(0);
mycommand2.Parameters["@id"].Value = id;
mycommand2.ExecuteNonQuery();
}
...
#2 alternatively, you can submit the inner command you want transacted as a single batch or pack the commands in a stored procedure with the transaction statements with the transaction statements in the batch or the procedure. SQL Server can allow this special case and the batch or the stored procedure can work in its own transaction. So the code looks like this;
//BATCH
SqlConnection mycconnection1 = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=tempdb;Data Source=patates\yukon;MultipleActiveResultSets=true");
SqlCommand mycommand1 = new SqlCommand("select * from t1", mycconnection1);
SqlCommand mycommand2 = new SqlCommand("begin tran;update t1 set c2='<test>123</test>' where id=@id;commit tran", mycconnection1);
mycommand2.Parameters.Add("@id", SqlDbType.Int, 4);
mycconnection1.Open();
SqlDataReader mydr = mycommand1.ExecuteReader();
while (mydr.Read())
{
int id = mydr.GetInt32(0);
mycommand2.Parameters["@id"].Value = id;
mycommand2.ExecuteNonQuery();
}
...
This is stating the obvious but the code above works because we have a well defined boundary for the transactions.
If you have comments on MARS and transactions and other data retrieval ‘flows’ that you are having difficulties with, please send those my way through the comments section below.
That's it for now. Thanks