解读SQL一些语句执行后出现异常不会回滚的问题

admin MsSql

2023-12-01 0 941

SQL一些语句执行后出现异常不会回滚

MySQL回滚问题

SQL中会隐式提交的操作：

1、DDL语句：ALTER DATABASE、ALTER EVENT、ALTER PROCEDURE、ALTER TABLE、ALTER VIEW、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等；

2、修改MYSQL架构的语句：CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD；

3、管理语句：ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE等，

总结：设计事务时，不应包含这类语句。如果在事务的前部中发布了一个不能被回滚的语句，则后部的其它语句会发生错误，在这些情况下，通过发布ROLLBACK语句不能回滚事务的全部效果，简单来说如果这个不

能被回滚的语句之前有事务，并且这个不能被回滚的语句执行异常了，之前的修改操作会隐式的提交。

例如：

这种情况下 int i=5/0；发生异常了，上面的修改操作会发生回滚，修改密码失败的。

这种情况下userMapper.dropField(tableName,fieldName);我故意tableName传一个不存在的表名，这里会抛出异常，但是上面的更新密码操作还是会成功（隐式地提交了事务）

SQL事务回滚的两种方式

1.XACT_ABORT

1）set XACT_ABORT off时,回滚产生错误的Transact-SQL语句,而事务将继续进行处理，（注：错误严重或者语法错误时可能回滚整个事务）

2) set XACT_ABORT ON时,如果Transact-SQL语句产生运行时错误,整个事务将终止并回滚,

步骤1：

CREATE TABLE [dbo].[A](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Name] [nvarchar](5) NULL,
[CreateDate] [datetime] NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

–默认XACT_ABORT=off
SET XACT_ABORT off
begin tran
insert into [dbo].[A](Name,CreateDate) values(\’测试off\’,GETDATE());
insert into [dbo].[A](Name,CreateDate) values(\’测试off_异常\’,GETDATE());
insert into [dbo].[A](Name,CreateDate) values(\’测试off\’,GETDATE());
commit tran

(1 行受影响)

消息 8152，级别 16，状态 4，第 3 行
将截断字符串或二进制数据。
语句已终止。

(1 行受影响)

select * from [dbo].[A]

IDNameCreateDate1测试off

2018-10-31 15:06:09.330

3测试off2018-10-31 15:06:09.333

注意：

事务的回滚对于拥有自增主键的表来说，insert的数据是被删除，update和delete的数据是被恢复。所以上面所显示的ID=3 。

步骤2：

set XACT_ABORT ON
begin tran
insert into [dbo].[A](Name,CreateDate) values(\’测试on\’,GETDATE());
insert into [dbo].[A](Name,CreateDate) values(\’测试on_异常\’,GETDATE());
insert into [dbo].[A](Name,CreateDate) values(\’测试on\’,GETDATE());
commit tran

同样执行select 查询语句得到如下结果

IDNameCreateDate1测试off

2018-10-31 15:06:09.330

3测试off2018-10-31 15:06:09.333

证明事务全部回滚成功。（注意下次新增数据的时候，ID的值。。。）

2.rollback

在sql中用事务的时候一般是这么写。

declare @error int ;
begin tran
begin try
insert into [dbo].[A](Name,CreateDate) values(\’测试异常\’,GETDATE());
insert into [dbo].[A](Name,CreateDate) values(\’测试异常_错误\’,GETDATE());
insert into [dbo].[A](Name,CreateDate) values(\’测试异常\’,GETDATE());
end try
begin catch
set @error=1
end catch
if @error>0
rollback tran
else
commit tran

注：

判断事务回滚,则整个事务里面的sql语句一起回滚,跟SET XACT_ABORT ON相似,不同的是当事务进行嵌套时,如果用事务判断的方法,嵌套的子事务不出错,外面语句出错,那么整个事务只会回滚外面出错的语句,子事务提交成功了就回滚不了了,但是用SET XACT_ABORT ON,即使子事务提交成功,如果外围事务出错,子事务还是回滚.