August 9, 2012 at 3:50 am
[font="Verdana"]
I have written the following sample scenario, with respect to database NESTED transactions
but keep in mind that the coding is done with the structure of TRY CATCH (exception handling)
Scenario
Transaction 1 -> BEGIN
Transaction 2 -> BEGIN
Transaction 2 -> Error
Transaction 2 -> Rollback only 2
Transaction 1 -> should continue and should commit whatever it has .... (obviously the correct one )
So ... how .. to achieve this .. ??? is there any workaround .. or ... to make both transaction separate rather than nested within!
Many Thanks![/font]
August 9, 2012 at 6:50 am
Here's an example:
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
-- CREATE A TEST TABLE
IF OBJECT_ID('tempdb..#test1') IS NOT NULL
DROP TABLE #test1;
CREATE TABLE #test1 (i INT PRIMARY KEY);
BEGIN TRY
-- START THE FIRST TRANSACTION
BEGIN TRANSACTION;
INSERT INTO #test1
VALUES (1);
-- CREATE A SAVEPOINT
SAVE TRAN Save1;
BEGIN TRY
-- START THE SECOND TRANSACTION
BEGIN TRANSACTION;
INSERT INTO #test1
VALUES (3);
INSERT INTO #test1
VALUES (4);
-- VIOLATES THE PK CONSTRAINT!!!
INSERT INTO #test1
VALUES (1);
IF XACT_STATE() <> 0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
-- WE'RE IGNORING THE ERROR, BUT YOU
-- MIGHT WANT TO DO SOMETHING WITH
-- THE MESSAGE (EG: SAVE TO A LOG TABLE)
IF XACT_STATE() <> 0
BEGIN
-- ROLLBACK TO THE SAVEPOINT
ROLLBACK TRANSACTION Save1;
-- COMMIT THE NESTED TRANSACTION:
-- ROLLING BACK TO A SAVEPOINT DOES NOT
-- DECREMENT @@TRANCOUNT AND THE TRANSACTION
-- MUST BE ENDED WITH A COMMIT COMMAND
IF XACT_STATE() <> 0
COMMIT;
END
END CATCH
-- INSERTS AN ADDITIONAL ROW
INSERT INTO #test1
VALUES (2);
-- COMMIT THE FIRST TRANSACTION
IF XACT_STATE() <> 0
BEGIN
COMMIT TRANSACTION;
END
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
-- THIS ROLLBACK AFFECTS THE WHOLE TRANSACTION STACK
-- DECREMENTS @@TRANCOUNT TO 0
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
RAISERROR (
@ErrorMessage
,@ErrorSeverity
,@ErrorState
)
END CATCH
-- TWO ROWS INSERTED
SELECT *
FROM #test1;
/*
OUTPUT:
i
-----------
1
2
*/
IMHO, nested transactions are pointless and can seriously clutter your code. Usually, I check for an open transaction and I avoid opening a new one. In this particular case, nested transactions are the only way to achieve what you're after.
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply