Best way to prevent deadlocks and server locking for single row Insert/Update transaction?

Question

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.

Answer 1

I suggest one handle only errors that are expected and unavoidable. It would be better to address the root cause of errors rather than introduce the complexity of a retry work-around.

In this case (singleton update by primary key), the 1205 deadlock error is unlikely because only a single row is involved. The 1222 error will occur only with a non-default LOCK_TIMEOUT setting, which doesn't appear to be the case here. The 1204 error should not happen on a healthy server.

Command timeout errors cannot be caught in T-SQL. Command timeouts occur on the client side when the client API cancels the query, waiting up to the specified CommandTimeout (30 seconds by default with most APIs). No subsequent statements in the T-SQL batch will execute after cancelation, including the CATCH block. A consideration is that an explict transaction started in the proc will remain open after a timeout (until the application closes or reuses the pooled connection). For this reason, I strongly recommend one specify SET XACT_ABORT ON in stored procedures with explict an BEGIN TRANSACTION so that transactions are rolled back immediately following a client timeout. There is no need for an explict transaction for this singleton row update proc, though, since autocommit will guaranteed atomic all-or-none behavior.

You might want to add an optimistic concurrency check so that users don't accidentally overwrite one another's changes. Whether or not this is worth the effort depends on the business impact of such a scenario and the likelihood of it happening. An optimistic concurrency check can be accomplished by either comparing each column value with the value originally retrieved or by adding a rowversion column to the table to simplify the task. Here's an optimistic concurrency example using a rowversion column. The app code would retrieve the rowversion when presenting data to the user and provide that value when updating the row.

ALTER TABLE dbo.Building
    ADD RowVersion rowversion NOT NULL;
GO

CREATE PROCEDURE [dbo].[UpdateBuilding] 
    @Status BIT = NULL,
    @Name VARCHAR(50) = NULL,
    @Code CHAR(2) = NULL,
    @ActionID UNIQUEIDENTIFIER = NULL,
    @OriginalRowVersion ROWVERSION
AS
UPDATE dbo.Building
SET Status = @Status,
    Name = @Name,
    Code = @Code,
    ActionDt = SYSDATETIME(),
    ActionID = @ActionID
WHERE
    Code = @Code
    AND RowVersion = @OriginalRowVersion;
IF @@ROWCOUNT = 0
BEGIN
    RAISERROR('Building with code %s was modified or deleted by another user.', 16, 1, @Code);
END;
GO

Regarding the INSERT proc, it seems primary key violations are unlikely given the 2 character Code primary key and presumably small user base. You could just use a single statement stored procedure (autocommit) and handle PK violations errors on the applciation side with a user-freindly message.