Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Is a doomed transaction inevitable if deadlock occurs in a trigger (SQL Server 2008 R2)? Expand / Collapse
Author
Message
Posted Saturday, March 2, 2013 1:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:03 AM
Points: 10, Visits: 159
Here is sample SQL code for my SProc:
DECLARE @retry INT = 3;
DECLARE @waitForTime CHAR(8) = '00:00:01' -- 1 SECOND
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @TranCount int = @@trancount;
DECLARE @TranName varchar(32)

BEGIN TRY
WHILE @retry > 0
BEGIN
BEGIN TRY
IF @TranCount > 0
BEGIN
SET @TranName = 'CS' + CAST( NEWID() AS VARCHAR(36)) -- create almost unique save tran name
SAVE TRANSACTION @TranName;
END
ELSE
BEGIN TRANSACTION;

/******************************************************************************
-- INSERT / Update data...from which we occassionally get 1205 deadlocks errors
******************************************************************************/
IF @TranCount = 0
COMMIT TRANSACTION;

SET @retry = 0;

END TRY
BEGIN CATCH
DECLARE @ErrorNumber INT= ERROR_NUMBER();
DECLARE @XACTState INT= XACT_STATE();

SET @ErrorMessage = ERROR_MESSAGE(); -- since this is inner catch only use sql error
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();

IF @XACTState <> -1 --If the transaction is still valid then roll back to the savepoint
AND @TranCount > 0 -- if we started a savepoint and this is deadlock
ROLLBACK TRANSACTION @TranName;
ELSE
IF @@TRANCOUNT > 0 -- we started the transaction, so roll it back or it was doomed transaction
ROLLBACK TRANSACTION;

IF ((@TranCount > 0 AND @XACTState <> -1) OR @TranCount = 0)
AND @ErrorNumber = 1205 -- we can retry if the transaction was not doomed and it was a deadlock error
BEGIN
-- If Deadlock Error, reduce @retry count for next retry
SET @retry = @retry - 1;
PRINT 'Deadlock detected, retrying update: ' + @ErrorMessage
WAITFOR DELAY @waitForTime; -- 1 second
END
ELSE
BEGIN
-- If a different error exit retry WHILE Loop
SET @retry = -1;

RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);

END

END CATCH;

END; -- End WHILE loop.

-- some more code to deal with return values

RETURN 0
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION -- Rollback Tran

SELECT
@ErrorMessage = ERROR_MESSAGE() + ' Error occurred in ''' + ERROR_PROCEDURE() + ''' at line #' + CAST(ERROR_LINE() AS varchar(30)) + '.',
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
RETURN -1
END CATCH


GO




Post #1425894
Posted Friday, March 8, 2013 8:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:03 AM
Points: 10, Visits: 159
I'm not exactly sure what you are looking to do, it would be helpful if you would provide some DDL to have a look .


DECLARE @retry INT = 3;
DECLARE @waitForTime CHAR(8) = '00:00:01' -- 1 SECOND
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @TranCount int = @@trancount;
DECLARE @TranName varchar(32)

BEGIN TRY
WHILE @retry > 0
BEGIN
BEGIN TRY
IF @TranCount > 0
BEGIN
SET @TranName = 'CS' + CAST( NEWID() AS VARCHAR(36)) -- create almost unique save tran name
SAVE TRANSACTION @TranName;
END
ELSE
BEGIN TRANSACTION;

/******************************************************************************
-- INSERT / Update data...from which we occassionally get 1205 deadlocks errors
******************************************************************************/
IF @TranCount = 0
COMMIT TRANSACTION;

SET @retry = 0;

END TRY
BEGIN CATCH
DECLARE @ErrorNumber INT= ERROR_NUMBER();
DECLARE @XACTState INT= XACT_STATE();

SET @ErrorMessage = ERROR_MESSAGE(); -- since this is inner catch only use sql error
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();

IF @XACTState <> -1 --If the transaction is still valid then roll back to the savepoint
AND @TranCount > 0 -- if we started a savepoint and this is deadlock
ROLLBACK TRANSACTION @TranName;
ELSE
IF @@TRANCOUNT > 0 -- we started the transaction, so roll it back or it was doomed transaction
ROLLBACK TRANSACTION;

IF ((@TranCount > 0 AND @XACTState <> -1) OR @TranCount = 0)
AND @ErrorNumber = 1205 -- we can retry if the transaction was not doomed and it was a deadlock error
BEGIN
-- If Deadlock Error, reduce @retry count for next retry
SET @retry = @retry - 1;
PRINT 'Deadlock detected, retrying update: ' + @ErrorMessage
WAITFOR DELAY @waitForTime; -- 1 second
END
ELSE
BEGIN
-- If a different error exit retry WHILE Loop
SET @retry = -1;

RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);

END

END CATCH;

END; -- End WHILE loop.

-- some more code to deal with return values

RETURN 0
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION -- Rollback Tran

SELECT
@ErrorMessage = ERROR_MESSAGE() + ' Error occurred in ''' + ERROR_PROCEDURE() + ''' at line #' + CAST(ERROR_LINE() AS varchar(30)) + '.',
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
RETURN -1
END CATCH


GO


Post #1428595
Posted Sunday, March 10, 2013 7:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:12 AM
Points: 115, Visits: 279
Maybe you should take a look at Service Broker architecture if deadlocks are a big problem? All changes will be applied sequentially?
Alternatively try to understand why they occur.
Post #1428981
Posted Sunday, March 10, 2013 5:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:03 AM
Points: 10, Visits: 159
siggemannen (3/10/2013)
Maybe you should take a look at Service Broker architecture if deadlocks are a big problem? All changes will be applied sequentially?
Alternatively try to understand why they occur.
we cannot use server broker because the transaction is expected to be completed immediately; the UI queries the results right after the data is saved
Post #1429033
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse