Thursday, 4 June 2015

Example of SET XACT_ABORT ON in SQL Server by serverku

We have seen one error The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION and seen workaround too. There is related to transaction mismatch and now I am writing further same with different error and an issue.

In that post, if I remove one column of used tables in those SPs. What will happen? It will raise an error and keep the transaction open. Let us check.
-- Creating table which will be used in SPs.
CREATE TABLE tbl_Tran
(
TranId INT NOT NULL PRIMARY KEY
,TranName VARCHAR(10)
)

GO

-- Altering first stored procedure here
CREATE PROCEDURE Firttranproc
AS
BEGIN
SET NOCOUNT ON

-- Here we have specified Tran1 as transaction name

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO tbl_Tran
(TranId
,TranName)

SELECT
1
,'Tran-1'
UNION ALL
SELECT
1
,'Tran-1'

COMMIT TRANSACTION
END TRY

BEGIN CATCH
PRINT 'Rollback Tran1'

-- This statement first check open transaction for their session
-- If found then will rollback it.
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
END

GO

-- Altering second stored procedure here
CREATE PROCEDURE Secondtranproc
AS
BEGIN
SET NOCOUNT ON

BEGIN TRY
BEGIN TRANSACTION

-- Inserting records
INSERT INTO tbl_Tran
(TranId
,TranName)
SELECT
2
,'Tran-2'

-- Calling first stored procedure here
EXEC Firttranproc

COMMIT TRANSACTION
END TRY

BEGIN CATCH
PRINT 'Rollback Tran2'


-- This statement first check open transaction for their session
-- If found then will rollback it.
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
END

GO

-- Executing second stored procedure which will also call SP1
EXEC Secondtranproc
GO

-- Droping one column to raise an complite type error
ALTER TABLE tbl_Tran
DROP COLUMN TranName
GO

-- Executing second stored procedure which will also call SP1
EXEC Secondtranproc
GO
Msg 207, Level 16, State 1, Procedure Secondtranproc, Line 13
Invalid column name 'TranName'.
Msg 266, Level 16, State 2, Procedure Secondtranproc, Line 13
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
This will raise a compiled type error and keep transaction open which you can get details using the following script,
USE master
GO

SELECT
est.session_id as [Session ID],
est.transaction_id as [Transaction ID],
tas.name as [Transaction Name],
tds.database_id as [Database ID]
FROM sys.dm_tran_active_transactions tas
INNER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
INNER JOIN sys.dm_tran_session_transactions est
ON (est.transaction_id=tas.transaction_id)
WHERE est.is_user_transaction = 1 -- user
AND tas.transaction_state = 2 -- active
AND tas.transaction_begin_time IS NOT NULL
GO
/*
Output :

Session ID Transaction ID Transaction Name Database ID
----------- ---------------- ----------------- -----------
54 176426 user_transaction 19

*/
You can see the open transaction details above. For a fox for such issue we should use SET XACT_ABORT ON in the beginning of the stored procedures. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. So let's change stored procedures,
-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
-- Here we have specified Tran1 as transaction name

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO tbl_Tran
(TranId
,TranName)

SELECT
1
,'Tran-1'
UNION ALL
SELECT
1
,'Tran-1'

COMMIT TRANSACTION
END TRY

BEGIN CATCH
PRINT 'Rollback Tran1'

-- This statement first check open transaction for their session
-- If found then will rollback it.
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
END

GO

-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRY
BEGIN TRANSACTION

-- Inserting records
INSERT INTO tbl_Tran
(TranId
,TranName)
SELECT
2
,'Tran-2'

-- Calling first stored procedure here
EXEC Firttranproc

COMMIT TRANSACTION
END TRY

BEGIN CATCH
PRINT 'Rollback Tran2'


-- This statement first check open transaction for their session
-- If found then will rollback it.
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
END

GO

-- Executing second stored procedure which will also call SP1
EXEC Secondtranproc
GO
It will raise a run-time error, but does not keep the transaction open. Please execute stored procedures and check for open transaction using query provided above. This is just what I want to share with you. I would like to put your comments.

No comments:

Post a Comment

Please Use Good Leanguage