SQL Server 事务、异常

Ø 事务

在数据库中有时候需要把多个步骤的指令当作一个整体来运行，这个整体要么全部成功，要么全部失败，这就需要用到事务。

 

1、 事务的特点
事务有若干条T-SQL指令组成，并且所有的指令作为一个整体提交给数据库系统，执行时，这组指令要么全部执行完成，要么全部取消。因此，事务是一个不可分割的逻辑单元。

事务有4个属性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）以及持久性（Durability），也称作事务的ACID属性。
原子性：事务内的所有工作要么全部完成，要么全部不完成，不存在只有一部分完成的情况。
一致性：事务内的然后操作都不能违反数据库的约束或规则，事务完成时所有内部数据结构都必须是正确的。
隔离性：事务之间是相互隔离的，如果有两个事务对同一个数据库进行操作，比如读取表数据。任何一个事务看到的所有内容要么是其他事务完成之前的状态，要么是其他事务完成之后的状态。一个事务不可能遇到另一个事务的中间状态。
持久性：事务完成之后，它对数据库系统的影响是持久的，即使是系统错误，重新启动系统后，该事务的结果依然存在。

 

2、 事务的模式

a、 显示事务
显示事务就是用户使用T-SQL明确的定义事务的开始（begin transaction）和提交（commit transaction）或回滚事务（rollback transaction）

b、 自动提交事务
自动提交事务是一种能够自动执行并能自动回滚事务，这种方式是T-SQL的默认事务方式。
例如在删除一个表记录的时候，如果这条记录有主外键关系的时候，删除就会受主外键约束的影响，那么这个删除就会取消。

c、 隐式事务
隐式事务是指当事务提交或回滚后，SQL Server自动开始事务。
使用时，需要设置set implicit_transaction on语句，将隐式事务模式打开，下一个语句会启动一个新的事物，再下一个语句又将启动一个新事务。

3、 事务处理

常用T-SQL事务语句：
a、 begin transaction语句
开始事务，
@@trancount（全局变量用来记录事务的数目）值加1，@@error全局变量记录执行过程中的错误信息，如果没有错误可以直接提交事务，有错误可以回滚。

b、 commit transaction语句
提交事务，表示一个隐式或显示的事务的结束，对数据库所做的修改正式生效。并将@@trancount的值减1；

c、 rollback transaction语句
回滚事务，执行rollback tran语句后，数据会回滚到begin tran的时候的状态

 

4、 事务的示例

--开始事务
begin transaction tran_bank;
declare @tran_error int;
set @tran_error = 0;
begin try
update bank set totalMoney = totalMoney - 10000 where userName = 'jack';
set @tran_error = @tran_error + @@error;
update bank set totalMoney = totalMoney + 10000 where userName = 'jason';
set @tran_error = @tran_error + @@error;
end try
begin catch
print '出现异常，错误编号：' + convert(varchar, error_number()) + '， 错误消息：' + error_message();
set @tran_error = @tran_error + 1;
end catch
if (@tran_error > 0)
begin
--执行出错，回滚事务
rollback tran;
print '转账失败，取消交易';
end
else
begin
--没有异常，提交事务
commit transaction;
print '转账成功';
end
go

 

 

Ø 异常
在程序中，有时候完成一些Transact-SQL会出现错误、异常信息。如果我们想自己处理这些异常信息的话，需要手动捕捉这些信息。那么我们可以利用try catch完成。

错误函数

ERROR_NUMBER() 返回错误号。
ERROR_MESSAGE() 返回错误消息的完整文本。此文本包括为任何可替换参数（如长度、对象名称或时间）提供的值。
ERROR_SEVERITY() 返回错误严重性。
ERROR_STATE() 返回错误状态号。
ERROR_LINE() 返回导致错误的例程中的行号。
ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称。

示例
#错误消息存储过程
if (object_id('proc_error_info') is not null)
drop procedure proc_error_info
go
create proc proc_error_info
as
select
error_number() '错误编号',
error_message() '错误消息',
error_severity() '严重性',
error_state() '状态好',
error_line() '错误行号',
error_procedure() '错误对象(存储过程或触发器)名称';
go

