SQLSERVER-存储过程-事务-小结

sqlserver 事务

在存储过程中使用事务的时候也遇到些概念性的错误和操作性的错误，特记录下来。
提到事务，一般都知道其是一个单个的工作单元，也就是用户定义的一个操作序列，要么都成功，要么都失败。
事务有一个  隐藏的 XACT_ABORT 设置开关，一般在启用事务(transaction)的时候,其默认值为off
XACT_ABORT 官方给的解释是 “指定当 Transact-SQL 语句出现运行时错误时，SQL Server 是否自动回滚当前事务”
其中
当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。
当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。 如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。 OFF 是默认设置。


第一种：
我在使用事务的时候，犯得第一个错误时，错误理解使用到呢系统给的函数@@error
例如：
create table student
{
  id int PRIMARY KEY IDENTITY(1,1),
  name varchar(10),
  age int
}


create PROCEDURE pro_student_test
as
begin tran
     insert into student(name,age) values('中国人民a',null);
insert into student(name,age) values('中国人民共和国,中国人名共和国',null);  --插入name，字符超过定义大小
     insert into student(name,age) values('中国人民b',null);


if @@error <> 0
       rollback tran
    else
commit tran
end
go


@@error ：返回执行的上一个 Transact-SQL 语句的错误号。如果最后的 Transact-SQL 语句执行成功，则 @@ERROR 系统函数返回 0，其中每一个 Transact-SQL 语句完成时，@@ERROR 的值都会改变。
上面一个存储过程错误点就在于判断的依据是最后一个语句返回的@error，结果是第一条和第三条都插入到数据中，而没有出现当发生错误的时候整个操作系列都回滚的现象
结果
插入1
插入2


第二种情况：如果开启呢 XACT_ABORT 为on 的话


create PROCEDURE pro_student_test
as
SET XACT_ABORT ON
begin tran
     insert into student(name,age) values('中国人民a',null);
insert into student(name,age) values('中国人民共和国,中国人名共和国',null);  --插入name，字符超过定义大小
     insert into student(name,age) values('中国人民b',null);
commint tran
end
go


结果：第二条语句发生错误，整个事务都回滚。
插入结果3
插入结果4


第三种情况：如果没有开启 XACT_ABORT 也就是不写这个属性和显示设置为off的话
create PROCEDURE pro_student_test
as
SET XACT_ABORT off --（不写这条语句或者显示设置成off）
begin tran
     insert into student(name,age) values('中国人民a',null);
insert into student(name,age) values('中国人民共和国,中国人名共和国',null);  --插入name，字符超过定义大小
     insert into student(name,age) values('中国人民b',null);
commint tran
end
go


结果：第二天语句发生错误，第二天语句自身回滚，第一条和第三条都插入到数据库中
结果插入5,
结果插入6


第四种 ：如果没有开启 XACT_ABORT 但结合try catch 进行捕获
create PROCEDURE pro_student_test
as
begin try
begin tran
insert into student(name,age) values('中国人民a',null);
insert into student(name,age) values('中国人民共和国,中国人名共和国',null);  --插入name，字符超过定义大小
insert into student(name,age) values('中国人民b',null);
commint tran
return 1    
end try
begin catch
    rollback tran
    return 0
end catch
end
go
结果：遇到第二天语句发生错误的时候，整个事务都回滚
结果插入7
结果插入8


第二种显示开启 XACT_ABORT 为ON 和使用到呢try catch 的都可以使整个事务发生回滚。
推荐使用第二种，少写代码。