How do I enable nested transactions with ADO.NET and SQL Server?

Question

I have similar question to how to check if you are in a transaction. Instead of checking, how do I allow nested transactions?

I am using Microsoft SQL Server Database with ADO.NET. I have seen examples using T-SQL and examples starting transactions using begin and using transaction names. When calling connection.BeginTransaction, I call another function in the same connection, and it calls BeginTransaction again which gives me the exception:

SqlConnection does not support parallel transactions.

It appears many Microsoft variants allow this, but I can't figure out how to do it with my .mdf file.

How do I allow nested transactions with a Microsoft SQL Server Database using C# and ADO.NET?

Myself

Answer 1

SQL Server as a whole does not support nested transactions. In T-SQL you can issue a BEGIN TRAN inside an earlier BEGIN TRAN but this is just for convenience. It's only the outer transaction that counts. The .NET client for SQL Server (SqlConnection) does not even allow you to do that and throws this exception when you try.

Blanketing

My host says they offer 'Microsoft SQL 2008'. Is this T-SQL? If not where does T-SQL come from? I only want the outer transaction to count. Does 'Microsoft SQL 2008' not allow this? (maybe to configure it not to throw an exception?) – Myself

T-SQL is Microsoft's variant of the SQL standard that runs in all versions of SQL Server. If you want to avoid this exception, simply avoid calling BeginTransaction before committing or rolling back an earlier transaction - you can't being a transaction if an earlier transaction is still pending. I recommend you take a look at using TransactionScope (msdn.microsoft.com/en-us/library/…) instead of trying to manage them yourself. – Blanketing

hmm, thats not the same thing. This code was used with sqlite and i am unsure if that is supported with it (it could be since it seems to support all of ado.net). But this sounds strange, if T-SQL is in all versions of sql server then shouldnt i have that and shouldnt this be allowed? That one feature will make my life easier. Even though theres only one function that uses it (i guess i could destroy it since its only on init where only one connection is used until done.) – Myself

T-SQL is the SQL language dialect of SQL Server. SqlConnection issues commands to SQL Server in T-SQL but even though T-SQL allows you to (superficially) nest transactions, SqlConnection does not allow it. You could issue a BEGIN TRAN command yoursef via a SqlCommand but then you'd be bypassing all of .NETs support for transactions. – Blanketing

Hmm. Good to know. Thanks, it looks like the easiest way to solve this is to kill the outer transaction since it is only used once and was there to get better performance in sqlite (its only runs once so it isnt much of a boost). – Myself

Answer 2

It is a common misconception that SQL Server supports nested transactions. It does not. opening multiple transactions and then calling commit does absolutely nothing. you can easily write some test SQL to try this yourself. The only option here to emulate a nested transaction is to use Savepoints.

I should add that the only thing that matters is when @@TRAN_COUNT reaches zero is the point at which only the outer transaction will be committed.

Incurrent

Answer 3

SqlConnection conn = new SqlConnection(@"Data Source=test;Initial Catalog=test;User ID=usr;Password=pass");
conn.Open();
var com = conn.CreateCommand();

com.CommandText = "BEGIN TRANSACTION";
com.ExecuteNonQuery();
com.CommandText = "BEGIN TRANSACTION";
com.ExecuteNonQuery();
com.CommandText = "INSERT INTO testTable (ParamName,ParamValue) values ('test','test');";
com.ExecuteNonQuery();
com.CommandText = "COMMIT TRANSACTION";
com.ExecuteNonQuery();
com.CommandText = "ROlLBACK TRANSACTION";
com.ExecuteNonQuery();

com.CommandText = "SELECT COUNT(*) FROM testTable ";

MessageBox.Show(string.Format("Found {0} rows.", com.ExecuteScalar()));

Seamy

This answer could benefit from some comments about what you discovered by running it. – Sweetening