SQL Server 在生產環境中這樣寫存儲過程的坑都避免了嗎？

来源:http://www.cnblogs.com/chenmh/archive/2017/11/20/7856777.html
-Advertisement-
Play Games

概述 最近因為業務的需求寫了一段時間存儲過程，發現之前寫的存儲過程存在一些不嚴謹的地方，特別是TRY...CATCH中嵌套事務的寫法；雖然之前寫的並沒有錯，但是還是埋藏著很大的隱患在裡面。希望這篇文章能給大家一些參考；文章內容有點長還望耐心閱讀。 1.插入測試數據 創建表 DROP TABLE sc ...


概述  

 最近因為業務的需求寫了一段時間存儲過程，發現之前寫的存儲過程存在一些不嚴謹的地方，特別是TRY...CATCH中嵌套事務的寫法；雖然之前寫的並沒有錯，但是還是埋藏著很大的隱患在裡面。希望這篇文章能給大家一些參考；文章內容有點長還望耐心閱讀。

 

 

1.插入測試數據

----創建表
DROP TABLE score
GO
CREATE TABLE [dbo].[score](
    id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50) NOT NULL,
    score INT NOT NULL CHECK (score>=0),
    months INT NOT NULL,
    createtime DATETIME NOT NULL DEFAULT GETDATE()
)

---根據姓名月份查詢分數
CREATE INDEX IX_score_name ON score(name,months) include(score)
---根據月份查詢最高分數
CREATE INDEX IX_score_months ON score(months) include(name,score)
---創建姓名和月份組合的唯一索引
CREATE UNIQUE INDEX IX_score_months_name ON score(months,name)

------插入測試數據
TRUNCATE TABLE score

INSERT INTO score(name,score,months) 
VALUES('li',50,10),('chen',70,10),('zhang',80,10),('wang',90,10),('li',50,11),('chen',70,11),('zhang',80,11),('wang',90,11)

SELECT * FROM score;

2.THROW

THROW是在2012版本中引入的，在有些場景當中，應用程式端不做一些合法性的驗證，這些驗證會被放在資料庫端來驗證。當資料庫端驗證輸入的信息不合法時需要主動拋出異常來中斷代碼的執行。

THROW既可以接收錯誤信息拋錯提示，同時也可以手動拋出錯誤到CATCH中。語法如下：

;THROW

THROW [ { error_number | @local_variable }, 

        { message | @local_variable }, 

        { state | @local_variable } ]  

[ ; ]

參數

error_number
表示異常的常量或變數。 error_number是int並且必須為大於或等於 50000 且小於或等於 2147483647，如果CATCH中使用RAISERROR來接收錯誤信息那麼指定的error_number必須在sys.messages 中存在；如果使用CATCH來接收則不需要。

消息
描述異常的字元串或變數。 消息是nvarchar(2048)。

狀態
在 0 到 255 之間的常量或變數，指示與消息關聯的狀態。 狀態是tinyint。

註意：

1.THROW代碼前必須要用分號，因為THROW會中斷代碼的執行，所以如果將THROW放在CATCH中時必須放在ROLLBACK TRAN之後，否則不會回滾事務導致對象一直處於提交狀態被鎖。

2.THROW放CATCH中可以達到RAISERROR一樣的效果，同時還簡便了代碼。

3. THROW能返回正確的錯誤代碼行號，而RAISERROR沒辦法

參考：https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/throw-transact-sql

3.sp_addmessage

自定義錯誤號

EXEC sp_addmessage  
    @msgnum = 60000,  
    @severity = 16,  
    @msgtext = N'Manual cast wrong ',
    @lang = 'us_english'; 

EXEC sp_addmessage  
    @msgnum = 60000,  
    @severity = 16,  
    @msgtext = N'手動拋錯',  
    @lang = '簡體中文';

註意：自定義錯誤號必須大於50000

二、調用存儲過程

1.查詢存儲過程

----查詢存儲過程
CREATE PROCEDURE  Pro_score
(@Option VARCHAR(50),
 @name VARCHAR(50)='',
 @months INT=''
)
AS
BEGIN

---查詢指定人分數
IF @Option='GetScore'
   BEGIN
        SELECT name,
               score
        FROM score
        WHERE name=@name

   END

----查詢指定月份最高分數
IF @Option='MonthMaxScore'
   BEGIN
        SELECT Top 1 
             name,
             score
        FROM score
        WHERE months=@months
        ORDER BY score

   END



