主要是利用事务原子性的特性,即一个事务中的语句全都执行,或全都不执行。

首先创建一个测试表

USE [A1]
GO

/****** Object:  Table [dbo].[_test]    Script Date: 2020-8-18 13:35:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[_test](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[SN] [bigint] NULL,
	[name] [nvarchar](50) NULL,
	[sex] [bit] NULL,
	[birthday] [datetime] NULL,
	[moreinfo] [nvarchar](500) NULL
) ON [PRIMARY]

GO

例1,正常执行

insert into _test(sn,name,sex,birthday,moreinfo)
  values(1001,'张三',0,'2009-04-29 00:00:00.000','')
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1002,'李四',1,'2012-11-05 00:00:00.000','')
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1003,'王五',1,'20111-05-06 00:00:00.000','')

执行结果:

sql server 数据回滚函数 sqlserver数据库回滚怎么实现_数据库事务

即使最后一条语句出现错误(日期格式不匹配),但前两条依旧执行。

如果使用数据库事务

begin transaction t1
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1001,'张三',0,'2009-04-29 00:00:00.000','')
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1002,'李四',1,'2012-11-05 00:00:00.000','')
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1003,'王五',1,'20111-05-06 00:00:00.000','')
  commit transaction t1

则与运行结果为

sql server 数据回滚函数 sqlserver数据库回滚怎么实现_数据库事务_02

全都没有执行。

 

但各种类型的错误也有不同的表现

以上是数据格式不匹配的问题。不利用事务执行时,问题语句前的语句会正常执行,问题语句后的语句不会执行。利用事务执行后,则所有语句都不会执行。

但如果是类似的语句错误(insert目标数与值数不匹配)。如

insert into _test(sn,name,sex,birthday,moreinfo)
  values(1001,'张三',0,'2009-04-29 00:00:00.000','')
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1002,'李四','r','2012-11-05 00:00:00.000','',11)  
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1003,'王五',1,'2011-05-06 00:00:00.000','')

无论是否使用事务,整个语句都不会执行。

 

这又涉及到数据库错误级别的相关问题,并没有找到很确切详细的资料。仅发现以下几类情况以在应用中注意

1.更新失败不算作错误

【begin transaction t1 】
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1001,'张三',0,'2009-04-29 00:00:00.000','')
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1002,'李四','1','2012-11-05 00:00:00.000','')  
  update _test set moreinfo='维修记录' where sn = '10011'
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1003,'王五',1,'2011-05-06 00:00:00.000','')
  【commit transaction t1】

虽然更新语句无执行目标;但无论是否使用事务都能够正常执行。

 

2.字段错误级别较高,如果存在字段错误。无论在哪类语句(insert,update,delete)、是否使用事务,都会直接报错,所有语句都不执行。

【begin transaction t1】
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1001,'张三',0,'2009-04-29 00:00:00.000','')
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1002,'李四','1','2012-11-05 00:00:00.000','')  
  update _test set moreinfo='维修记录' where snSS = '10011'
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1003,'王五',1,'2011-05-06 00:00:00.000','')
  【commit transaction t1】


  【begin transaction t1】
  insert into _test(sn,nMame,sex,birthday,moreinfo)
  values(1001,'张三',0,'2009-04-29 00:00:00.000','')
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1002,'李四','1','2012-11-05 00:00:00.000','')  
  update _test set moreinfo='维修记录' where sn = '10011'
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1003,'王五',1,'2011-05-06 00:00:00.000','')
  【commit transaction t1】

 

3.表名错误,这里问题比较奇怪

正常执行

insert into _test(sn,name,sex,birthday,moreinfo)
  values(1001,'张三',0,'2009-04-29 00:00:00.000','')
  insert into _test11(sn,name,sex,birthday,moreinfo)
  values(1002,'李四','1','2012-11-05 00:00:00.000','')  
  update _test set moreinfo='维修记录' where sn = '10011'
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1003,'王五',1,'2011-05-06 00:00:00.000','')

结果

sql server 数据回滚函数 sqlserver数据库回滚怎么实现_数据库事务_03

仅问题语句前的语句执行了。

但使用了数据库事务

begin transaction t1   
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1001,'张三',0,'2009-04-29 00:00:00.000','')
  insert into _test11(sn,name,sex,birthday,moreinfo)
  values(1002,'李四','1','2012-11-05 00:00:00.000','')  
  update _test set moreinfo='维修记录' where sn = '10011'
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1003,'王五',1,'2011-05-06 00:00:00.000','')
  【if @@ERROR<>0
    rollback transaction t1
  else】
  commit transaction t1

结果

sql server 数据回滚函数 sqlserver数据库回滚怎么实现_数据库事务_04

数据库事务并没有回滚?!也停留在了问题语句。即使加了问题处理也不行。

寻找问题原因,通过补习,了解到SQL语句默认事务不自动回滚,如果想要自动回滚的话,需要语句设置。如下:

begin transaction t1   
  set xact_abort on
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1001,'张三',0,'2009-04-29 00:00:00.000','')
  insert into _test11(sn,name,sex,birthday,moreinfo)
  values(1002,'李四','1','2012-11-05 00:00:00.000','')  
  update _test set moreinfo='维修记录' where sn = '10011'
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1003,'王五',1,'2011-05-06 00:00:00.000','')
  commit transaction t1

结果

sql server 数据回滚函数 sqlserver数据库回滚怎么实现_数据库事务_02

即使没有错误处理语句,也可以完成回滚。

然而,回到开头的例1,正常情况下默认不回滚的事务回滚了。即使加上不自动回滚的限定语句依旧回滚。

begin transaction t1
  set xact_abort off
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1001,'张三',0,'2009-04-29 00:00:00.000','')
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1002,'李四',1,'2012-11-05 00:00:00.000','')
  insert into _test(sn,name,sex,birthday,moreinfo)
  values(1003,'王五',1,'20111-05-06 00:00:00.000','')
  commit transaction t1

目测此类问题应该与错误的类型与级别相关,但查询很久也没有得到很明确的答案。如有大佬明晰请指一条明路吧!