Nested Transactions - what for?

Hi
Can someone explain what nested transactions can be used for? I know about
the outer an inner transaction but you still either commit or rollback all
transactions.
In Oracle you can start autonomous transaction within another transaction
and any commit or rollback statement affects its own transaction.
So - I am not looking at explanation how to create nested transactions but
what could be a practical use of them?I think that this is largely a leftover from the original Sybase design.
As you know, there are no nested transactions in SQL Server, semantically speaking.
Why did they put in the syntax then? I guess you had to be in that Sybase design meeting to be able
to answer that question. My guess is that it allow for code modules to call each other. One stored
proc programmer want to transaction-protect the code. The proc he is writing will call another proc
which is also transaction-protected. Stuff like that...
Note that savepoint is another matter. This gives the ability to perform partial rollbacks. This is
IMO much more useful then doing partial commits. I find the thought of a partial commit a bit scary,
but perhaps this is because the feature isn't available in SQL Server.
One scenario I would consider it useful, though, is when you want to log actions even if you
rollback the transaction. In SQL Server 2000, we can often achieve the same functionality logging to
a table variable (which survives a ROLLBACK).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Witold" <witoldi@.shaw.ca> wrote in message news:bWo8d.211357$%S.80172@.pd7tw2no...
> Hi
> Can someone explain what nested transactions can be used for? I know about
> the outer an inner transaction but you still either commit or rollback all
> transactions.
> In Oracle you can start autonomous transaction within another transaction
> and any commit or rollback statement affects its own transaction.
> So - I am not looking at explanation how to create nested transactions but
> what could be a practical use of them?
>

Nested Transactions - what for?

Hi
Can someone explain what nested transactions can be used for? I know about
the outer an inner transaction but you still either commit or rollback all
transactions.
In Oracle you can start autonomous transaction within another transaction
and any commit or rollback statement affects its own transaction.
So - I am not looking at explanation how to create nested transactions but
what could be a practical use of them?
I think that this is largely a leftover from the original Sybase design.
As you know, there are no nested transactions in SQL Server, semantically speaking.
Why did they put in the syntax then? I guess you had to be in that Sybase design meeting to be able
to answer that question. My guess is that it allow for code modules to call each other. One stored
proc programmer want to transaction-protect the code. The proc he is writing will call another proc
which is also transaction-protected. Stuff like that...
Note that savepoint is another matter. This gives the ability to perform partial rollbacks. This is
IMO much more useful then doing partial commits. I find the thought of a partial commit a bit scary,
but perhaps this is because the feature isn't available in SQL Server.
One scenario I would consider it useful, though, is when you want to log actions even if you
rollback the transaction. In SQL Server 2000, we can often achieve the same functionality logging to
a table variable (which survives a ROLLBACK).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Witold" <witoldi@.shaw.ca> wrote in message news:bWo8d.211357$%S.80172@.pd7tw2no...
> Hi
> Can someone explain what nested transactions can be used for? I know about
> the outer an inner transaction but you still either commit or rollback all
> transactions.
> In Oracle you can start autonomous transaction within another transaction
> and any commit or rollback statement affects its own transaction.
> So - I am not looking at explanation how to create nested transactions but
> what could be a practical use of them?
>
sql

Nested Transactions

Hello! Sorry if I choose wrong forum for this post.
I have next scenario:

Transaction1

Transaction2

Commit Transaction2
Transaction3

Commit Transaction3

Commit Transaction1 I wanna implement it in C# code (.NET 1.1, MS SQL 2000):

IDbConnection connection = new OleDbConnection(connectionString);

IDbTransaction transaction = null;

connection.Open();

/* NOTE: I can't use something like this:

* transaction outter = connection.BeginTransaction();

* transacrion inner = connection.BeginTransaction();

* // Here I'm getting an error: OleDB doesn't support parallel transactions,

* // though I wanna create nested one.

*/

// So, I decided to turn implicit transactions mode on in hope it should help:

IDbCommand bt = connection.CreateCommand();

bt.CommandText = " SET IMPLICIT_TRANSACTIONS ON; BEGIN TRANSACTION;";

bt.ExecuteNonQuery();

transaction = connection.BeginTransaction();

IDbCommand command = connection.CreateCommand();

command.Transaction = transaction;

command.CommandType = CommandType.Text;

command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description');";

command.ExecuteNonQuery();

command.CommandText = "SELECT @.@.TRANCOUNT;";

