1

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.

Improve this question
8
  • 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. Commented Aug 23, 2018 at 1:43
  • In that case all this code in try/catch is not necessary? Basically only thing I need is ROLLBACK TRANSACTION. Commented Aug 23, 2018 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. Commented Aug 23, 2018 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. Commented Aug 23, 2018 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. Commented Aug 23, 2018 at 11:03

1 Answer 1

Reset to default
2

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.

Improve this answer
13
  • 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? Commented Aug 24, 2018 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. Commented Aug 24, 2018 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? Commented Aug 24, 2018 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. Commented Aug 24, 2018 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. Commented Aug 24, 2018 at 16:36

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.