1月23

MSSQL中SET XACT_ABORT ON

Large | Medium | Small
22:50 DBA  From: Original
Reads(3360) | Comments(0) | Trackbacks(0)
SET XACT_ABORT ON时,在事务中,若出现错误,系统即默认回滚事务,但只对非自定义错误有效

SET XACT_ABORT OFF,默认值,在事务中,回滚一个语句还是整个事务视错误的严重程序而定,用户级错误一般不会回滚整个事务
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
The setting of SET XACT_ABORT is set at execute or run time and not at parse(从语法上分析;解析) time.
------------------------------------------------------------------------------

Examples

The following code example causes a foreign key violation error in a transaction that has other Transact-SQL statements. In the first set of statements, the error is generated, but the other statements execute successfully and the transaction is successfully committed. In the second set of statements, SET XACT_ABORT is set to ON. This causes the statement error to terminate the batch and the transaction is rolled back.

USE AdventureWorks2008R2; GO 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); INSERT INTO t1 VALUES (3); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (6); GO SET XACT_ABORT OFF; GO BEGIN TRANSACTION; INSERT INTO t2 VALUES (1); INSERT INTO t2 VALUES (2); -- Foreign key error. INSERT INTO t2 VALUES (3); COMMIT TRANSACTION; GO SET XACT_ABORT ON; GO BEGIN TRANSACTION; INSERT INTO t2 VALUES (4); INSERT INTO t2 VALUES (5); -- Foreign key error. INSERT INTO t2 VALUES (6); COMMIT TRANSACTION; GO -- SELECT shows only keys 1 and 3 added. -- Key 2 insert failed and was rolled back, but -- XACT_ABORT was OFF and rest of transaction -- succeeded. -- Key 5 insert error with XACT_ABORT ON caused -- all of the second transaction to roll back. SELECT *     FROM t2; GO
Add a comment
Emots
emotemotemotemotemotemotemotemotemotemotemotemotemotemotemotemot
Enable HTML
Enable UBB
Enable Emots
Hidden
Remember

Nickname : 

Password(可不填) :  Optional

Site URI (可不填) : 

Email (可不填) :  [Register]