注意,开始事务前,需要设置set xact_abort on--设置,如果事务中的任何代码出错,则整个事务回滚。 set xact_abort on
貼一個聯機幫助上的例子 範例 本範例在還有其他 Transact-SQL 陳述式的交易中,造成外部索引鍵違反錯誤。在第一組陳述式中會產生錯誤,但是其他的陳述式將成功地執行,交易也會成功地認可。在第二組陳述式中,SET XACT_ABORT 設定變更為 ON,將造成陳述式錯誤,中斷批次及交易復原。 CREATE TABLE t1 (a int PRIMARY KEY) CREATE TABLE t2 (a int 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 TRAN INSERT INTO t2 VALUES (1) INSERT INTO t2 VALUES (2) /* Foreign key error */ INSERT INTO t2 VALUES (3) COMMIT TRAN GOSET XACT_ABORT ON GOBEGIN TRAN INSERT INTO t2 VALUES (4) INSERT INTO t2 VALUES (5) /* Foreign key error */ INSERT INTO t2 VALUES (6) COMMIT TRAN 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 GODROP TABLE t2 DROP TABLE t1 GO
没有回滚,你查询时会提示超时已过期。不过,可以用select * from 表名 with (nolock) 查询到数据
set xact_abort on
範例
本範例在還有其他 Transact-SQL 陳述式的交易中,造成外部索引鍵違反錯誤。在第一組陳述式中會產生錯誤,但是其他的陳述式將成功地執行,交易也會成功地認可。在第二組陳述式中,SET XACT_ABORT 設定變更為 ON,將造成陳述式錯誤,中斷批次及交易復原。 CREATE TABLE t1 (a int PRIMARY KEY)
CREATE TABLE t2 (a int 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 TRAN
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2) /* Foreign key error */
INSERT INTO t2 VALUES (3)
COMMIT TRAN
GOSET XACT_ABORT ON
GOBEGIN TRAN
INSERT INTO t2 VALUES (4)
INSERT INTO t2 VALUES (5) /* Foreign key error */
INSERT INTO t2 VALUES (6)
COMMIT TRAN
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
GODROP TABLE t2
DROP TABLE t1
GO