MSOLEDBSQL Explicit Begin Transaction fails with MARS enabled

Question

Using MSOLEDBSQL v19 and SQL Server 2022, an explicit "BEGIN TRANSACTION" fails with "A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back" with MARS enabled. This is the first command executed immediately after opening the connection.

Answer 1

So you submit BEGIN TRANSACTION the first thing you do on your MARS-enabled connection, and that's all that batch contains?

I guess the gist of the error message is that you cannot do that. If you want to have an explicit transaction, you need to have BEGIN TRANSACTION and COMMIT TRANSACTION in the same batch.

Or maybe you should reconsider your choice of using MARS (Multiple Active Result Sets) in the first place. MARS is intended for the scenario where you are reading a number of rows, and you want to update them one by one as they come. This pattern has always seemed dubious to me, and even more so in the time of the cloud, when you should avoid chatty applications.

Answer 2

Hello @Bill Pearson ,

When using Multiple Active Result Sets (MARS) in SQL Server, transactions must be completed within the same batch they were started. If a transaction is still active at the end of the batch, SQL Server will automatically roll it back.

To avoid this issue, ensure that you commit or roll back the transaction before the batch ends. Here’s an example of how you might structure your code:

 

BEGIN TRANSACTION;
-- Your SQL statements here
COMMIT; -- or ROLLBACK if needed

 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Answer 3

I thought I should summarize this issue as follows and call this the answer, even though I think the behavior of MSOLEDBSQL v19 is anomalous:

Executing a native SQL "begin transaction" statement fails with MSOLEDBSQL v19 with the error "A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back". This does not fail with v18. This is the failing statement:

db.Execute("BEGIN TRANSACTION", , ADODB.CommandTypeEnum.adCmdText + ADODB.ExecuteOptionEnum.adExecuteNoRecords)

The native ADO.BeginTrans/.CommitTrans/.RollbackTrans functions work properly when the driver is v19.

Using SQL Server 2022 latest CU

v19 connection string:

Provider=MSOLEDBSQL19;Trusted_Connection=yes;Server=xxxxxxxx;MARS Connection=True;Database=xxxxxxxx;DataTypeCompatibility=80;Use Encryption For Data=Optional;

v18 connection string:

Provider=MSOLEDBSQL;Trusted_Connection=yes;Server=xxxxxxxx;MultipleActiveResultsets=True;Database=xxxxxxxx;