Nested Transactions in c#

[Mana]

Hi,

I want to implement nested transactions in C#.
When I write BEGIN TRANSACTION inside another BEGIN TRANSACTION in an
SQL Script it works fine. But when I call BeginTransaction() inside
another BeginTransaction() in a c# code on same connection object it
throws exception as "SQLConnection doesnot support parallel
transaction".

Following is the code snippet that i have written.
----------------------------------------------------------------------------------------
// Create and open a connection
SQLConnection connection = new SqlConnection("Initial
Catalog=mydatabase; Data Source=mymachine;Integrated Security=SSPI;");
connection.Open();

// Begin Outer Transaction
SQLTransaction transaction = connection.BeginTransaction();

SQLTransaction innerTransaction = null;
for (int i = 0; i < messageCount; ++i)
{
innerTransaction = connection.BeginTransaction();
// Do some stuff here eg execute update query
innerTransaction.Commit();
}

// Commit outer transaction and close the connection
transaction.Commit();
connection.Close();
----------------------------------------------------------------------------------------

Can anybody tell me how to implement nested transactions C#.

Mana

[Arnie Rowland]

Mana,

From an ADO standpoint, there is no such thing as a nested transaction with SQL Server -which is correct.

SQL Server doesn't really support nested TRANSACTIONS -even though it has seemed to you that it was possible.

When you nest BEGIN TRANSACTION, a second TRANSACTION is NOT started, but rather a 'marker' is placed and a counter incremented (actually a bit more, but I'm trying to keep it simple.

For example, in SQL code try nesting a TRANSACTION, and then from the inner TRANSACTION, issue a ROLLBACK, then return to the outer TRANSACTION and try to ROLLBACK or COMMIT. You should get an error because the TRANSACTION was completed at the inner level. There was only one even though it appears to have been two.

Try this example:

BEGIN TRANSACTION
PRINT 'Inside Outer Transaction'
BEGIN TRANSACTION
PRINT 'Inside Inner Transaction'
ROLLBACK TRANSACTION
PRINT 'Inner TRANSACTION Rolled Back'
COMMIT TRANSACTION
IF @@ERROR = 0
PRINT 'Outer TRANSACTION Committed'
ELSE
PRINT 'Outer Transaction DOES NOT Exist'

If you need to have 'partial' ROLLBACK functionality, and as a 'kinda' work-around, you may wish to read about SAVEPOINT in Books Online.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Post by Mana
Hi,
I want to implement nested transactions in C#.
When I write BEGIN TRANSACTION inside another BEGIN TRANSACTION in an
SQL Script it works fine. But when I call BeginTransaction() inside
another BeginTransaction() in a c# code on same connection object it
throws exception as "SQLConnection doesnot support parallel
transaction".
Following is the code snippet that i have written.
----------------------------------------------------------------------------------------
// Create and open a connection
SQLConnection connection = new SqlConnection("Initial
Catalog=mydatabase; Data Source=mymachine;Integrated Security=SSPI;");
connection.Open();
// Begin Outer Transaction
SQLTransaction transaction = connection.BeginTransaction();
SQLTransaction innerTransaction = null;
for (int i = 0; i < messageCount; ++i)
{
innerTransaction = connection.BeginTransaction();
// Do some stuff here eg execute update query
innerTransaction.Commit();
}
// Commit outer transaction and close the connection
transaction.Commit();
connection.Close();
----------------------------------------------------------------------------------------
Can anybody tell me how to implement nested transactions C#.
Mana

[Brian Gideon]

Mana,

Have you looked at the System.Transaction namespace? I can't remember
if it supports nested transaction.

Brian

Post by Mana
Hi,
I want to implement nested transactions in C#.
When I write BEGIN TRANSACTION inside another BEGIN TRANSACTION in an
SQL Script it works fine. But when I call BeginTransaction() inside
another BeginTransaction() in a c# code on same connection object it
throws exception as "SQLConnection doesnot support parallel
transaction".
Following is the code snippet that i have written.
----------------------------------------------------------------------------------------
// Create and open a connection
SQLConnection connection = new SqlConnection("Initial
Catalog=mydatabase; Data Source=mymachine;Integrated Security=SSPI;");
connection.Open();
// Begin Outer Transaction
SQLTransaction transaction = connection.BeginTransaction();
SQLTransaction innerTransaction = null;
for (int i = 0; i < messageCount; ++i)
{
innerTransaction = connection.BeginTransaction();
// Do some stuff here eg execute update query
innerTransaction.Commit();
}
// Commit outer transaction and close the connection
transaction.Commit();
connection.Close();
----------------------------------------------------------------------------------------
Can anybody tell me how to implement nested transactions C#.
Mana

[Tibor Karaszi]

Regardless of that the API might expose, SQL Server still doesn't support independent
sub-transactions. Only savepoints.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Post by Brian Gideon
Mana,
Have you looked at the System.Transaction namespace? I can't remember
if it supports nested transaction.
Brian

Post by Mana
Hi,
I want to implement nested transactions in C#.
When I write BEGIN TRANSACTION inside another BEGIN TRANSACTION in an
SQL Script it works fine. But when I call BeginTransaction() inside
another BeginTransaction() in a c# code on same connection object it
throws exception as "SQLConnection doesnot support parallel
transaction".
Following is the code snippet that i have written.
----------------------------------------------------------------------------------------
// Create and open a connection
SQLConnection connection = new SqlConnection("Initial
Catalog=mydatabase; Data Source=mymachine;Integrated Security=SSPI;");
connection.Open();
// Begin Outer Transaction
SQLTransaction transaction = connection.BeginTransaction();
SQLTransaction innerTransaction = null;
for (int i = 0; i < messageCount; ++i)
{
innerTransaction = connection.BeginTransaction();
// Do some stuff here eg execute update query
innerTransaction.Commit();
}
// Commit outer transaction and close the connection
transaction.Commit();
connection.Close();
----------------------------------------------------------------------------------------
Can anybody tell me how to implement nested transactions C#.
Mana