int transCount = (int)command.ExecuteScalar(); // It's equal to 2 here, seems to be OK.

transaction.Commit();

// Let's start the second "nested" transaction

IDbTransaction transaction1 = connection.BeginTransaction();

IDbCommand command1 = connection.CreateCommand();

command1.Transaction = transaction1;

command1.CommandType = CommandType.Text;

command1.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description');";

command1.ExecuteNonQuery();

command1.CommandText = " SELECT @.@.TRANCOUNT; ";

transCount = (int)command1.ExecuteScalar(); // WOW! Now it's already equal to 1 here.

transaction1.Commit();

// Well, here I wanna close outter transaction, but... I'll get exception: There is nothing to commit here

bt = connection.CreateCommand();

bt.CommandText = "Commit TRANSACTION";

bt.ExecuteNonQuery();

Well, I know that SQL Server has no support for nested transactions. Nesting of transactions only increments @.@.TRANCOUNT and it is the final commit that has control over the outcome of the entire transaction. And I can't use the new TransactionScope class in .NET Framework 2.0 which has promotable transactions concept.

Please help me: How can I implement required operations?

You could try using a ServicedComponent and using COM+ functionalities to perform that type of transactions. I don't know if it will work well with OleDB - you just have to try it.|||

Thank you Miguelb for reply. I found solution much simpler :). If somebody find it helpful this is it:

public static void InitiateTransactionsChain(string connectionString)
{
using (IDbConnection connection = new OleDbConnection(connectionString))
{
IDbCommand mostOutter = null;
connection.Open();
try
{
connection.Open();

mostOutter = connection.CreateCommand();
mostOutter.CommandText = "SET IMPLICIT_TRANSACTIONS ON;"; // There is no BEGIN TRANSACTION here
mostOutter.ExecuteNonQuery();
ExecuteNestedTrans(connection, 100, "help me"); // see below
ExecuteNestedTrans(connection, 101, "hope it is OK"); // see below
// If any exception occurs previous transactions will be rolled back!
// ExecuteNestedTrans(connection, 102, null); // see below
mostOutter = connection.CreateCommand();
mostOutter.CommandText = "Commit TRANSACTION";
mostOutter.ExecuteNonQuery();
}
catch (Exception ex)
{
// if something goes wrong, we can easy roll back everything :
if (mostOutter == null)
return;
mostOutter = connection.CreateCommand();
mostOutter.CommandText = "ROLLBACK TRANSACTION";
mostOutter.ExecuteScalar(); // That's all...
}
}
}
// Here is ExecuteNestedTrans:
public static void ExecuteNestedTrans(IDbConnection connection, int value, string description)
{
IDbCommand fakeTransaction = null;
// Let's cheat here: Increase the counter (@.@.TRANCOUNT):
fakeTransaction = connection.CreateCommand();
fakeTransaction.CommandText = "BEGIN TRANSACTION;";
fakeTransaction.ExecuteNonQuery();

IDbTransaction innerTransaction = null;
try
{
innerTransaction = connection.BeginTransaction();
IDbCommand command = connection.CreateCommand();
// Somewhere here transcount somehow will be decremented by 1
// Strange, isn't it?
command.Transaction = innerTransaction;
command.CommandType = CommandType.Text;
command.CommandText = String.Format("Insert into Region (RegionID, RegionDescription) VALUES ({0}, '{1}');", value, description);
command.ExecuteNonQuery();
innerTransaction.Commit();
}
catch (Exception exInner)
{
if (innerTransaction != null)
{
innerTransaction.Rollback();
throw exInner;
}
}
// NOTE: There is no need to commit fakeTransaction.
// It will be commited by something somewhere deep in .NET
// That is why code below is commented
//fakeTransaction = connection.CreateCommand();
//fakeTransaction.CommandText = "COMMIT TRANSACTION;";
//fakeTransaction.ExecuteNonQuery();
if (description == null)
throw new Exception("He-he!");
}

That's all :). Thanks for the time You spent for me.

Nested Transaction: how to commit the outer SP even if the inner ones fails.

Since some days I'm facing problems with the nested transactions and I read the they are not fully supported in sql server 2005 so I'd need an help from a more experienced SS programming.

My outer SP must live inside a transaction. It calles an another SP but IT MUST NOT ROLLBACK in case the INNER SP fails.

My nested SP, let's call it INNERSP is quite complex because it involves INSERT-UPDATE-DELETE, mathematic calculation and calls other SP as well.