#简单try catch示例
begin try
select 1 / 0;
end try
begin catch
exec proc_error_info; --调用错误消息存储过程
end catch
go

#异常能处理的错误信息
--无法处理错误，语法错误
begin try
select * * from student;
end try
begin catch
exec proc_error_info;
end catch
go
--不处理错误(不存在的表对象)
begin try
select * from st;
end try
begin catch
exec proc_error_info;
end catch
go
--能处理存储过程（触发器）中（不存在表对象）的错误信息
if (object_id('proc_select') is not null)
drop procedure proc_select
go
create proc proc_select
as
select * from st;
go
begin try
exec proc_select;
end try
begin catch
exec proc_error_info;
end catch
go

异常不能处理编译期的错误，如语法错误。以及重编译造成部分名称对象得不到正确解析的时候所出现的错误。

#无法提交的事务

--创建临时用表
if (object_id('temp_tab', 'u') is not null)
drop table temp_tab
go
create table temp_tab(
id int primary key identity(100000, 1),
name varchar(200)
)
go

begin try
begin tran;
--没有createTime字段
alter table temp_tab drop column createTime;
commit tran;
end try
begin catch
exec proc_error_info;--显示异常信息
if (xact_state() = -1)
begin
print '会话具有活动事务，但出现了致使事务被归类为无法提交的事务的错误。'
+ '会话无法提交事务或回滚到保存点；它只能请求完全回滚事务。'
+ '会话在回滚事务之前无法执行任何写操作。会话在回滚事务之前只能执行读操作。'
+ '事务回滚之后，会话便可执行读写操作并可开始新的事务。';
end
else if (xact_state() = 0)
begin
print '会话没有活动事务。';
end
else if (xact_state() = 1)
begin
print '会话具有活动事务。会话可以执行任何操作，包括写入数据和提交事务。';
end
end catch
go

#处理异常日志信息
---异常、错误信息表
if (object_id('errorLog', 'U') is not null)
drop table errorLog
go
create table errorLog(
errorLogID int primary key identity(100, 1), --ErrorLog 行的主键。
errorTime datetime default getDate(), --发生错误的日期和时间。
userName sysname default current_user, --执行发生错误的批处理的用户。
errorNumber int, --发生的错误的错误号。
errorSeverity int, --发生的错误的严重性。
errorState int, --发生的错误的状态号。
errorProcedure nvarchar(126), --发生错误的存储过程或触发器的名称。
errorLine int, --发生错误的行号。
errorMessage nvarchar(4000)
)
go

--存储过程：添加异常日志信息
if (object_id('proc_add_exception_log', 'p') is not null)
drop proc proc_add_exception_log
go
create proc proc_add_exception_log(@logId int = 0 output)
as
begin
set nocount on;
set @logId = 0;
begin try
if (error_number() is null)
return;

if (xact_state() = -1)
begin
print '会话具有活动事务，但出现了致使事务被归类为无法提交的事务的错误。'
+ '会话无法提交事务或回滚到保存点；它只能请求完全回滚事务。'
+ '会话在回滚事务之前无法执行任何写操作。会话在回滚事务之前只能执行读操作。'
+ '事务回滚之后，会话便可执行读写操作并可开始新的事务。';
end
else if (xact_state() = 0)
begin
print '会话没有活动事务。';
end
else if (xact_state() = 1)
begin
print '会话具有活动事务。会话可以执行任何操作，包括写入数据和提交事务。';
end

--添加日志信息
insert into errorLog values(getDate(),
current_user, error_number(),
error_severity(), error_state(),
error_procedure(),
error_line(), error_message());
--设置自增值
select @logId = @@identity;
end try
begin catch
print '添加异常日志信息出现错误';
exec proc_error_info;--显示错误信息
return -1;
end catch
end
go

---处理异常信息示例
declare @id int;
begin try
begin tran;
--删除带有外键的记录信息
delete classes where id = 1;
commit tran;
end try
begin catch
exec proc_error_info;--显示错误信息
if (xact_state() <> 0)
begin
rollback tran;
end
exec proc_add_exception_log @id output
end catch
select * from errorLog where errorLogID = @id;
go

 

 

 

本文转载自： http://www.cnblogs.com/hoojo/archive/2011/07/19/2110325.html