主要是利用事务原子性的特性,即一个事务中的语句全都执行,或全都不执行。
首先创建一个测试表
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','')
执行结果:
即使最后一条语句出现错误(日期格式不匹配),但前两条依旧执行。
如果使用数据库事务
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
则与运行结果为
全都没有执行。
但各种类型的错误也有不同的表现
以上是数据格式不匹配的问题。不利用事务执行时,问题语句前的语句会正常执行,问题语句后的语句不会执行。利用事务执行后,则所有语句都不会执行。
但如果是类似的语句错误(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','')
结果
仅问题语句前的语句执行了。
但使用了数据库事务
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语句默认事务不自动回滚,如果想要自动回滚的话,需要语句设置。如下:
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
结果
即使没有错误处理语句,也可以完成回滚。
然而,回到开头的例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
目测此类问题应该与错误的类型与级别相关,但查询很久也没有得到很明确的答案。如有大佬明晰请指一条明路吧!