END

調用存儲過程：

EXEC Pro_score @Option='GetScore',@name='li'
EXEC Pro_score @Option='MonthMaxScore',@months=11

3.修改存儲過程

 1 CREATE PROCEDURE [dbo].[Pro_Insert_score]
 2 (@Option VARCHAR(50),
 3  @name VARCHAR(50)='',
 4  @months INT=0,
 5  @score INT=0
 6 ) 
 7 AS
 8 BEGIN
 9 DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000);
10 IF @Option='InsertScore'
11     BEGIN
12     
13              -----使用事務
14                   BEGIN TRY  
15                         BEGIN TRAN 
16                         INSERT INTO score(name,score,months) 
17                         VALUES(@name,@score,@months)
18                     
19                         ----插入重覆值報錯事務回滾
20                         INSERT INTO score(name,score,months) 
21                         VALUES(@name,@score,@months)
22 
23                         COMMIT TRAN  
24         
25                     END TRY  
26                     BEGIN CATCH 
27                          SELECT  @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
28                          RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) ;
29                         ROLLBACK TRAN
30                         ;THROW
31                         ----執行失敗
32                         RETURN 1
33                     END CATCH
34 
35         ----執行成功
36         RETURN 0
37     END
38 
39 END

調用存儲過程

----調用存儲過程2
DECLARE @status INT
EXEC @status=Pro_Insert_score @Option='InsertScore',@name='chen',@months=12,@score=90
SELECT @status

可以發現使用RAISERROR拋錯出來的行號和消息號都是錯誤的，50000這個消息號其實是不存在的，它是保留的一個統一的消息號。

可以通過查詢sys.message查詢對應的消息號

SELECT * FROM score WHERE name='chen'
SELECT * FROM sys.messages WHERE message_id=2601 and language_id=2052

 4.手動拋錯中斷

手動拋錯也是這篇文章主要要講的一個知識點，在有一些業務場景當中有一些驗證操作需要在資料庫中進行，甚至必須在更新之後進行但是又是正常的提交操作，在這種情況下就需要手動進行驗證是否需要執行下麵的代碼。，見過很多程式員寫存儲過程喜歡在每一個判斷的地方加上RETURN操作，目的是為了不執行後面的代碼，同時又在RETURN前加上ROLLBACK操作。這雖然是一個辦法，但是在事務中運用RETURN是一個很危險的操作，弄不好會導致事務一直處於打開操作導致表一直被鎖住，在生成環境是很危險的操作。

建議使用THROW來手動進行拋錯，THROW拋錯會相當於觸發一個11-19級別的錯誤，這樣會跳到CATCH中做ROLLBACK操作。

註意：THROW前必須以分號開頭，如果THROW前有代碼以分號結尾也可以。

CREATE PROCEDURE [dbo].[Pro_score_throw]
(@Option VARCHAR(50),
 @name VARCHAR(50)='',
 @months INT=0,
 @score INT=0
) 
AS
BEGIN
DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000);
IF @Option='UpdateScore'
    BEGIN
    
             -----使用事務
                  BEGIN TRY  
                        BEGIN TRAN 
                        UPDATE score
                        SET score=score+@score
                        WHERE name=@name AND months=@months

                        ----在有些業務場景有些判斷必須等操作完了才能去做判斷是否能繼續執行下去
                        IF (SELECT score FROM score WHERE name=@name AND months=@months)>100
                        BEGIN
                             
                             ;THROW 60000,'分數不能大於100',111 

                        END
                        COMMIT TRAN  
        
                    END TRY  
                    BEGIN CATCH 
             
                     ROLLBACK TRAN
                        ;THROW
                    END CATCH

        ----執行成功
        RETURN 0
    END

END

調用存儲過程

DECLARE @status INT
EXEC @status=Pro_score_throw @Option='UpdateScore',@name='chen',@months=10,@score=40
SELECT @status

5.存儲過程調用存儲過程

