Server-side transaction management

Server-side transaction management.sql

create proc dbo.MyProc
as
begin
    set xact_abort on
    begin try
        begin tran
            /* Some logic here  */
        commit
    end try
    begin catch
        if @@TRANCOUNT > 0 -- Transaction is active
            rollback;
        /* Optional error-handling  code */
        throw;
    end catch;
end;

Using TRY..CATCH block to retry the operation in case of deadlock.sql

DECLARE @retry TINYINT = 5
-- Keep trying to update table if this task is selected as the deadlock victim.
WHILE (@retry > 0)
BEGIN
    BEGIN TRY
    	BEGIN TRAN
    	-- some code that can lead to the deadlock
    	COMMIT
    END TRY
    BEGIN CATCH
    	-- Check error number. If deadlock victim error, then reduce retry count
    	-- for next update retry. If some other error occurred, then exit WHILE loop.
    	IF (ERROR_NUMBER() = 1205)
    	    SET @retry = @retry - 1;
    	ELSE
    	SET @retry = 0;
    	IF @@trancount > 0
    	    ROLLBACK;
    END CATCH
END

from Expert SQL Server Transactions and Locking
APress. ISBN-13: 978-1484239568 ISBN-10: 1484239563
Written by Dmitri V. Korotkevitch