Of course I want to make the INNERSP error-proof and I'd like to wrap it inside a try..catch structure.

If my INNERSP is:

BEGIN TRY

Insert ... --this insert cause an error that is trapped in the BEGIN CATCH

BEGIN TRANSACTION
COMMIT

END TRY

BEGIN CATCH
ROLLBACK TRANSACTION

END CATCH

The Rollback statement roll back also the transaction of the caller, but for me it is not acceptable.

I was investigating on the SAVE TRANSACTION statement but if I use it the @.@.TRANCOUNT is not decremented and my OUTERSP ends with a @.@.TRANCOUNT that is not zero, but from what I know "Nothing is actually committed until @.@.TRANCOUNT = 0"

Any helps is more that welcome!!
Thank you

Marina B.

Try putting your begin transaction statement before the work that you want inside of the transaction.|||

Interesting problem. It appears that even if an error in the called procedure is caught, the calling procedure's transaction still knows about it somehow:

if object_id('x') is not null drop table x

go

create table x(x1 int)

go

if object_id('px') is not null drop proc px

go

create proc px as

begin try

insert x values ('x')

end try

begin catch

print 'caught px'

end catch

go

if object_id('py') is not null drop proc py

go

create proc py as

begin tran

begin try

exec px

print @.@.error

insert x values (2)

commit

end try

begin catch

print 'caught py'

print error_message()

rollback

end catch

go

exec py

select * from x

go

/*

caught px

0

caught py

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

*/

|||

Not surprisingly, this behavior is my design. More importantly, this only applies to "fatal" errors. Per BOL:

"A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block."

This script is changed from the one above: the error that occurs in px is not a "fatal" error (the insert of "x" into an integer column is considered a syntax error and is fatal), and after it is caught, the py procedure does complete the transaction, resulting in a new row being inserted into table x:

set nocount on

go

if object_id('x') is not null drop table x

go

create table x(x1 int primary key)

go

if object_id('px') is not null drop proc px

go

create proc px as

begin try

insert x values (1)

end try

begin catch

print 'caught px'

print error_message()

print xact_state()

end catch

go

if object_id('py') is not null drop proc py

go

create proc py as

begin tran

begin try

insert x values (1)

exec px

print @.@.error

commit

end try

begin catch

print 'caught py'

print error_message()

rollback

end catch

go

exec py

select * from x

go

/*

caught px

Violation of PRIMARY KEY constraint 'PK__x__177C9889'. Cannot insert duplicate key in object 'dbo.x'.

1

0

*/

|||

SQL Server does not support autonomous transactions. Only way is to write an extended SP/SQLCLR SP to make a loopback connection to the database and call the inner SP without enlisting in same transaction. This technique of course has lot of disadvantages and side-effects. For one, you could create a distributed deadlock that is undetectable by SQL Server. You might end up blocking your own transaction consuming valuable resources on the server and so on.

Another technique is to use a table variable to store the results from the inner SP. Since table variables do not participate in user transactions, any subsequent COMMIT or ROLLBACK will not affect the data in the table variable. You can then retrieve the rows from the table variable after the transaction or in the CATCH block successfully. This will work from SQL Server 2000 onwards except there is no TRY...CATCH there.

|||

Hello everybody,

thankx for the answers .

By the way I found another solution that solve my problem but because SQL server programming is not my main skill I would like to know what to you think about it.

select @.@.trancount, 'Entering'

begin transaction

begin try

-- Detect if the procedure was called from an active transaction and save that for later use.In the procedure, @.TranCounter = 0 means there was no active transaction and the procedure started one.

-- @.TranCounter > 0 means an active transaction was started before the procedure was called.

DECLARE @.TranCounter INT;

DECLARE @.i INT;

SET @.TranCounter = @.@.TRANCOUNT;

select @.TranCounter,'Entering s.p. 1st level from broker'

IF @.TranCounter > 0

BEGIN

-- Procedure called when there is an active transaction.Create a savepoint to be able to roll back only the work done in the procedure if there is an error.

SAVE TRANSACTION ProcedureSave;

select @.@.trancount, 'Save transaction SP 1st level'

END

ELSE

BEGIN

-- Procedure must start its own transaction.

BEGIN TRANSACTION;

select @.@.trancount, 'New transaction created on the s.p. 1st level from broker'

END

BEGIN TRY

set @.i = 1/0; -- THIS IS THE INSTRUCTION THAT CAUSE AN ERROR IN THE FIRST LEVEL S.P.