CREATE PROCEDURE [dbo].[Pro_score_ProcToProc]
(@Option VARCHAR(50),
 @name VARCHAR(50)='',
 @months INT=0,
 @score INT=0
) 
AS
BEGIN
DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000);
    IF @Option='Update'
    BEGIN
             ----判斷修改的人是否存在
             IF NOT EXISTS(SELECT * FROM score WHERE name=@name)
             BEGIN
                 ---修改人不存在
                 RETURN 2
             END
             ELSE
             BEGIN
             -----使用事務
                  BEGIN TRY  
                        BEGIN TRAN 
                               UPDATE score
                               SET createtime='1900-01-01 00:00:000'
                               WHERE name=@name AND months=@months

                               SELECT name,months,createtime,score FROM score  WHERE name=@name AND months=@months
                               ---定義事務保存點
                               ---SAVE TRAN TRAN1
                               ----調用別的存儲過程
                               EXEC Pro_score_ProcToProc @Option='UpdateScore',@name=@name,@months=@months,@score=@score

                        COMMIT TRAN  
        
                    END TRY  
                    BEGIN CATCH 
                        SELECT name,months,createtime,score FROM score  WHERE name=@name AND months=@months
                        IF @@TRANCOUNT > 0
                        ROLLBACK TRAN ;
                        SELECT name,months,createtime,score FROM score  WHERE name=@name AND months=@months
                        ;THROW
                    END CATCH
             END
        ----執行成功     
         RETURN 0
     END

    IF @Option='UpdateScore'
    BEGIN
    
             ---使用事務
                  BEGIN TRY  
                        BEGIN TRAN 
                        UPDATE score
                        SET score=score+@score
                        WHERE name=@name AND months=@months

                        ----在有些業務場景有些判斷必須等操作完了才能去做判斷是否能繼續執行下去
                        IF (SELECT score FROM score WHERE name=@name AND months=@months)>100
                        BEGIN
                             
                             ;THROW 60000,'分數不能大於100',111 

                        END
                        COMMIT TRAN 
        
                    END TRY  
                    BEGIN CATCH 
                         ----回滾到指定保存點
                         ----ROLLBACK TRAN TRAN1

                         --回滾事務
                         ROLLBACK TRAN
                        ----執行失敗
                        ;THROW
                    END CATCH

    END
END

存儲過程調用存儲過程事務的三種處理方法:

1.內部存儲過程不要包含事務，因為內部ROLLBACK會直接回滾到外部的BEGIN TRAN導致外部的ROLLBACK沒有對應的COMMIT;

2.還有一種方法是在調用內部存儲過程之前使用保存點“SAVE TRAN TRAN1”,同時內部存儲過程的ROLLBACK TRAN必須指定事務保存點，例如“ROLLBACK TRAN TRAN1”,這樣內部存儲過程回滾就只會回滾到保持點.

3.在外部存儲過程的CATCH塊的ROLLBACK前加上IF @@TRANCOUNT > 0判斷條件

 

事務嵌套事務的理解

---事務1
BEGIN TRAN
        ---事務2
        BEGIN TRAN

        COMMIT TRAN /ROLLBACK TRAN 

COMMIT TRAN /ROLLBACK TRAN

對於事務嵌套事務，事務2的ROLLBACK操作會直接回滾到事務1的BEGIN TRAN，會導致事務1的ROLLBACK沒有對應的BEGIN TRAN。處理方法可以在調用事務2之前定義一個事務保存點或者在事務1的ROLLBACK前加上IF @@TRANCOUNT > 0判斷條件是否存在事務需要回滾。

SET XACT_ABORT ON

並不是所有的錯誤都能被CATCH所接收。對於嚴重級別為0-10（信息性消息）和20-25（致命的消息）是不能被CATCH所接收的，這時如果在事務中遇到了這類的報錯那麼通用會導致事務處理打開狀態，這時就需要開啟XACT_ABORT。當開啟XACT_ABORT後只要代碼中存在報錯就會執行回滾操作，而不管錯誤的級別。例如：

CREATE TABLE [dbo].[AA](
    [id] [int] NULL
) ON [PRIMARY]
GO
CREATE PROC Pro_bb
(@Option VARCHAR(50))
AS
BEGIN
IF @OPTION='a'
   BEGIN
       TRUNCATE TABLE AA;
       SELECT * FROM AA;
        ----事務1
        BEGIN TRY  
        BEGIN TRAN  
            INSERT INTO AA SELECT 2  
            SELECT * FROM AA;
            INSERT INTO #BB SELECT 1 
            COMMIT TRAN;  
        END TRY  
        BEGIN CATCH  
            IF @@TRANCOUNT > 0
            ROLLBACK TRAN;  
            ;THROW
        END CATCH 
    END
END

