防止單行插入/更新事務死鎖和伺服器鎖定的最佳方法？

我有更新事務，當時將執行單行。但是，我的應用程序是多執行緒的，當時可能有多個使用者嘗試訪問同一個表。雖然做一些研究似乎最好的方法是在單獨的過程中執行 Insert 和 Update 語句。我正在決定應該執行哪個儲存過程，然後使用所有必需的參數呼叫過程。還有一種方法可以在 SQL try/catch 塊中擷取死鎖或超時等錯誤。我想知道這在我目前的程式碼中是否有必要或者它過於復雜。這是我目前程式碼的範例：

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

對於我實際處理的問題，這個解決方案可能很複雜，但同時我想捕捉我在 UPDATE/INSERT 事務中可能面臨的任何死鎖或其他問題。這個儲存過程用於單行插入/更新（我只在這個問題中顯示了更新）。如果有人對此有建議或更好的方法，請告訴我。

我建議只處理預期和不可避免的錯誤。最好解決錯誤的根本原因，而不是引入重試解決方法的複雜性。

在這種情況下（通過主鍵進行單例更新），1205 死鎖錯誤不太可能發生，因為只涉及單行。1222 錯誤只會在非預設LOCK_TIMEOUT設置下發生，這裡似乎不是這種情況。1204 錯誤不應發生在健康的伺服器上。

在 T-SQL 中無法擷取命令超時錯誤。當客戶端 API 取消查詢時，客戶端會發生命令超時，等待指定的 CommandTimeout（大多數 API 預設為 30 秒）。取消後不會執行 T-SQL 批處理中的任何後續語句，包括 CATCH 塊。需要考慮的是，在 proc 中啟動的顯式事務將在超時後保持打開狀態（直到應用程序關閉或重用池連接）。出於這個原因，我強烈建議SET XACT_ABORT ON在儲存過程中使用 explict an 進行指定，BEGIN TRANSACTION以便在客戶端超時後立即回滾事務。但是，對於這個單例行更新過程，不需要顯式事務，因為自動送出將保證原子的全或無行為。

您可能希望添加一個樂觀並發檢查，以便使用者不會意外覆蓋彼此的更改。這是否值得努力取決於這種情況的業務影響及其發生的可能性。可以通過將每個列的值與最初檢索的值進行比較或通過向rowversion表中添加列來簡化任務來完成樂觀並發檢查。這是一個使用rowversion列的樂觀並發範例。應用程式碼將在向使用者呈現數據時檢索rowversion並在更新行時提供該值。

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

關於proc，考慮到 2 個字元的主鍵和可能很小的使用者群INSERT，似乎不太可能違反主鍵。Code您可以只使用單個語句儲存過程（自動送出）並在應用程序端使用使用者友好的消息處理 PK 違規錯誤。

引用自：https://dba.stackexchange.com/questions/215628