Can you clarify this ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can you clarify this ?

Author

Topic

Asif5566
Starting Member

9 Posts

Posted - 2011-07-06 : 04:56:33

Hello,

Here, test is a table of two column of integer type. Now, this transaction should commit the first INSERT and the UPDATE statement, BUT, as there is a Error in the second INSERT statement in this, this should only commit First Insert and Update but NOT the second INSERT.

However, what i am getting is, if ANY Error occurring inside this transaction, it Rolls back to first BEGIN TRAN Despite, setting 'xact_abort' off.

Can you tell me where is my mistake in understanding the issue ?

Thank You.

-----

set xact_abort off

BEGIN TRAN

INSERT INTO test VALUES (1,1);
UPDATE test SET col1 = 3;
INSERT INTO test VALUES (1,ff);

COMMIT TRAN

---

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-06 : 05:05:44

Why not taking the second insert into its own tran?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-06 : 05:10:08

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

I don't know what are some cases though...

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-07-06 : 05:13:23

You need to set SAVE TRANSACTION points and ROLLBACK to the NAMED SAVED TRANSACTION.

Nested TRANSACTIONS all rollback when you issue a blind ROLLBACK.

Post the code maybe?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-06 : 05:37:07

[code]BEGIN TRY
INSERT dbo.Test
VALUES (1, 1)
END TRY
BEGIN CATCH
END CATCH

BEGIN TRY
UPDATE dbo.Test
SET Col1 = 3;
END TRY
BEGIN CATCH
END CATCH

BEGIN TRY
INSERT dbo.Test
VALUES (1, 'ff')
END TRY
BEGIN CATCH
END CATCH[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-06 : 05:37:37

Autonomous transaction is NOT supported in SQL Server.

N 56°04'39.26"
E 12°55'05.63"

Asif5566
Starting Member

9 Posts

Posted - 2011-07-06 : 09:35:28

quote:
Originally posted by Transact Charlie

You need to set SAVE TRANSACTION points and ROLLBACK to the NAMED SAVED TRANSACTION.

Nested TRANSACTIONS all rollback when you issue a blind ROLLBACK.

Post the code maybe?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Actually I posted the code in my original post, here it is again,

set xact_abort off

BEGIN TRAN

INSERT INTO test VALUES (1,1);
UPDATE test SET col1 = 3;
INSERT INTO test VALUES (1,ff);

COMMIT TRAN

As u can see, i didn't put any nesting BEGIN, I was just wondering as I've alre4ady set xact_abort OFF, then why it was behaving as If i set it ON, in fact the behaviour of this transaction (as described in my first post) is all the same regardless xact_abort is Set ON or OFF.

My actual intent is to understanding the transaction process, it is not part of any production code.

Asif5566
Starting Member

9 Posts

Posted - 2011-07-06 : 09:36:57

quote:
Originally posted by webfred

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

I don't know what are some cases though...

No, you're never too old to Yak'n'Roll if you're too young to die.

I think this is actually what is happening, the behavior of auto Rollback is not guaranteed.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-07-06 : 18:37:10

quote:
Originally posted by Asif5566

quote:
Originally posted by Transact Charlie

You need to set SAVE TRANSACTION points and ROLLBACK to the NAMED SAVED TRANSACTION.

Nested TRANSACTIONS all rollback when you issue a blind ROLLBACK.

Post the code maybe?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Actually I posted the code in my original post, here it is again,

set xact_abort off

BEGIN TRAN

INSERT INTO test VALUES (1,1);
UPDATE test SET col1 = 3;
INSERT INTO test VALUES (1,ff);

COMMIT TRAN

As u can see, i didn't put any nesting BEGIN, I was just wondering as I've alre4ady set xact_abort OFF, then why it was behaving as If i set it ON, in fact the behaviour of this transaction (as described in my first post) is all the same regardless xact_abort is Set ON or OFF.

My actual intent is to understanding the transaction process, it is not part of any production code.

Yeah -- sorry I misread / misunderstood what you were asking. sorry.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources