事务异常注意事项

主要点：

TRY...CATCH不会返回对象错误或者字段错误等类型的错误

　当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。

   当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。OFF 是默认设置。

编译错误（如语法错误）不受 SET XACT_ABORT 的影响。

事务的理解

---创建表Table1
IF OBJECT_ID('Table1','U') IS NOT NULL
DROP TABLE Table1
GO
CREATE TABLE Table1
(ID INT NOT NULL PRIMARY KEY,
Age INT NOT NULL CHECK(Age>10 AND Age<50));
GO

---创建表Table2
IF OBJECT_ID('Table2','U') IS NOT NULL
DROP TABLE Table2
GO
CREATE TABLE Table2
(
ID INT NOT NULL)
GO

1.简单的事务提交

BEGIN TRANSACTION
INSERT INTO Table1(ID,Age)
VALUES(1,20)
INSERT INTO Table1(ID,Age)
VALUES(2,5)
INSERT INTO Table1(ID,Age)
VALUES(2,20)
INSERT INTO Table1(ID,Age)
VALUES(3,20)
COMMIT TRANSACTION
GO
---第二条记录没有执行成功,其他的都执行成功
SELECT * FROM Table1
所以并不是事务中的任意一条语句报错整个事务都会回滚，其它的可执行成功的语句依然会执行成功并提交。

2.TRY...CATCH

DELETE FROM Table1

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Table1(ID,Age)
VALUES(1,20)
INSERT INTO Table1(ID,Age)
VALUES(2,20)
INSERT INTO Table1(ID,Age)
VALUES(3,20)
INSERT INTO Table3
VALUES(1) 
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

----重新打开一个回话执行查询，发现由于存在对象出错BEGIN CATCH并没有收到执行报错，且事务一直处于打开状态，没有被提交，也没有执行回滚。
SELECT * FROM Table1

---如果事务已经提交查询XACT_STATE()的状态值是0,或者执行DBCC OPENTRAN
SELECT XACT_STATE()

DBCC OPENTRAN

---手动执行提交或者回滚操作
ROLLBACK TRANSACTION

TRY...CATCH不会返回对象错误或者字段错误等类型的错误

想详细了解TRY...CATCH请参考http://www.cnblogs.com/chenmh/articles/4012506.html

3.打开XACT_ABORT

SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO Table1(ID,Age)
VALUES(1,20)
INSERT INTO Table1(ID,Age)
VALUES(2,20)
INSERT INTO Table1(ID,Age)
VALUES(3,20)
INSERT INTO Table3
VALUES(1) 
COMMIT TRANSACTION
SET XACT_ABORT OFF

---事务全部执行回滚操作(对象table3是不存在报错，但是也回滚所有的提交，跟上面的TRY...CATCH的区别)
SELECT * FROM Table1

---查询是否有打开事务
SELECT XACT_STATE()

DBCC OPENTRAN

未查询到有打开事务

当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。

当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。OFF 是默认设置。

编译错误（如语法错误）不受 SET XACT_ABORT 的影响。

      所以我们应该根据自己的需求选择正确的事务。

转自：http://www.cnblogs.com/chenmh/p/3999475.html