I have Update transaction that will run for single row at the time. However, my application is multi thread and there might be more than one user at the time trying to access same table. While doing some research seems that the best approach would be to run Insert and Update statements in separate procedures. I'm deciding which stored procedure should be executed and then call procedure with all required parameters. There is also way to catch errors like deadlock or timeout in SQL try/catch block. I'm wondering if this would be necessary in my current code or it's over complicated. Here is example of my current code:
CREATE PROCEDURE [dbo].[UpdateBuilding] @Status BIT = NULL, @Name VARCHAR(50) = NULL, @Code CHAR(2) = NULL, @ActionID UNIQUEIDENTIFIER = NULL AS GO DECLARE @Transaction varchar(20) = 'TransUpdate'; DECLARE @RetryCount INT DECLARE @Success BIT SELECT @RetryCount = 1, @Success = 0 WHILE @RetryCount < = 3 AND @Success = 0 BEGIN TRY BEGIN TRANSACTION @Transaction -- This line is to show you on which execution -- we successfully commit. SELECT CAST (@RetryCount AS VARCHAR(5)) + 'st. Attempt' BEGIN UPDATE dbo.Building SET Status = @Status, Name = @Name, Code = @Code, ActionDt = CURRENT_TIMESTAMP, ActionID = @ActionID OUTPUT INSERTED.Code WHERE Code = @Code; END COMMIT TRANSACTION @Transaction SELECT 'Success!' SELECT @Success = 1 -- To exit the loop END TRY BEGIN CATCH ROLLBACK TRANSACTION @Transaction SELECT ERROR_NUMBER() AS [Error Number], ERROR_MESSAGE() AS [ErrorMessage]; -- Now we check the error number to -- only use retry logic on the errors we -- are able to handle. -- You can set different handlers for different -- errors IF ERROR_NUMBER() IN ( 1204, -- SqlOutOfLocks 1205, -- SqlDeadlockVictim 1222 -- SqlLockRequestTimeout ) BEGIN SET @RetryCount = @RetryCount + 1 -- This delay is to give the blocking -- transaction time to finish. -- So you need to tune according to your -- environment WAITFOR DELAY '00:00:02' END ELSE BEGIN -- If we don't have a handler for current error -- then we throw an exception and abort the loop THROW; END END CATCH END
This solution might be to complex for what I actually deal with but at the same time I would like to catch any deadlocks or other problems that I might face with UPDATE/INSERT transactions. This stored procedure is used for single row insert/update (I only showed update in this question). If anyone have suggestion or better approach for this please let me know.