sql server中嵌套事務*

来源:https://www.cnblogs.com/Richard2014/archive/2018/11/09/9935984.html
-Advertisement-
Play Games

轉自 https://www.cnblogs.com/guanjie20/archive/2013/02/17/2914488.html 我們在寫事務時經常遇到的問題如下： 消息 266，級別 16，狀態 2，過程 sp1，第 0 行 EXECUTE 後的事務計數指示 BEGIN 和 COMMIT  ...


轉自 https://www.cnblogs.com/guanjie20/archive/2013/02/17/2914488.html

我們在寫事務時經常遇到的問題如下：

消息 266，級別 16，狀態 2，過程 sp1，第 0 行 EXECUTE 後的事務計數指示 BEGIN 和 COMMIT 語句的數目不匹配。上一計數 = 1，當前計數 = 0。 消息 3903，級別 16，狀態 1，過程 sp2，第 15 行 ROLLBACK TRANSACTION 請求沒有對應的 BEGIN TRANSACTION。  

如果這隻是一個單獨的事務引起的，那麼很好解決，我們只要檢查下是否遺漏了匹配的BEGIN tran 和 COMMIT tran即可，但是如果2個存儲過程都是用事務寫的，那麼就即使每個存儲過程的事務寫法都正常，也會報這個錯誤，

這是因為只要子事務里有回滾語句：如ROLLBACK      那麼全局的@@TRANCOUNT被直接置為0了，導致父事務提交時發現 @@TRANCOUNT=0  報錯 ，sql server會認為當前不存在任何事務，在父存儲過程中任何的COMMIT TRAN或

ROLLBACK 語句都會找不到它對應的 BEGIN TRAN   

下麵我們用一個實例來看下：

假設有一張表，ID為非自增主鍵

USE [TestDB]
GO

