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,
    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'

                UPDATE dbo.Building
                SET Status = @Status,
                    Name = @Name,
                    Code = @Code,
                    ActionDt = CURRENT_TIMESTAMP,
                    ActionID = @ActionID
                OUTPUT INSERTED.Code  
                WHERE Code = @Code;
            COMMIT TRANSACTION @Transaction

            SELECT 'Success!'
            SELECT @Success = 1 -- To exit the loop

        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
            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'  
            -- If we don't have a handler for current error
            -- then we throw an exception and abort the loop

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.

  • You can't catch command timeouts in T-SQL. Command timeouts occur on the client side where the client API cancels the query when it's impatient, waiting up to the specified CommandTimeout (30 seconds by default with most APIs). No subsequent statements, including the CATCH block, will execute. – Dan Guzman Aug 23 '18 at 1:43
  • In that case all this code in try/catch is not necessary? Basically only thing I need is ROLLBACK TRANSACTION. – espresso_coffee Aug 23 '18 at 1:59
  • With a single UPDATE statement, you don't need the BEGIN TRAN at all since the statement will autocommit by default and any errors will be returned to the client. The TRY/CATCH is needed only if you need to handle specific errors (besides client timeouts) in the proc and take appropriate action. I would not expect deadlocks (1205) with single-row insert/updates, the 1222 error will occur only if you set a not-default LOCK_TIMEOUT on the server side, and 1204 should not happen on a healthy server. – Dan Guzman Aug 23 '18 at 10:00
  • Is your actual use-case a conditional INSERT/UPDATE proc, where you want to insert the row if it doesn't exist and update the existing row if it does? In that case, I would use a single proc with either a MERGE or conditional INSERT/UPDATE in a transaction, and use locking hints to serialize access to the single row. – Dan Guzman Aug 23 '18 at 10:07
  • @DanGuzman your first answer indicates that I won't need Begin Tran and I'm assuming that is the case if table has PK correct? In that case won't be possible to insert duplicates. Your second answer, yes I have tried to do this in single transaction with MERGE but after researching around seems that is not a good option. Using single statement is much faster and cause less problems. If you have any suggestions please let me know. – espresso_coffee Aug 23 '18 at 11:03

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;

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

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.

  • Dan, for Insert transaction should I use try/catch to identify PK violation or there is something better than that? Also I'm not familiar with auto commit, is that same as begin trans or something different? Can you please provide example for that situation. Also does that mean that code above does not need try/catch block? – espresso_coffee Aug 24 '18 at 12:28
  • There is no value with TRY/CATCH for the PK violation since the error will be returned to the client by default without additional T-SQL code, where it can be handled in the app as desired. A CATCH block is only needed when you need to perform additional actions, like rollback an explict transaction. Autocommit simply means that each statement is automatically committed when successful and automatically rolled back after errors. – Dan Guzman Aug 24 '18 at 12:30
  • So if I want to handle this situation with K violation maybe I just leave the code without try/catch block and my server side language should handle that error? Previously discussed that means that you example above does not need try/catch block as well. Also is it good idea to keep try / catch block and insert error in table? – espresso_coffee Aug 24 '18 at 12:32
  • An explict transaction in a proc is appropriate only when there multiple statements in the proc. In that case, the pattern I suggest is a TRY block with BEGIN TRAN followed by the DML statements, and a final COMMIT. The CATCH block would be IF @@TRANCOUNT > 0 ROLLBACK;THROW;. As in my example, no TRY/CATCH is needed because either unexpected errors as well as the RAISERRROR will be returned to the client app. – Dan Guzman Aug 24 '18 at 12:36
  • 1
    @espresso_coffee, the rowversion column is needed for the optimistic concurrency check, The IDENTITY column value will remain unchanged after the initial insert whereas the rowversion will change every time the row is updated. – Dan Guzman Aug 24 '18 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.