SET XACT_ABORT ON

最新推荐文章于 2021-12-20 08:34:25 发布
原创 最新推荐文章于 2021-12-20 08:34:25 发布 · 551 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

Syntax

SET XACT_ABORT { ON | OFF}



Remarks

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions.

When ANSI_WARNINGS=OFF, permissions violations cause transactions to abort.

The setting of SET XACT_ABORT is set at execute or run time and not at parse time.

目前理解：

set xact_abort on, 事务失败all将rollback，

set xact_abort off,事务失败只有失败的才会rollback.





ImYourMan
关注
SQLSERVER事务和XACT_ABORT专题
12-01
该PPT详述XACT_ABORT为ON和OFF的情况，讲解事务和批操作的区别
SQL事务设置SET XACT_ABORT后正确回滚
01-19
SET XACT_ABORT { ON | OFF } 　　当 SET XACT_ABORT 为 ON 时，如果 Transact-SQL 语句产生运行时错误，整个事务将终止并回滚。为 OFF 时，只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。编译错误（如语法错误）不受 SET XACT_ABORT 的影响。 　　对于大多数 OLE DB 提供程序（包括 SQL Server），隐性或显式事务中的数据修改语句必须将 XACT_ABORT 设置为 ON。不需要该选项的情况是提供程序支持嵌套事务时。有关更多信息，请参见分布式查询和分布式事务。 　　SET XACT_
SET XACT_ABORT各种用法及显示结果
weixin_34399060的博客
01-12 350
默认行为 　　默认为SET XACT_ABORT OFF，没有事务行为。 SET XACT_ABORT ON 　　SET XACT_ABORT ON分为两种： 　　1、总体作为一个事务，整体提交或整体回滚，格式为： SET XACT_ABORT ONBEGIN TRAN --要执行的语句COMMIT TRANGO 　　2、每个语句作为一个事务，事务在错误行终止，错误行回滚...
sql server：SET XACT_ABORT ON
一只小黄毛
07-06 2261
SET  XACT_ABORT  ON指當Transact-SQL語句產生運行錯誤時，SQL Server 是否自動回滾當前事務。語法SET XACT_ABORT{ON|OFF}当 SET  XACT_ABORT 为 ON 时，如果 Transact-SQL 语句产生运行时错误，整个事务将终止并回滚。为 OFF 时，只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。编译错误（...
SET XACT_ABORT { ON | OFF }
MrWang
11-06 7822
SET XACT_ABORT { ON | OFF } 2011-05-23 09:40 语法 SET XACT_ABORT { ON | OFF } 注释 当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。 当 SET X
数据库事物用法 SET XACT_ABORT ON
fengchao1000的专栏
07-05 8161
数据库事物的用法和作用就不再重复，写一下在实战当中遇到的问题，代码如下： begin tran --开始执行事务      --语句一     update  set acount = acount-100 where person = 1    --语句二     update  set acount = acount+100 where person = 2    --语句三
SET XACT_ABORT ON 数据库事务
weixin_34255793的博客
07-15 153
转载:http://www.cnblogs.com/rob0121/articles/2320932.html SET XACT_ABORT ON 　　SET XACT_ABORT ON分为两种： 　　1、总体作为一个事务，整体提交或整体回滚，格式为： SET XACT_ABORT ONBEGIN TRAN --要执行的语句COMMIT TRANGO 　　2、每个语句作为一...
SET XACT_ABORT ON \SET NOCOUNT ON
weixin_30823227的博客
10-17 447
1:SET XACT_ABORT ON 语法 SET XACT_ABORT { ON | OFF } 注释 当 SET XACT_ABORT 为 ON 时，如果 Transact-SQL 语句产生运行时错误，整个事务将终止并回滚。为 OFF 时，只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。编译错误（如语法错误）不受 SET XACT_ABORT 的影响。 ...
SQL_SET XACT_ABORT ON(事务回滚)
AinUser的博客
03-28 988
存储过程中的 SET XACT_ABORT ON 和事务
热门推荐
Fanbin168的专栏
08-17 1万+
在存储过程中写SET XACT_ABORT ON 有什么用？ SET XACT_ABORT ON是设置事务回滚的！ 当为ON时，如果你存储中的某个地方出了问题，整个事务中的语句都会回滚 为OFF时，只回滚错误的地方 use sales --指定数据库 go alter table T_UserInfoTwo add constraint ck_id check
set XACT_ABORT ON与判断事务的异同
wei2516341237的博客
11-09 652
1.set XACT_ABORT ON 当SET XACT_ABORT为ON时,如果Transact-SQL语句产生运行时错误,整个事务将终止并回滚,为OFF时,只回滚产生错误的Transact-SQL语句,而事务将继续进行处理. 2.判断事务 在sql中用事务的时候一般是这么写 begin tran sql语句 if @@error>0 rollback tran e
SET XACT_ABORT { ON | OFF }SQL Server 是否自动回滚到当前事务
cxzhq2002的杂记
07-31 3550
指定当 Transact-SQL 语句出现运行时错误时，SQL Server 是否自动回滚到当前事务。 Transact-SQL 语法约定语法 SET XACT_ABORT { ON | OFF }备注当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务
SET XACT_ABORT ON是什么？
aex34027的博客
11-06 201
避免自己遗忘，在这里做个笔记： SET XACT_ABORT ON：强制事务回滚，如果不加这句的话事务有可能回滚失败。 转载于:https://www.cnblogs.com/dawenyang/p/7793646.html...
SET XACT_ABORT ON/OFF测试
我想我是海 冬天的大海 心情随风轻摆
12-20 290
IF OBJECT_ID(N't2', N'U') IS NOT NULL DROP TABLE t2; GO IF OBJECT_ID(N't1', N'U') IS NOT NULL DROP TABLE t1; GO CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY); CREATE TABLE t2 (a INT NOT NULL REFERENCES t1(a)); GO INSERT INTO t1 VALUES (1);.
SET XACT_ABORT 的用法
chelen_jak的专栏
05-23 1030
原文 ：http://study.qqcf.com/web/619/167733.htm SET XACT_ABORT       指定当 Transact-SQL 语句产生运行时错误时，Microsoft® SQL Server™ 是否自动回滚当前事务。 语法        SET XACT_ABORT { ON | OFF } 注释         当 SET XACT_A
创建一个临时表，并且执行SP将结果集插入该临时表中，insert into #Table_Forecast EXEC MES.DBO.pro_fcst_bycust_rep_new_ssTH ，SP：MES.DBO.pro_fcst_bycust_rep_new_ssTH中会 SET XACT_ABORT ON;开启事务，提示The operation could not be performed because OLE DB provider "MSOLEDBSQL" for linked server "MES_FIN" was unable to begin a distributed transaction. 但是如果没有 SET XACT_ABORT ON;就会提示OLE DB provider "MSOLEDBSQL" for linked server "MES_ERP" returned message "Cannot start more transactions on this session.". 訊息 7395，層級 16，狀態 2，程序 MES.DBO.pro_fcst_bycust_rep_new_ssTH，行 20 [批次開始行 9] Unable to start a nested transaction for OLE DB provider "MSOLEDBSQL" for linked server "MES_ERP". A nested transaction was required because the XACT_ABORT option was set to OFF.
最新发布
10-17
<think>我们面对的问题是在SQL Server中创建临时表并执行存储过程将结果集插入临时表时，由于存储过程中开启或不开启事务（特别是涉及SET XACT_ABORT ON/OFF）导致的分布式事务和嵌套事务错误。错误信息包括分布式事务无法启动、会话上无法开始更多事务以及由于XACT_ABORT OFF导致需要嵌套事务而无法启动的问题。 ### 问题分析 1. **分布式事务错误**：当存储过程涉及链接服务器（如MES_FIN、MES_ERP）并且尝试在分布式事务中操作时，如果分布式事务协调器（MSDTC）未正确配置或不可用，则会发生此错误。 2. **无法开始更多事务**：这通常是因为在一个会话中已经有一个活动事务，而尝试再开始一个新事务（嵌套事务）时，但SQL Server链接服务器提供程序对嵌套事务的支持有限。 3. **嵌套事务错误**：当`SET XACT_ABORT OFF`时，如果存储过程中有事务并且被嵌套调用，则内部事务会要求嵌套事务，但链接服务器的OLE DB提供程序可能不支持嵌套事务。 ### 解决思路 #### 方法1：避免使用嵌套事务和分布式事务 - **修改存储过程**：如果可能，修改存储过程，使其不开启事务（即移除`BEGIN TRAN`/`COMMIT TRAN`），或者确保在调用存储过程时不会创建嵌套事务。但是，这可能会影响存储过程原有的错误处理机制。 - **使用表变量或临时表暂存数据**：如果存储过程内部有复杂逻辑且需要事务，考虑将存储过程拆分为两部分：一部分在远程服务器上执行，将结果存入临时表，另一部分在本地处理。或者，使用OPENQUERY或OPENROWSET直接获取数据，避免调用存储过程。 #### 方法2：调整事务设置 - **设置`SET XACT_ABORT ON`**：在调用存储过程的代码之前设置`SET XACT_ABORT ON`。这样，当发生运行时错误时，整个事务会自动回滚，并且不会产生嵌套事务。这是因为`SET XACT_ABORT ON`会导致当前事务在错误时终止并回滚，从而避免嵌套事务的需要。 ```sql SET XACT_ABORT ON; EXEC YourStoredProcedure; ``` 注意：这可能会改变原有的事务行为，需要测试。 #### 方法3：配置MSDTC（分布式事务协调器） 对于分布式事务错误，需要确保MSDTC已正确配置： 1. 在服务器上启用MSDTC服务（设置为自动启动并确保正在运行）。 2. 配置MSDTC安全设置：允许网络访问、允许入站和出站事务等。 3. 在防火墙中为MSDTC添加例外（端口135以及动态端口范围）。 #### 方法4：避免跨链接服务器的事务 - **使用`OPENQUERY`或`OPENROWSET`**：将存储过程的执行改为直接查询，避免在分布式事务中调用存储过程。 例如，将： ```sql INSERT INTO #temp EXEC LinkedServer.Database.dbo.YourStoredProcedure; ``` 改为： ```sql INSERT INTO #temp SELECT * FROM OPENQUERY(LinkedServer, 'EXEC Database.dbo.YourStoredProcedure'); ``` 但是，`OPENQUERY`执行存储过程时，如果存储过程内部有事务，同样可能引发问题。因此，这种方法可能需要配合存储过程的修改（如移除事务）。 #### 方法5：修改存储过程，使其不返回结果集，而是写入远程临时表 - 在存储过程中，将结果集写入一个远程临时表（或永久表），然后在本地读取这个表。这样，存储过程内部的事务不会与本地事务嵌套。 步骤： 1. 在存储过程中，将结果插入到一个全局临时表（如`##GlobalTemp`）或一个永久表中（注意并发问题，可使用唯一标识）。 2. 修改调用方：在调用存储过程后，从该全局临时表或永久表中读取数据。 3. 注意清理临时表。 ### 具体解决方案建议 鉴于错误信息同时涉及嵌套事务和分布式事务，推荐以下步骤： 1. **优先尝试设置`SET XACT_ABORT ON`**： ```sql SET XACT_ABORT ON; -- 创建临时表 CREATE TABLE #temp (...); -- 执行存储过程 INSERT INTO #temp EXEC [LinkedServer].[Database].[dbo].[StoredProcedure]; ``` 这可以避免嵌套事务的产生，因为当`XACT_ABORT`为`ON`时，存储过程内部即使有事务也不会创建嵌套事务（而是加入现有事务），且在错误时整个事务会回滚。 2. **如果上述方法无效，尝试避免使用`INSERT...EXEC`**： - 使用`OPENQUERY`或`OPENROWSET`来执行存储过程并获取结果，但注意这些方法也可能有事务问题。 - 或者，修改存储过程，使其输出到远程临时表，然后在本地读取。 3. **配置MSDTC**：确保分布式事务协调器已正确配置，特别是在跨服务器操作时。 4. **检查链接服务器配置**：确认链接服务器的配置是否支持事务。可以尝试在链接服务器上设置`'use remote transaction'`选项： ```sql EXEC sp_serveroption 'LinkedServerName', 'use remote transaction', 'true'; ``` 这样，当本地事务存在时，对链接服务器的操作会自动成为分布式事务，但前提是MSDTC已配置好。 5. **将存储过程拆解**：如果可能，将存储过程的逻辑抽取出来，改为直接在本地执行查询，避免跨服务器调用存储过程。 ### 注意事项 - 修改存储过程可能涉及业务逻辑，需要谨慎。 - 分布式事务配置可能需要协调服务器管理员。 - 测试时注意事务的一致性和错误处理。 ### 示例代码 假设我们有一个存储过程`usp_GetData`在链接服务器`MES_ERP`上，我们尝试将结果插入到临时表。 **方案1：使用SET XACT_ABORT ON** ```sql SET XACT_ABORT ON; BEGIN TRY CREATE TABLE #temp (ID INT, Data VARCHAR(100)); INSERT INTO #temp EXEC [MES_ERP].[DBName].[dbo].[usp_GetData]; SELECT * FROM #temp; END TRY BEGIN CATCH -- 错误处理 SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH ``` **方案2：使用OPENQUERY（如果存储过程不返回多个结果集）** ```sql SELECT * INTO #temp FROM OPENQUERY(MES_ERP, 'EXEC DBName.dbo.usp_GetData'); ``` **方案3：远程临时表** 修改存储过程`usp_GetData`，使其将结果写入一个全局临时表`##TempResult`（注意并发问题，可以添加会话ID区分）： ```sql -- 在存储过程中 CREATE TABLE ##TempResult (SessionID INT, ...); INSERT INTO ##TempResult SELECT @@SPID, ... FROM ...; ``` 然后本地读取： ```sql -- 执行远程存储过程 EXEC [MES_ERP].[DBName].[dbo].[usp_GetData]; -- 读取全局临时表（根据当前会话ID或其他标识） SELECT * FROM [MES_ERP].[DBName]..##TempResult WHERE SessionID = @@SPID; -- 清理 EXEC [MES_ERP].[DBName].[dbo].[usp_CleanupTemp] @@SPID; -- 或者直接删除 ``` ### 总结 解决此类问题的关键是避免嵌套事务和分布式事务冲突。优先尝试设置`SET XACT_ABORT ON`，其次考虑改变数据获取方式（如使用OPENQUERY或远程临时表）。同时，确保分布式事务协调器配置正确。如果问题依然存在，可能需要深入分析存储过程内部的事务逻辑。 §§相关问题§§ 1. 如何配置SQL Server的MSDTC以支持分布式事务？ 2. 在SQL Server中，`SET XACT_ABORT ON`和`OFF`有什么区别？ 3. 使用链接服务器时，还有哪些常见的错误及解决方法？ </think>### 解决分布式事务和嵌套事务错误的方案 您在 SQL Server 中遇到的错误主要涉及三个关键问题： 1. **分布式事务启动失败**（MSOLEDBSQL 提供程序无法开始分布式事务） 2. **会话事务超限**（无法在会话上启动更多事务） 3. **嵌套事务冲突**（XACT_ABORT OFF 时无法启动嵌套事务） 以下是针对性的解决方案： --- ### 一、核心解决方案 #### 1. 启用并配置 MSDTC（分布式事务协调器） ```sql -- 步骤1：启动 MSDTC 服务 (Windows 服务) -- 步骤2：执行服务器端配置 EXEC sp_configure 'remote access', 1; RECONFIGURE; EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'remote proc trans', 0; -- 关闭远程事务提升 RECONFIGURE; ``` #### 2. 修改存储过程事务设置 最佳实践是**统一事务控制层级**： ```sql ALTER PROCEDURE YourProcedure AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; -- 关键设置 BEGIN TRY BEGIN TRANSACTION; -- 业务逻辑... COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; THROW; END CATCH END ``` #### 3. 调用存储过程时的优化 ```sql -- 调用方设置 SET XACT_ABORT ON; -- 强制统一事务行为 CREATE TABLE #Temp (...); BEGIN TRY INSERT INTO #Temp EXEC LinkedServer.Database.dbo.YourStoredProcedure; -- 跨服务器调用 END TRY BEGIN CATCH SELECT ERROR_MESSAGE(); -- 错误捕获 END CATCH ``` --- ### 二、进阶优化方案 #### 1. 替代远程存储过程调用 使用 **OPENQUERY** 避免分布式事务： ```sql INSERT INTO #Temp SELECT * FROM OPENQUERY(MES_ERP, 'EXEC Database.dbo.YourStoredProcedure'); ``` #### 2. 链接服务器配置优化 ```sql EXEC sp_serveroption 'MES_ERP', 'remote proc transaction promotion', 'false'; ``` #### 3. 事务隔离层级设置 ```sql SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; INSERT INTO #Temp EXEC YourStoredProcedure; ``` --- ### 三、系统级配置 1. **MSDTC 安全配置**： - 控制面板 → 管理工具 → 组件服务 - 计算机 → 我的电脑 → MSDTC → 安全配置： - ✅ 启用网络 DTC 访问 - ✅ 允许远程客户端 - ✅ 允许入站/出站 - ✅ 不要求认证 2. **防火墙规则**： - 开放 TCP 135 端口（MSDTC 主端口） - 允许动态端口范围（49152-65535） --- ### 四、错误处理规范 ```sql -- 范例：安全的事务嵌套结构 SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION MainTran; EXEC LocalStoredProcedure; -- 本地调用 SAVE TRANSACTION RemoteProcPoint; -- 保存点 BEGIN TRY INSERT INTO #Temp EXEC LinkedServer...; -- 远程调用 END TRY BEGIN CATCH ROLLBACK TRANSACTION RemoteProcPoint; -- 回滚到保存点 -- 记录错误日志 END CATCH COMMIT TRANSACTION MainTran; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; EXEC LogError; -- 错误日志过程 END CATCH ``` --- ### 关键注意事项 1. **XACT_ABORT 一致性**：确保调用方和被调用存储过程使用相同的 `SET XACT_ABORT` 设置（推荐统一设为 `ON`） 2. **事务嵌套深度**：SQL Server 最多允许 32 级嵌套事务，复杂业务需分层管理 3. **链接服务器验证**： ```sql SELECT name, is_remote_proc_transaction_promotion_enabled FROM sys.servers; ``` 确保 `remote_proc_transaction_promotion_enabled = 0` > 根据微软文档，分布式事务错误常由 MSDTC 配置不当引起，而嵌套事务冲突多源于事务控制层级不一致[^1]。建议优先检查 `XACT_ABORT` 的统一性和 MSDTC 服务状态。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值