/****** Object:  Table [dbo].[test]    Script Date: 02/17/2013 15:44:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[test](
    [ID] [bigint] NOT NULL,
    [UserID] [bigint] NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

我們常規的寫一個插入的子存儲過程如下：

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[innertranv1]    Script Date: 02/17/2013 15:46:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--內層事務存儲過程，演示如何處理才能在嵌套的事務存儲過程中正確處理事務
ALTER PROCEDURE [dbo].[innertranv1]
    @ID BIGINT ,
    @UserID BIGINT ,
    @Name VARCHAR(50)
AS 
    BEGIN
        SET XACT_ABORT ON    
        BEGIN TRAN    
     
        IF(EXISTS(SELECT TOP 1 * FROM dbo.test WHERE ID=@ID))    
        BEGIN
                ROLLBACK        
                RETURN 0 ;  
        END
        
        --業務邏輯開始
        
        INSERT  dbo.test
                ( ID, UserID, Name)
        VALUES  ( @ID, 
                  @UserID,
                  @Name  
                  )
        --業務邏輯結束
        
        IF @@error <> 0 
            BEGIN  
                ROLLBACK                                   
                RETURN 0;  
            END        
  
        COMMIT   
　　　　 SET XACT_ABORT OFF;   
　　　　 RETURN 1 ; 

  END

調用的父存儲過程如下：

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[outertranv2]    Script Date: 02/17/2013 16:09:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <外層存儲過程>
-- =============================================
ALTER PROCEDURE [dbo].[outertranv2]
    @ID BIGINT ,
    @UserID BIGINT ,
    @Name VARCHAR(50)
AS 
        
    BEGIN TRAN     
    DECLARE @result INT
    EXEC @result = innertranv1 @ID =@ID, @UserID =@UserID, @Name = @Name
    IF ( @result <= 0 ) 
        BEGIN
            ROLLBACK TRAN  ;                 
            RETURN ;
        END 
    COMMIT TRAN

我們執行父存儲過程：

USE [TestDB]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[outertranv2]
        @ID = 0,
        @UserID = 0,
        @Name = N'0'

SELECT    'Return Value' = @return_value

GO

第一次提交正常，再次執行就會出現如下錯誤：

消息 266，級別 16，狀態 2，過程 innertranv1，第 0 行
EXECUTE 後的事務計數指示 BEGIN 和 COMMIT 語句的數目不匹配。上一計數 = 1，當前計數 = 0。
消息 3903，級別 16，狀態 1，過程 outertranv2，第 18 行
ROLLBACK TRANSACTION 請求沒有對應的 BEGIN TRANSACTION。

如何解決？我們修改子存儲過程如下：

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[innertran]    Script Date: 02/17/2013 16:26:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--內層事務存儲過程，演示如何處理才能在嵌套的事務存儲過程中正確處理事務
ALTER PROCEDURE [dbo].[innertran]
    @ID BIGINT ,
    @UserID BIGINT ,
    @Name VARCHAR(50)
AS 
    BEGIN
        DECLARE @TRANCOUNT int=(select @@TRANCOUNT)
    
        SET XACT_ABORT ON
        SET @TRANCOUNT=(select @@TRANCOUNT)
        PRINT '未進入子事務前全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))    
        BEGIN TRAN tran1      --開始事務 
        SAVE TRAN tranpoint   --保存事務點
        SET @TRANCOUNT=(select @@TRANCOUNT)
        PRINT '進入子事務後全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))    
       
        IF(EXISTS(SELECT TOP 1 * FROM dbo.test WHERE ID=@ID))    
        BEGIN
                ROLLBACK TRAN tranpoint ;   --回滾保存點的事務   
                COMMIT TRAN tran1 ;            --提示當前事務
                SET @TRANCOUNT=(select @@TRANCOUNT)  
                PRINT '回滾子事務後全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))     
                          
                RETURN 0 ;  
        END
        
        --業務邏輯開始
        
        INSERT  dbo.test
                ( ID, UserID, Name)
        VALUES  ( @ID, 
                  @UserID,
                  @Name  
                  )
        --業務邏輯結束
        
        IF @@error <> 0 
            BEGIN  
                ROLLBACK TRAN tranpoint ; --回滾保存點的事務   
                COMMIT TRAN tran1 ;          --提示當前事務  
                SET @TRANCOUNT=(select @@TRANCOUNT)  
                PRINT '回滾子事務後全局@@TRANCOUNTT'+CAST(@TRANCOUNT AS VARCHAR(50))     
                                         
                RETURN 0;  
            END        
  
        COMMIT TRAN tran1 ;    
        SET XACT_ABORT OFF;    
        SET @TRANCOUNT=(select @@TRANCOUNT)  
        PRINT '提交子事務後全局@@TRANCOUNT'+CAST(@TRANCOUNT AS VARCHAR(50))
           
        RETURN 1 ;

    END

父過程如下： 

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[outertran]    Script Date: 02/17/2013 16:27:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <外層存儲過程>
-- =============================================
ALTER PROCEDURE [dbo].[outertran]
    @ID BIGINT,
    @UserID BIGINT,
    @Name VARCHAR(50)
AS 
    DECLARE @TRANCOUNT int=(select @@TRANCOUNT)
    PRINT '未進入父事務前全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50))
    
        
    BEGIN TRAN 
    SET @TRANCOUNT=(select @@TRANCOUNT) 
    PRINT '進入父事務後全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50))    
        
    DECLARE @result INT

    EXEC @result = innertran @ID = @ID, @UserID = @UserID, @Name =@Name

    IF ( @result <= 0 ) 
        BEGIN
            ROLLBACK TRAN  ;
            SET @TRANCOUNT=(select @@TRANCOUNT) 
            PRINT '回滾父事務後全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50)) 
                        
            RETURN ;
        END 
    COMMIT TRAN 
    SET @TRANCOUNT=(select @@TRANCOUNT) 
    PRINT '提交父事務後全局@@TRANCOUNT:'+CAST(@TRANCOUNT AS VARCHAR(50))

調用父存儲過程：

USE [TestDB]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[outertran]
        @ID = 0,
        @UserID = 0,
        @Name = N'0'

SELECT    'Return Value' = @return_value

GO

結果如下：

未進入父事務前全局@@TRANCOUNT:0
進入父事務後全局@@TRANCOUNT:1
未進入子事務前全局@@TRANCOUNT:1
進入子事務後全局@@TRANCOUNT:2
回滾子事務後全局@@TRANCOUNT:1
回滾父事務後全局@@TRANCOUNT:0

不會再報"EXECUTE 後的事務計數指示 BEGIN 和 COMMIT 語句的數目不匹配"之類的錯誤了，實際上就是在每個嵌套的子過程中標明當前事務點，每個子事務 只提交/回滾 子事務點，而不是回滾整個事務！

實際開發中還是會出現事務錯亂的情況，如在try...catch...中


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

-Advertisement-
Play Games
更多相關文章
  • MPP架構海量數據分析倉庫——Greenplum介紹
    一、Greenplum背景 時間回到2002年，互聯網行業經過近10年的發展，數據量正處於快速增長期： 1、傳統的主機計算模式在海量數據面前，除了造價昂貴外，在CPU計算和IO吞吐上不能滿足海量數據的計算需求； 2、傳統資料庫大多基於SMP架，縱向擴容(scale-up)模式遇到了瓶頸。 3、分散式 ...
  • Ubuntu 安裝hive + mysql
    先安裝mysql 1. 通過tar -zxvf ....hive.tar.gz -C /soft 解壓安裝hive 3. cp hive-exec-log4j.properties.template hive-exec-log4j.properties 顯示日誌 ...
  • MySQL資料庫實驗：任務一 創建資料庫和表
    任務一 創建資料庫和表 @[toc] |班級| 姓名 | | | | | 軟體工程16 9班 |洪燕妮 | 【實訓目的與要求】 1、安裝MySQL系統，瞭解MySQL環境； 2、利用MySQL命令行視窗創建資料庫及表； 3、利用界面工具創建資料庫及表； 4、完整性約束設計。 【實訓原理】 DBMS環 ...
  • mysql-8.0.13在windows上的部署
    1 、下載mysql-8.0.13-x64 官方網站：https://dev.mysql.com/downloads/mysql/ 2、解壓到G盤 3、準備my.ini文件保存到解壓目錄 4、‘保存以下代碼成.bat腳本文件 5、cd到mysql解壓目錄，執行以上的.bat腳本，執行完以上步驟控制台 ...
  • mysql安裝出現問題（The service already exists）
    1.管理員身份運行cmd（系統win10） 2.輸入命令cd /d F:\mysql-5.7.19-win32\bin（此為mysql要安裝的目錄） 3.輸入安裝命令mysqld install 出現問題The service already exists 這是由於之前已經安裝過mysql並且沒有刪 ...
  • Redis數據類型基本操作
    String類型: 設置鍵值對: set key value 設置鍵值對和過期時間：setex key seconds value ( 以秒為單位 ) 設置多個鍵值對: mset key1 value1 key2 value2 給已有鍵的值追加: append key value 獲取指定鍵的值: ...
  • mysql 架構篇系列 4 複製架構一主一從搭建(半同步複製)
    一.概述 在mysql 5.5之前，mysql 的複製是非同步操作，主庫和從庫的數據之間存在一定的延時，這樣存在一個隱患：當主庫上寫入一個事務並提交成功，而從庫尚未得到主庫推送的Binlog日誌時，主庫down機了，事務Binlog丟失了，此時從庫就缺失了這個事務，從而造成主從不一致。 為瞭解決這個問 ...
  • Left Jion等價SQL猜想驗證
    驗證結果： 寫作時間：2017-11-8 本文只代表本人的見解，可能存在錯誤，僅用於技術交流。如果你喜歡該文，可以掃下麵的二維碼打賞我（打賞敬請備註“博客園打賞”五字）。 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類