--this is an another nested SP

DECLARE @.TranCounterSP2 INT;

SET @.TranCounterSP2 = @.@.TRANCOUNT;

select @.TranCounterSP2,'Entering s.p. 2nd level from s.p. 1st level'

IF @.TranCounterSP2 > 0

BEGIN

-- Procedure called when there is an active transaction.Create a savepoint to be able to roll back only the work done in the procedure if there is an error.

SAVE TRANSACTION ProcedureSave2;

select @.@.trancount, 'Save transaction SP 2nd level'

END

ELSE

BEGIN

-- Procedure must start its own transaction.

BEGIN TRANSACTION;

select @.@.trancount, 'New transaction created on the s.p. 2nd level from sp 1st level'

END

BEGIN TRY

set @.i = 1/0; -- THIS IS THE INSTRUCTION THAT CAUSE AN ERROR IN THE SECOND LEVEL S.P.

IF @.TranCounterSP2 = 0

BEGIN

COMMIT TRANSACTION

END

select @.@.trancount, 'S.p. 2nd level was committed'

END TRY

BEGIN CATCH

select @.@.trancount, 'SP 2nd level transaction on going to be rolled back rolled back'

IF @.TranCounterSP2 = 0

-- Transaction started in procedure. Roll back complete transaction - included the outer one.

ROLLBACK TRANSACTION;

ELSE

IF XACT_STATE() <> -1

ROLLBACK TRANSACTION ProcedureSave2;select @.@.trancount, 'SP 2nd level transaction was rolled back'

END CATCH

--this is the end of an another nested SP

-- Get here if no errors; must commit any transaction started in the procedure, but not commit a transaction started before the transaction was called.

IF @.TranCounter = 0

-- @.TranCounter = 0 means no transaction was started before the procedure was called. The procedure must commit the transaction it started.

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

select @.@.trancount, 'SP 1st level transaction on going to be rolled back rolled back'

-- An error occurred; must determine which type of rollback will roll back only the work done in the procedure.

IF @.TranCounter = 0

-- Transaction started in procedure. Roll back complete transaction (INCLUDED the outer TRANSACTION).

ROLLBACK TRANSACTION;

ELSE

-- Transaction started before procedure called, do not roll back modifications made before the procedure was called.

IF XACT_STATE() <> -1

-- If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure.

ROLLBACK TRANSACTION ProcedureSave;

select @.@.trancount, 'SP 1st level transaction was rolled back'

END CATCH

commit transaction -- for the outer transaction

select @.@.trancount, 'Outer transaction was committed'

end try

begin catch

if @.@.trancount>0

begin

rollback transaction

select @.@.trancount, 'Outer transaction was rolled back'

end

end catch

select @.@.trancount, 'Outer'

If I try this script everything works as I want, somebody can also confirm me that the transaction are also properly define and I'm not going to execute code in the wrong one?

Thank you

Marina B.

Nested Transaction

Hi,
I wanted to know why the nested transaction is used.
If I am not wrong, what I understand by the below SQL is that the
"commit tran outer1"
should be run to commit the entire transaction and to release the locks
hold by transaction.
and if the rollback happens anywhere, the entire transaction is rolled
back.
As per my current understanding I don't see any use for nested
transaction.
And also what is the significance of using savepoint in transaction?
Begin tran outer1
Update table1 set column1 = 45 where column2 = 56
begin tran outer2
Update table2 set column1 = 45 where column2 = 56
commit tran outer2
Update table3 set column1 = 45 where column2 = 56
commit tran outer1
ThanksHi,
If you use a save point you can rollbackup or commit based on the method you
do the save transaction. The savepoint will define a location
to which a transaction can return if the part of transaction is cancelled.
But in your example you have not Save point for that you have to use
SAVE TRAN <Tran Name>
See details for Begin tran, Commit Tran, Rollback and Save Tran in books
online.
In your case nested tran is not required. see the below example form books
online for nested trans.
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
Thanks
Hari
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156943181.580372.44710@.i42g2000cwa.googlegroups.com...
> Hi,
> I wanted to know why the nested transaction is used.
> If I am not wrong, what I understand by the below SQL is that the
> "commit tran outer1"
> should be run to commit the entire transaction and to release the locks
> hold by transaction.
> and if the rollback happens anywhere, the entire transaction is rolled
> back.
> As per my current understanding I don't see any use for nested
> transaction.
> And also what is the significance of using savepoint in transaction?
> Begin tran outer1
> Update table1 set column1 = 45 where column2 = 56
> begin tran outer2
> Update table2 set column1 = 45 where column2 = 56
> commit tran outer2
> Update table3 set column1 = 45 where column2 = 56
> commit tran outer1
>
> Thanks
>

