查看数据库版本:
查看当前XACT_ABORT值
DECLARE @XACT_ABORT VARCHAR(3) = ‘OFF’
IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = ‘ON’
SELECT @XACT_ABORT AS XACT_ABORT
测试开始:
Test表结构
运行:
IF ( (16384 & @@OPTIONS) = 16384 )
PRINT(‘XACT_ABORT = ON’)
INSERT INTO test ( a, b ) VALUES( ‘a’, 0 )
PRINT(4)
INSERT INTO test ( a, b ) VALUES( ‘b’, null)
PRINT(5)
INSERT INTO test ( a, b ) VALUES( ‘c’, 0 )
PRINT(6)
4
Msg 515, Level 16, State 2, Server WIN-JO702AA1QO7, Procedure , Line 0
不能将值 NULL 插入列 ‘b’,表 ‘master.dbo.test’;列不允许有 Null 值。INSERT 失败。
语句已终止。
[23000] [Microsoft][SQL Server Native Client 10.0][SQL Server]不能将值 NULL 插入列 ‘b’,表 ‘master.dbo.test’;列不允许有 Null 值。INSERT 失败。 (515)
[01000] [Microsoft][SQL Server Native Client 10.0][SQL Server]语句已终止。 (3621)
时间: 0.007s
提示:很奇怪, XACT_ABORT是OFF状态。中间一步报错,前后数据都成功插入了,但是打印数据只打到4,5和6都没有打印,不知道啥原因,难道是因为sql的错误提示信息影响了打印输出功能?print换成插入数据到log表试试。
SET XACT_ABORT OFF
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, NULL )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
Msg 515, Level 16, State 2, Server WIN-JO702AA1QO7, Procedure , Line 0
不能将值 NULL 插入列 ‘b’,表 ‘master.dbo.test’;列不允许有 Null 值。INSERT 失败。
语句已终止。
[23000] [Microsoft][SQL Server Native Client 10.0][SQL Server]不能将值 NULL 插入列 ‘b’,表 ‘master.dbo.test’;列不允许有 Null 值。INSERT 失败。 (515)
[01000] [Microsoft][SQL Server Native Client 10.0][SQL Server]语句已终止。 (3621)
时间: 0.022s
提示:NULL值插入错误这样的类型,不会中断执行过程
SET XACT_ABORT OFF
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, ‘sssss’ )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
Msg 245, Level 16, State 1, Server WIN-JO702AA1QO7, Procedure , Line 0
在将 varchar 值 ‘sssss’ 转换成数据类型 bit 时失败。
[22018] [Microsoft][SQL Server Native Client 10.0][SQL Server]在将 varchar 值 ‘sssss’ 转换成数据类型 bit 时失败。 (245)
时间: 0.007s
提示:解释过程错误这样的类型,会中断执行过程
打开XACT_ABORT试试:
SET XACT_ABORT ON
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, NULL )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
Msg 515, Level 16, State 2, Server WIN-JO702AA1QO7, Procedure , Line 0
不能将值 NULL 插入列 ‘b’,表 ‘master.dbo.test’;列不允许有 Null 值。INSERT 失败。
[23000] [Microsoft][SQL Server Native Client 10.0][SQL Server]不能将值 NULL 插入列 ‘b’,表 ‘master.dbo.test’;列不允许有 Null 值。INSERT 失败。 (515)
时间: 0.007s
SET XACT_ABORT ON
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, ‘sssss’ )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
Msg 245, Level 16, State 1, Server WIN-JO702AA1QO7, Procedure , Line 0
在将 varchar 值 ‘sssss’ 转换成数据类型 bit 时失败。
[22018] [Microsoft][SQL Server Native Client 10.0][SQL Server]在将 varchar 值 ‘sssss’ 转换成数据类型 bit 时失败。 (245)
时间: 0.007s
提示:打开XACT_ABORT,错误都回中断执行过程
SET XACT_ABORT OFF
BEGIN TRY
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, NULL )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
END TRY
BEGIN CATCH
INSERT INTO log( mark ) VALUES(‘5’)
END CATCH
INSERT INTO log( mark ) VALUES(‘6’)
Affected rows: 1Affected rows: 1Affected rows: 1Affected rows: 0Affected rows: 1Affected rows: 1
时间: 0.01s
SET XACT_ABORT OFF
BEGIN TRY
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, ‘sssss’ )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
END TRY
BEGIN CATCH
INSERT INTO log( mark ) VALUES(‘5’)
END CATCH
INSERT INTO log( mark ) VALUES(‘6’)
Affected rows: 1Affected rows: 1Affected rows: 1Affected rows: 0Affected rows: 1Affected rows: 1
时间: 0.009s
SET XACT_ABORT ON
BEGIN TRY
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, NULL )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
END TRY
BEGIN CATCH
INSERT INTO log( mark ) VALUES(‘5’)
END CATCH
INSERT INTO log( mark ) VALUES(‘6’)
Affected rows: 1Affected rows: 1Affected rows: 1Affected rows: 0Affected rows: 1Affected rows: 1
时间: 0.009s
SET XACT_ABORT ON
BEGIN TRY
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, ‘sssss’ )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
END TRY
BEGIN CATCH
INSERT INTO log( mark ) VALUES(‘5’)
END CATCH
INSERT INTO log( mark ) VALUES(‘6’)
Affected rows: 1Affected rows: 1Affected rows: 1Affected rows: 0Affected rows: 1Affected rows: 1
时间: 0.008s
提示:使用Try以后,不管发生什么类型的错误,不管XACT_ABORT ON还是OFF,都会被catch捕获,转而执行catch后的代码。
SET XACT_ABORT OFF
BEGIN TRAN
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, NULL )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
COMMIT TRAN
Msg 515, Level 16, State 2, Server WIN-JO702AA1QO7, Procedure , Line 0
不能将值 NULL 插入列 ‘b’,表 ‘master.dbo.test’;列不允许有 Null 值。INSERT 失败。
语句已终止。
[23000] [Microsoft][SQL Server Native Client 10.0][SQL Server]不能将值 NULL 插入列 ‘b’,表 ‘master.dbo.test’;列不允许有 Null 值。INSERT 失败。 (515)
[01000] [Microsoft][SQL Server Native Client 10.0][SQL Server]语句已终止。 (3621)
时间: 0.004s
SET XACT_ABORT OFF
BEGIN TRAN
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, ‘sssssss’ )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
COMMIT TRAN
Msg 245, Level 16, State 1, Server WIN-JO702AA1QO7, Procedure , Line 0
在将 varchar 值 ‘sssssss’ 转换成数据类型 bit 时失败。
[22018] [Microsoft][SQL Server Native Client 10.0][SQL Server]在将 varchar 值 ‘sssssss’ 转换成数据类型 bit 时失败。 (245)
时间: 0.005s
SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, NULL )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
COMMIT TRAN
Msg 515, Level 16, State 2, Server WIN-JO702AA1QO7, Procedure , Line 0
不能将值 NULL 插入列 ‘b’,表 ‘master.dbo.test’;列不允许有 Null 值。INSERT 失败。
[23000] [Microsoft][SQL Server Native Client 10.0][SQL Server]不能将值 NULL 插入列 ‘b’,表 ‘master.dbo.test’;列不允许有 Null 值。INSERT 失败。 (515)
时间: 0.002s
SET XACT_ABORT OFF
BEGIN TRY
BEGIN TRAN
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, NULL )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
COMMIT TRAN
END TRY
BEGIN CATCH
INSERT INTO log( mark ) VALUES(‘5’)
END CATCH
INSERT INTO log( mark ) VALUES(‘6’)
Affected rows: 1Affected rows: 1Affected rows: 1Affected rows: 0Affected rows: 1Affected rows: 1
时间: 0.002s
(阻塞了)
ROLLBACK后直接全是空,如果COMMIT以后
SET XACT_ABORT OFF
BEGIN TRY
BEGIN TRAN
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, ‘ssssss’ )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
COMMIT TRAN
END TRY
BEGIN CATCH
INSERT INTO log( mark ) VALUES(‘5’)
END CATCH
INSERT INTO log( mark ) VALUES(‘6’)
Msg 3930, Level 16, State 1, Server WIN-JO702AA1QO7, Procedure , Line 0
当前事务无法提交,而且无法支持写入日志文件的操作。请回滚该事务。
[42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]当前事务无法提交,而且无法支持写入日志文件的操作。请回滚该事务。 (3930)
时间: 0.024s
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, NULL )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
COMMIT TRAN
END TRY
BEGIN CATCH
INSERT INTO log( mark ) VALUES(‘5’)
END CATCH
INSERT INTO log( mark ) VALUES(‘6’)
Msg 3930, Level 16, State 1, Server WIN-JO702AA1QO7, Procedure , Line 0
当前事务无法提交,而且无法支持写入日志文件的操作。请回滚该事务。
[42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]当前事务无法提交,而且无法支持写入日志文件的操作。请回滚该事务。 (3930)
时间: 0.002s
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
INSERT INTO log( mark ) VALUES(‘1’)
INSERT INTO test( a, b ) VALUES( ‘a’, 0 )
INSERT INTO log( mark ) VALUES(‘2’)
INSERT INTO test( a, b ) VALUES( ‘b’, ‘ssssss’ )
INSERT INTO log( mark ) VALUES(‘3’)
INSERT INTO test( a, b ) VALUES( ‘c’, 0 )
INSERT INTO log( mark ) VALUES(‘4’)
COMMIT TRAN
END TRY
BEGIN CATCH
INSERT INTO log( mark ) VALUES(‘5’)
END CATCH
INSERT INTO log( mark ) VALUES(‘6’)
Msg 3930, Level 16, State 1, Server WIN-JO702AA1QO7, Procedure , Line 0
当前事务无法提交,而且无法支持写入日志文件的操作。请回滚该事务。
[42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]当前事务无法提交,而且无法支持写入日志文件的操作。请回滚该事务。 (3930)
时间: 0.002s
DECLARE @i INT=0
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
INSERT INTO test ( a, b ) VALUES( ‘a’, 0 )
INSERT INTO test ( a, b ) VALUES( ‘b’, null)
INSERT INTO test ( a, b ) VALUES( ‘c’, 0 )
COMMIT TRAN
END TRY
BEGIN CATCH
SET @i=1
ROLLBACK
PRINT @i
END CATCH
1
Affected rows: 1Affected rows: 0Affected rows: 0
时间: 0.001s
终端没有报错信息,回滚前的变量赋值会保留,并且rollback后的程序会继续执行下去
DECLARE @i INT=0
SET XACT_ABORT OFF
PRINT(1)
BEGIN TRY
PRINT(2)
BEGIN TRAN
PRINT(3)
INSERT INTO test ( a, b ) VALUES( ‘a’, 0 )
PRINT(4)
INSERT INTO test ( a, b ) VALUES( ‘b’, null)
PRINT(5)
INSERT INTO test ( a, b ) VALUES( ‘c’, 0 )
PRINT(6)
COMMIT TRAN
PRINT(7)
END TRY
–PRINT(8)
BEGIN CATCH
PRINT(9)
SET @i=1111
PRINT(10)
ROLLBACK
PRINT(11)
PRINT @i
PRINT(12)
END CATCH
PRINT(13)
1
2
3
4
9
10
11
1111
12
13
Affected rows: 0Affected rows: 0Affected rows: 0Affected rows: 1Affected rows: 0Affected rows: 0Affected rows: 0Affected rows: 0Affected rows: 0Affected rows: 0Affected rows: 0Affected rows: 0
时间: 0.002s