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
>