Nested Transaction

Hi,
I wanted to know why the nested transaction is used.
If I am not wrong, what I understand by the below SQL is that the
"commit tran outer1"
should be run to commit the entire transaction and to release the locks
hold by transaction.
and if the rollback happens anywhere, the entire transaction is rolled
back.
As per my current understanding I don't see any use for nested
transaction.
And also what is the significance of using savepoint in transaction?
Begin tran outer1
Update table1 set column1 = 45 where column2 = 56
begin tran outer2
Update table2 set column1 = 45 where column2 = 56
commit tran outer2
Update table3 set column1 = 45 where column2 = 56
commit tran outer1
ThanksHi,
If you use a save point you can rollbackup or commit based on the method you
do the save transaction. The savepoint will define a location
to which a transaction can return if the part of transaction is cancelled.
But in your example you have not Save point for that you have to use
SAVE TRAN <Tran Name>
See details for Begin tran, Commit Tran, Rollback and Save Tran in books
online.
In your case nested tran is not required. see the below example form books
online for nested trans.
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
Thanks
Hari
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1156943181.580372.44710@.i42g2000cwa.googlegroups.com...
> Hi,
> I wanted to know why the nested transaction is used.
> If I am not wrong, what I understand by the below SQL is that the
> "commit tran outer1"
> should be run to commit the entire transaction and to release the locks
> hold by transaction.
> and if the rollback happens anywhere, the entire transaction is rolled
> back.
> As per my current understanding I don't see any use for nested
> transaction.
> And also what is the significance of using savepoint in transaction?
> Begin tran outer1
> Update table1 set column1 = 45 where column2 = 56
> begin tran outer2
> Update table2 set column1 = 45 where column2 = 56
> commit tran outer2
> Update table3 set column1 = 45 where column2 = 56
> commit tran outer1
>
> Thanks
>sql

Nested Database Transactions in Forms

This should be a fairly simple question. It's based on this error message:

"Transaction count after EXECUTEindicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.Previous count = 1, current count = 0."

I get this when executing a stored procedure upon processing a form. This error happens when I intentionally provide input to the stored procedure that I know should cause it to error out. I catch the exception, and it contains the error message, but it also contains the above message added on to it, which I don't want.

I won't post the entire stored procedure. But I'll list a digest of it (Just those lines that are significant). Assume that what's included is what happens when I provide bad input:

BEGIN

BEGIN TRY
BEGIN TRANSACTION
RAISERROR('The item selected does not exist in the database.', 16, 1);
COMMIT -- This won't execute when the RAISERROR breaks out to the CATCH block
END TRY

BEGIN CATCH
ROLLBACK
DECLARE @.ErrorSeverity INT, @.ErrorMessage NVARCHAR(4000)
SET @.ErrorSeverity = ERROR_SEVERITY()
SET @.ErrorMessage = ERROR_MESSAGE()
RAISERROR(@.ErrorMessage, @.ErrorSeverity, 1)
END CATCH

END

Okay, so that works fine. The problem is when I execute this with an SqlCommand object, on which I've opened a transaction. I won't include the entire setup of the data (with the parameters, since those seem fine), but I'll give my code that opens the connection and executes the query:

con.Open();
SqlTransaction transaction = con.BeginTransaction();
command.Transaction = transaction;

try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
finally
{
con.Close();
}

I'm calling the stored procedure listed above (which has its own transaction), using a SqlCommand object on which I've opened a transaction. When there is no error it works fine. But when I give the stored procedure bad data, it gives me that message about the transaction count.

Is there something I need to do in either my SQL or my C# to handle this? The entire message found in the Exception's Message is a concatenation of the message in my RAISERROR, along with the transaction count message I quoted at the beginning.

Thanks,

-Dan

In playing around with it, I've come to learn that the Exception.Message is the concatenation of any messages the exception holds. So, my CATCH block could contain this instead:

Label.Text = String.Format("Error: {0}", ex.Errors[0].Message);

That only gives the first error, which is the one I want (the one specified in RAISERROR). I just don't know if this is what I should do. Is that transaction count message something I should be concerned about, or will that always happen? Should I just use the first message in the exception?

Thanks.