允許在交易中進入應用程序
我們有一個程序需要在事務中執行一組特定的儲存過程,如果其中一個失敗則失敗……
很簡單,我們有類似的東西:
BEGIN TRY BEGIN TRANSACTION; --run some sprocs EXEC [dbo].[sproc1]; EXEC [dbo].[sproc2]; EXEC [dbo].[sproc3]; COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; -- Re-raise error EXEC [dbo].[reraise_error]; RETURN -1; END CATCH
這些儲存過程是相當長的執行過程。但是,我注意到開發此應用程序的團隊在晚上 10 點執行此作業,並且在此作業之前有一步完全關閉了站點。他們希望確保沒有人編輯任何這些數據,以便在此過程發生時將站點關閉,然後一旦完成,站點就會自動恢復生機。這在過去 3 年裡一直很好,直到我們有一位來自海外的客戶加入我們……他們注意到該網站已關閉(由於此過程)並且現在要求進行更改。
我認為很簡單,我可以禁用網站的關閉並在我的應用程序前端處理編輯。問題是在執行上述事務時數據庫似乎被鎖定,例如,試圖訪問該站點的使用者會得到一個旋轉的輪子,直到該事務完成。
當我執行測試時,如果我註釋掉
BEGIN TRANSACTION
並只執行儲存過程(在事務之外),那麼我不再有任何問題,因為表沒有被鎖定。但我不能這樣做,因為我們希望這個過程要麼完全通過/失敗。如果它失敗了,我們希望整個事務失敗(因為我們有一個回滾事務)。客戶仍然希望網站不要鎖定並繼續此過程,因此我嘗試添加:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
但這並沒有任何幫助,因為在交易完成之前,使用者似乎仍然無法在應用程序中執行任何操作。
我做錯了什麼嗎?我該怎麼做才能不必鎖定應用程序而在事務中繼續此過程?我認為更改隔離級別會幫助我,但我得到了相同的結果。
當我執行測試時,如果我註釋掉 BEGIN TRANSACTION 並只執行儲存過程(在事務之外),那麼我不再有任何問題,因為表沒有被鎖定。
在事務內部,修改鎖在整個事務期間都被持有。如果沒有 BEGIN TRAN,鎖只會保留到每個語句結束。仍然會有阻塞,但不會持續很長時間,因此您的站點可能能夠更有效地執行。
客戶仍然希望網站不要鎖定並繼續此過程,因此我嘗試添加:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
但這並沒有任何幫助,因為在交易完成之前,使用者似乎仍然無法在應用程序中執行任何操作。
數據修改查詢不是這樣工作的。查看有關隔離級別的文件:
選擇事務隔離級別不會影響為保護數據修改而獲取的鎖。無論為該事務設置的隔離級別如何,事務總是在其修改的任何數據上獲得排他鎖,並持有該鎖直到事務完成。
你真正想要的是相反的——每晚的程序會鎖定,你希望客戶端(網站)能夠繞過它們。但是使用
READ UNCOMMITTED
/NOLOCK
並不是這樣做的理想方式。有一個更好的方法,那就是使用 RCSI
如果網站主要是讀取數據,那麼您可能會成功使用已送出的讀取快照隔離級別 (RCSI)。這將允許網站繼續從數據庫中讀取數據,而不會被每晚程序阻塞。
您可以在此處閱讀有關啟用 RCSI 時要考慮的詳細說明:在 SQL Server 中實現快照或讀取送出的快照隔離:指南