Nested Transaction!

The following example is given in BOL under the topic 'Nested
Transaction':
---
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
---
The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
Apart from this, there is another transaction named 'OutOfProc' (which,
if I am not mistaken, isn't a part & parcel of the stored procedure
'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
where or which is the nested transaction? Shouldn't a nested
transaction have 1 transaction under another transaction something like
this (similar to nested If...Else conditions):
---
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
AS
BEGIN TRANSACTION InProc
--Do Something
BEGIN TRANSACTION InProc1
--Do Something More
BEGIN TRANSACTION InProc2
--Do Something More
COMMIT TRANSACTION InProc
---
Or does the presence of more than 1 SQL statement (the 2 INSERT
queries) within the transaction 'InProc' (in the stored procedure
'TransProc') make it a nested transaction?
Thanks,
ArpanA begin tran must have either a commit/rollback tran. Thus, the TransProc's
Inproc transaction is participating in the OutProc transaction. If
commit/rollback tran OutProc is not explicitly called and the connection to
the server is dropped, the transaction will be forced to rollback.
As stated in bol, if outer most commit/rollback is what really important. It
decides the final commit all or rollback all.
-oj
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
> The following example is given in BOL under the topic 'Nested
> Transaction':
> ---
> 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
> ---
> The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
> Apart from this, there is another transaction named 'OutOfProc' (which,
> if I am not mistaken, isn't a part & parcel of the stored procedure
> 'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
> the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
> where or which is the nested transaction? Shouldn't a nested
> transaction have 1 transaction under another transaction something like
> this (similar to nested If...Else conditions):
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> --Do Something
> BEGIN TRANSACTION InProc1
> --Do Something More
> BEGIN TRANSACTION InProc2
> --Do Something More
> COMMIT TRANSACTION InProc
> ---
> Or does the presence of more than 1 SQL statement (the 2 INSERT
> queries) within the transaction 'InProc' (in the stored procedure
> 'TransProc') make it a nested transaction?
> Thanks,
> Arpan
>|||Thanks, OJ, for your help. How stupid of me....actually I had
misinterpreted the example given in BOL :-)
Thanks once again,
Regards,
Arpan|||Transactions are not really nested. There can only be one outstanding
transaction context for a connection. The global variable @.@.TRANCOUNT
reports whether or not a transaction context is outstanding, and its value
immediately before a Transact-SQL statement begins executing determines
whether or not a new transaction context is initiated. When @.@.TRANCOUNT =
0, no transaction context is outstanding for the connection. Each time a
BEGIN TRANSACTION is executed, @.@.TRANCOUNT is incremented. Only when
@.@.TRANCOUNT = 0 immediately before a BEGIN TRANSACTION statement is a new
transaction started by that BEGIN TRANSACTION statement. Each sucessive
BEGIN TRANSACTION increments @.@.TRANCOUNT. Each COMMIT TRANSACTION
decrements @.@.TRANCOUNT. Only when @.@.TRANCOUNT = 1 immediately before a
COMMIT TRANSACTION statement are changes made within the transaction made
permanent by that COMMIT TRANSACTION statement. All Transact-SQL statements
that alter the state or schema of the database operate within the context of
a transaction. If a transaction context is not already outstanding, then a
new transaction context is initiated before executing the statement. If
IMPLICIT_TRANSACTIONS is OFF, then changes made by the statement are
committed immediately after the statement completes. This is called
"autocommit" mode. If IMPLICIT_TRANSACTIONS is ON, then an explicit COMMIT
WORK must be issued to commit the transaction. A transaction can span
multiple statements, multiple stored procedure calls, even multiple batches.
If a transaction is outstanding, dynamic SQL executed via either EXEC() or
sp_executesql executes within that transaction context. Since changes made
while a transaction context is outstanding are not made permanent until
they're committed, ROLLBACK backs all of the changes for the entire
transaction context. The only exception is when a savepoint is specified on
a ROLLBACK statement. SAVE TRANSACTION places a marker in the transaction
log that identifies a reference point which can be specified in a ROLLBACK
statement to partially backout changes made while a transaction context is
outstanding. When a ROLLBACK savepoint statement is executed, all changes
made after the save point are backed out, and the transaction context
remains outstanding.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
> The following example is given in BOL under the topic 'Nested
> Transaction':
> ---
> 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
> ---
> The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
> Apart from this, there is another transaction named 'OutOfProc' (which,
> if I am not mistaken, isn't a part & parcel of the stored procedure
> 'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
> the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
> where or which is the nested transaction? Shouldn't a nested
> transaction have 1 transaction under another transaction something like
> this (similar to nested If...Else conditions):
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> --Do Something
> BEGIN TRANSACTION InProc1
> --Do Something More
> BEGIN TRANSACTION InProc2
> --Do Something More
> COMMIT TRANSACTION InProc
> ---
> Or does the presence of more than 1 SQL statement (the 2 INSERT
> queries) within the transaction 'InProc' (in the stored procedure
> 'TransProc') make it a nested transaction?
> Thanks,
> Arpan
>|||Dude, paragraph breaks :)

> Transactions are not really nested. There can only be one outstanding
> transaction context for a connection.
I don't know that this particularly means that transactions aren't nested.
By your definition IF..THEN statements are not really nested. I don't think
that nested transactions implies any technical innerworkings more than it
just simply implies that you can syntactically do:
BEGIN TRANSACTION
BEGIN TRANSACTION
BEGIN TRANSACTION
COMMIT TRANSACTION
COMMIT TRANSACTION
COMMIT TRANSACTION
It is more or less meaningless to us whether a stack is used or a counter
and whether or not ROLLBACK kills the whole stack or just goes back to the
original point. Either way I still consider them nested because of syntax.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Transactions are not really nested. There can only be one outstanding
> transaction context for a connection. The global variable @.@.TRANCOUNT
> reports whether or not a transaction context is outstanding, and its value
> immediately before a Transact-SQL statement begins executing determines
> whether or not a new transaction context is initiated. When @.@.TRANCOUNT =
> 0, no transaction context is outstanding for the connection. Each time a
> BEGIN TRANSACTION is executed, @.@.TRANCOUNT is incremented. Only when
> @.@.TRANCOUNT = 0 immediately before a BEGIN TRANSACTION statement is a new
> transaction started by that BEGIN TRANSACTION statement. Each sucessive
> BEGIN TRANSACTION increments @.@.TRANCOUNT. Each COMMIT TRANSACTION
> decrements @.@.TRANCOUNT. Only when @.@.TRANCOUNT = 1 immediately before a
> COMMIT TRANSACTION statement are changes made within the transaction made
> permanent by that COMMIT TRANSACTION statement. All Transact-SQL
> statements
> that alter the state or schema of the database operate within the context
> of
> a transaction. If a transaction context is not already outstanding, then
> a
> new transaction context is initiated before executing the statement. If
> IMPLICIT_TRANSACTIONS is OFF, then changes made by the statement are
> committed immediately after the statement completes. This is called
> "autocommit" mode. If IMPLICIT_TRANSACTIONS is ON, then an explicit
> COMMIT
> WORK must be issued to commit the transaction. A transaction can span
> multiple statements, multiple stored procedure calls, even multiple
> batches.
> If a transaction is outstanding, dynamic SQL executed via either EXEC() or
> sp_executesql executes within that transaction context. Since changes
> made
> while a transaction context is outstanding are not made permanent until
> they're committed, ROLLBACK backs all of the changes for the entire
> transaction context. The only exception is when a savepoint is specified
> on
> a ROLLBACK statement. SAVE TRANSACTION places a marker in the transaction
> log that identifies a reference point which can be specified in a ROLLBACK
> statement to partially backout changes made while a transaction context is
> outstanding. When a ROLLBACK savepoint statement is executed, all changes
> made after the save point are backed out, and the transaction context
> remains outstanding.
>
> "Arpan" <arpan_de@.hotmail.com> wrote in message
> news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
>|||There is a difference. For example:
IF cond1
THEN IF cond2
THEN IF cond3
THEN stmt1
ELSE stmt2
Which condition does this ELSE go with? Here's another example:
for (int i = 1; i < 10; i++)
for (int j = 1; j < 10; j++)
for (int k = 1; k < 10; k++)
{
Console.WriteLine(i + j + k);
if (i + j + k == 55) break;
}
Which for does the break go with?
One of the common misunderstandings about transactions is that since there m
ust always be a matching commit transaction for every begin transaction, and
that transactions can be "nested," many newbies erroneously assume that a r
ollback only backs out the innermost block of statements--that is, to the po
int of the innermost BEGIN TRANSACTION. The assumption follows from the pat
tern etched in their brain by the second example. A break exits the innermo
st block; therefore, it is logical to assume (erroneously, of course) that a
rollback backs out the innermost transaction.
The important thing to remember is that a transaction context either exists
or it doesn't. It doesn't matter how many begin/commit pairs exist in the b
lock of code bounded by the outermost begin/commit pair, the transaction con
text is initiated by the first BEGIN TRANSACTION and is terminated either by
any ROLLBACK or by the outermost COMMIT.
Notice the pretty paragraph breaks in the above :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message news:#7aRUwomFHA.2484@.TK2
MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
>
> I don't know that this particularly means that transactions aren't nested.
> By your definition IF..THEN statements are not really nested. I don't thi
nk
> that nested transactions implies any technical innerworkings more than it
> just simply implies that you can syntactically do:
>
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
>
> It is more or less meaningless to us whether a stack is used or a counter
> and whether or not ROLLBACK kills the whole stack or just goes back to the
> original point. Either way I still consider them nested because of syntax
.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>
>|||I like to think of it as:
"Nested transactions are allowed in syntax but not semantics."
I don't know what "real" nested transactions mean, or if there is a formal d
efinition of what nested
transaction semantics means? :
Perhaps a rollback of an inner transaction would allow commit of an outer tr
ansaction? We can do
that with savepoints.
Or the other way: A commit of an inner transaction will still be committed i
f the outer transaction
does a rollback? True, we don't have this in SQL server (which, I believe, B
rian wished for in an
earlier post in some other thread). To some extent, we can work around it wi
th table variables or
opening a new connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%237aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
> I don't know that this particularly means that transactions aren't nested.
By your definition
> IF..THEN statements are not really nested. I don't think that nested tran
sactions implies any
> technical innerworkings more than it just simply implies that you can synt
actically do:
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> It is more or less meaningless to us whether a stack is used or a counter
and whether or not
> ROLLBACK kills the whole stack or just goes back to the original point. E
ither way I still
> consider them nested because of syntax.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>|||But the one break can get us out of several scope operators. I see your
point, but as long as you have to commit them one at a time, I think the
common term nested for transactions is probably going to stick...
And you code still looks pretty good in plain text :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:u9My2XpmFHA.4028@.TK2MSFTNGP10.phx.gbl...
There is a difference. For example:
IF cond1
THEN IF cond2
THEN IF cond3
THEN stmt1
ELSE stmt2
Which condition does this ELSE go with? Here's another example:
for (int i = 1; i < 10; i++)
for (int j = 1; j < 10; j++)
for (int k = 1; k < 10; k++)
{
Console.WriteLine(i + j + k);
if (i + j + k == 55) break;
}
Which for does the break go with?
One of the common misunderstandings about transactions is that since there
must always be a matching commit transaction for every begin transaction,
and that transactions can be "nested," many newbies erroneously assume that
a rollback only backs out the innermost block of statements--that is, to the
point of the innermost BEGIN TRANSACTION. The assumption follows from the
pattern etched in their brain by the second example. A break exits the
innermost block; therefore, it is logical to assume (erroneously, of course)
that a rollback backs out the innermost transaction.
The important thing to remember is that a transaction context either exists
or it doesn't. It doesn't matter how many begin/commit pairs exist in the
block of code bounded by the outermost begin/commit pair, the transaction
context is initiated by the first BEGIN TRANSACTION and is terminated either
by any ROLLBACK or by the outermost COMMIT.
Notice the pretty paragraph breaks in the above :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:#7aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
> I don't know that this particularly means that transactions aren't nested.
> By your definition IF..THEN statements are not really nested. I don't
> think
> that nested transactions implies any technical innerworkings more than it
> just simply implies that you can syntactically do:
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> It is more or less meaningless to us whether a stack is used or a counter
> and whether or not ROLLBACK kills the whole stack or just goes back to the
> original point. Either way I still consider them nested because of
> syntax.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>|||No doubt we could use a more convienient model for how transactions work,
but your statement is great.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uDRWzWqmFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I like to think of it as:
> "Nested transactions are allowed in syntax but not semantics."
>
> I don't know what "real" nested transactions mean, or if there is a formal
> definition of what nested transaction semantics means? :
> Perhaps a rollback of an inner transaction would allow commit of an outer
> transaction? We can do that with savepoints.
> Or the other way: A commit of an inner transaction will still be committed
> if the outer transaction does a rollback? True, we don't have this in SQL
> server (which, I believe, Brian wished for in an earlier post in some
> other thread). To some extent, we can work around it with table variables
> or opening a new connection.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%237aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
>