Home > SQL Script > Understand Nested Transaction

Understand Nested Transaction

Nested Begin Transaction and Commit/Rollback Transaction is
very confused. From my memory, rollback transaction within the nested begin transaction
will roll back the nested transaction as well as all parent begin transaction.
However the commit transaction only commit its corresponding transaction and if
there is a rollback outside, the transaction will still rollback unless you use
Save Tran cmd. You can play with the below script and exam the result.

USE [YourDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[test]’) AND type in (N’U’))
DROP TABLE [dbo].[test]
CREATE TABLE [dbo].[test](
    [value] [int] NOT NULL
) ON [PRIMARY]

insert into test values(1)
insert into test values(2)
insert into test values(3)
insert into test values(4)

DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
begin tran
    update test
    set value=111111
    where value=1   
    select @@TRANCOUNT

    IF @@TRANCOUNT >
0       
       SAVE TRANSACTION t1
    else
        BEGIN TRANSACTION

    update test
    set value=222222
    where value=1
rollback tran t1
commit

Advertisements
Categories: SQL Script
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: