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.
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.
This will raise a compiled type error and keep transaction open which you can get details using the following script,-- 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.
USE masterYou 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,
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
*/
-- Altering first stored procedure hereIt 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.
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
No comments:
Post a Comment
Please Use Good Leanguage