由於臨時表#BB不存在，導致插入報錯，但是嚴重級別又小於11導致CATCH接收不到錯誤，這時查看發現事務處於打開狀態，而且表AA也被鎖住。

EXEC Pro_bb @OPTION='a';
DBCC OPENTRAN;

加上事務前加上 SET XACT_ABORT ON  

ALTER TABLE [dbo].[AA](
    [id] [int] NULL
) ON [PRIMARY]
GO
CREATE PROC Pro_bb
(@Option VARCHAR(50))
AS
BEGIN
IF @OPTION='a'
   BEGIN
   SET XACT_ABORT ON  
       TRUNCATE TABLE AA;
       SELECT * FROM AA;
        ----事務1
        BEGIN TRY  
        BEGIN TRAN  
            INSERT INTO AA SELECT 2  
            SELECT * FROM AA;
            INSERT INTO #BB SELECT 1 
            COMMIT TRAN;  
        END TRY  
        BEGIN CATCH  
            IF @@TRANCOUNT > 0
            ROLLBACK TRAN;  
            ;THROW
        END CATCH 
    END
END

再次執行

EXEC Pro_bb @OPTION='a';
DBCC OPENTRAN;

沒有處於打開的事務而且事務也執行了回滾操作。

總結

1.建議2012以後版本所有的接收拋錯改成使用THROW，不要使用THROW拋錯又使用RAISERROR來介紹錯誤，在事務嵌套事務的寫法中如果內部事務使用RAISERROR來接收THROW返回的報錯不會執行後面的ROLLBACK。

2.建議在ROLLBACK前統一加上IF @@TRANCOUNT > 0判斷條件，這樣可以避免因為內部的ROLLBACK回滾或者RETURN操作導致ROLLBACK沒有對應的COMMIT。

3.建議不要在事務內使用RETURN返回代碼錯誤位置，RETURN會跳出事務導致提示ROLLBACK沒有對應的COMMIT,嚴重的會導致事務一直處於打開不提交，THROW也可以指定錯誤位置。

 

 

 

 

備註：

    作者：pursuer.chen

    博客：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明鏈接，否則保留追究責任的權利。

《歡迎交流討論》

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • web前端經典小題
    1.此題涉及的知識點眾多，包括變數定義提升、this指針指向、運算符優先順序、原型、繼承、全局變數污染、對象屬性及原型屬性優先順序等等。 function Foo() { getName = function () { console.log(1) }; return this; } Foo.getNa ...
  • 一些重要的學習資料
    正則表達式.zip http://pan.baidu.com/s/1dDmE0uP用AngularJS開發下一代Web應用.zip http://pan.baidu.com/s/1eQfkMMA掌握Ajax全書CHM版.zip http://pan.baidu.com/s/1sjOPBNv微軟Jav ...
  • 通過nodejs將less文件轉為css文件
    已經安裝過nodejs 1，安裝less； dos界面下進入node.js安裝目錄，通過命令npm install less –g 全局進行安裝less.（安裝過程可能需要等待一段時間） 2、先在控制台編譯一遍：lessc 文件路徑\文件名.less（可省略）； 3、在dos界面輸入：文件路徑\文件 ...
  • nodejs文件運行
    電腦中已經安裝nodejs。 cmd進入dos界面，輸入文件路徑；然後輸入>node 文件名.js ...
  • 隨機產生20個單詞
    隨機產生20個單詞 一、問題來源： 老師給了一份專業單詞word，說第二天要全背下來。錯了就五十遍啊五十遍。 然後，有人提出要做一個產生隨機單詞的Demo，來測試自己。 老師表示呵呵，做出來的就可以不用聽寫。 頓時，我就表示，是可忍，孰不可忍啊。這是在侮辱我們啊。這票我幹了，不能讓人看低了。我這麼做 ...
  • 來個Button看一看
    每次寫代碼總會忘記一些東西，又要重新Goooooooooogle，好煩吶~ ...
  • Android系統的三種分屏顯示模式
    本文介紹了Android 7系統原生支持的多視窗分屏顯示及VR系統的兩種分屏顯示模式。 ...
  • protobuf/android 交叉編譯筆記
    protobuf 交叉編譯筆記 目標是使用 android ndk 的工具鏈編譯出 android armeabi v7a 可用的 protobuf 庫。 交叉編譯環境配置 windows 平臺 1. 下載 "NDK x86_64" 或者 "NDK x86" 並解壓縮 2. 下載 "protobuf ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類