欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Transcation的一些总结分享

程序员文章站 2023-11-29 09:25:16
1.1.1 摘要 相信大家对于sql transcation再熟悉不过,它确保了数据库的数据一致性和安全性,尤其在对数据执行增删时,如果发生异常和错误它就会触发事务回滚,从...
1.1.1 摘要
相信大家对于sql transcation再熟悉不过,它确保了数据库的数据一致性和安全性,尤其在对数据执行增删时,如果发生异常和错误它就会触发事务回滚,从而确保了我们数据的一致性和安全性,下面我们将通过分四部分介绍事件(transcation)。

1.1.2 正文
首先让我们通过一个具体的例子介绍transcation的使用,假如我们的数据库中有一个表userinfo,它包含三个字段分别为:userid(自增)、username (nvarchar)和luckynumber (tinyint),如下图所示:

SQL Transcation的一些总结分享
图1 userinfo表


userinfo表的sql代码如下:
复制代码 代码如下:

-- the definition of userinfo.
set ansi_nulls on
go
set quoted_identifier on
go
create table [dbo].[userinfo](
[userid] [int] identity(1,1) not null,
[username] [nvarchar](50) not null,
[luckynumber] [tinyint] not null
) on [primary]

接着我们要把数据插入到表userinfo中,这里使用一个存储过程把数据插入到该表中,存储过程spadddatatouserinfo的定义如下:
复制代码 代码如下:

-- =============================================
-- author: jkhuang
-- create date: 12/8/2011
-- description: inserts data
-- =============================================
create procedure spadddatatouserinfo

as
begin
-- set nocount on added to prevent extra result sets from
-- interfering with select statements.
set nocount on;

-- hard code inserted data.
insert into userinfo values('jkhuang', 8);
insert into userinfo values('jkrush', 23);
insert into userinfo values('jackson', 20111111);
end
go

现在我们已经定义了一个存储过程,接着让我们执行该存储过程。

SQL Transcation的一些总结分享

图2执行存储过程的消息


通过上图我们知道在执行存储过程中发生了异常,而且是由于值“20111111”数据超出了tinyint的范围产生的,现在让我们看一下数据插入的情况。

SQL Transcation的一些总结分享

图3 userinfo表中数据

我们发现只插入了两行数据,而第三行数据没有成功插入,但为了确保数据完整性,我们要把数据全部插入或全部不插入,这时我们可以考虑使用transcation来确保数据完整性和安全性。
接着让我们修改一下存储过程spadddatatouserinfo,在存储过程中添加transcation。
复制代码 代码如下:

-- =============================================
-- author: jkhuang
-- create date: 12/8/2011
-- description: inserts data
-- =============================================
alter procedure spadddatatouserinfo
as
begin
begin transaction
-- set nocount on added to prevent extra result sets from
-- interfering with select statements.
set nocount on;
-- hard code inserted data.
insert into userinfo values('jkhuang', 8);
insert into userinfo values('jackson', 20111111);
insert into userinfo values('jkrush', 23);
commit transaction
end
go

现在我们再执行一次存储过程看一下是否全部不插入到表中。

SQL Transcation的一些总结分享

图4 userinfo表中数据

我们发现结果和没有添加transcation处理一样,数据依然插入到表中。这究竟是什么原因呢?也许细心的你已经发现了,我们没有添加事务回滚——rollback。
但我们究竟要在哪里添加事务回滚(rollback)呢?或更具体地说:“究竟什么时候我们要触发事务回滚(rollback)呢”?
由于我们数据插入失败是因为插入过程发生了异常情况,那么我们就要捕获异常和处理异常,那就是try/catch的设计了,好让我们继续完善我们的存储过程吧。
复制代码 代码如下:

-- =============================================
-- author: jkhuang
-- create date: 12/8/2011
-- description: inserts data
-- =============================================
alter procedure spadddatatouserinfo
as
begin
begin try
begin transaction
-- set nocount on added to prevent extra result sets from
-- interfering with select statements.
set nocount on;
-- hard code inserted data.
insert into userinfo values('jkhuang', 8);
insert into userinfo values('jackson', 20111111);
insert into userinfo values('jkrush', 23);
commit transaction
end try
begin catch
rollback transaction
end catch
end
go


现在我们给存储过程添加了异常处理机制try/catch(注意:sqlserver 2005或之后的版本才支持try/catch),接下来让我们再执行一次存储过程。
SQL Transcation的一些总结分享图5 userinfo表中数据

看上去我们已经把问题的解决了,我们知道存储过程可以内嵌存储过程或函数,所以我们把上面的存储过程spadddatatouserinfo内嵌到存储过程spmultidatatouserinfo中,spmultidatatouserinfo的定义如下:
复制代码 代码如下:

-- =============================================
-- author: jkhuang
-- create date: 12/8/2011
-- description: invokes store procedure to insert data.
-- =============================================
create procedure spmultidatatouserinfo

as
begin
begin try
begin transaction

-- set nocount on added to prevent extra result sets from
-- interfering with select statements.
--set nocount on;

-- hard code inserted data.
insert into userinfo values('cris', 1);
exec spadddatatouserinfo
insert into userinfo values('ada', 32);

commit transaction

end try
begin catch

rollback transaction

end catch

end
go

我们知道存储过程spadddatatouserinfo会发生异常,它会回滚事务(rollback),但spmultidatatouserinfo是发生回滚还是继续插入数据呢?

SQL Transcation的一些总结分享

图6 执行存储过程消息
SQL Transcation的一些总结分享
图7 userinfo表中数据

在插入的过程发生了异常,检查userinfo表数据并没有插入表中,这符合我们设计的意图,但我们发现异常不仅仅是产生于插入的数据超出了数值的范围,还包含事务计数异常。

由于定义了两个存储过程,而且spadddatatouserinfo内嵌在spmultidatatouserinfo中,在执行这两个存储过程中,它们都发生了异常并且进行事务回滚(因为user为空)。

由于插入的数据超出了数值的范围的异常是我们特意引起的,而事务计数异常这是预期之外的异常。

接下来让我们看一下究竟是什么原因引起了该异常,这里我们通过输出transactions计数来查看问题所在。
复制代码 代码如下:

-- =============================================
-- author: jkhuang
-- create date: 12/8/2011
-- description: inserts data
-- =============================================
alter procedure spadddatatouserinfo

as
begin
begin try
begin transaction
print 'in [spadddatatouserinfo] transactions: ' + convert(varchar, @@trancount);
-- set nocount on added to prevent extra result sets from
-- interfering with select statements.
--set nocount on;

-- hard code inserted data.
insert into userinfo values('jkhuang', 8);
insert into userinfo values('jackson', 20111111);
insert into userinfo values('jkrush', 23);

commit transaction

end try
begin catch
print 'error in [spadddatatouserinfo]: ' + error_message();
rollback transaction
print error_message();
print 'rolled back successful transactions: ' + convert(varchar, @@trancount);
end catch

end
go

-- =============================================
-- author: jkhuang
-- create date: 12/8/2011
-- description: invokes store procedure to insert data.
-- =============================================
alter procedure spmultidatatouserinfo

as
begin
begin try
begin transaction
print 'in [spmultidatatouserinfo] transactions: ' + convert(varchar, @@trancount);
-- set nocount on added to prevent extra result sets from
-- interfering with select statements.
--set nocount on;

-- hard code inserted data.
insert into userinfo values('cris', 1);
exec spadddatatouserinfo
insert into userinfo values('ada', 32);

commit transaction

end try
begin catch
print 'error in [spmultidatatouserinfo]: ' + error_message();
rollback transaction
print error_message();
print 'rolled back successful transactions: ' + convert(varchar, @@trancount);
end catch

end
go

上面我们通过输出事务的计数(@@trancount)来查看在事务回滚时,事务计数器的变化。
SQL Transcation的一些总结分享图8 存储过程执行消息

通过上图我们发现在执行spmultidatatouserinfo和spadddatatouserinfo时,事务计数器分别加1,当遇到spadddatatouserinfo中的异常时,事务回滚事务计算器置零。
当执行spmultidatatouserinfo中的事务时,由于事务计算器(@@trancount)已经置零,导致抛出异常,现在我们明白了导致事务计数异常的原因,所以我们在进行事务回滚之前必须判断事务计算器(@@trancount)是否为0,如果为0就不回滚事务。
复制代码 代码如下:

-- =============================================
-- author: jkhuang
-- create date: 12/8/2011
-- description: inserts data
-- =============================================
alter procedure spadddatatouserinfo
as
begin
begin try
begin transaction
print 'in [spadddatatouserinfo] transactions: ' + convert(varchar, @@trancount);
-- set nocount on added to prevent extra result sets from
-- interfering with select statements.
--set nocount on;
-- hard code inserted data.
insert into userinfo values('jkhuang', 8);
insert into userinfo values('jackson', 20111111);
insert into userinfo values('jkrush', 23);
commit transaction
end try
begin catch
print 'error in [spadddatatouserinfo]: ' + error_message();
if (@@trancount > 0)
rollback transaction
print 'rolled back successful in spadddatatouserinfo transactions: ' + convert(varchar, @@trancount);
end catch
end
go
-- =============================================
-- author: jkhuang
-- create date: 12/8/2011
-- description: invokes store procedure to insert data.
-- =============================================
alter procedure spmultidatatouserinfo
as
begin
begin try
begin transaction
print 'in [spmultidatatouserinfo] transactions: ' + convert(varchar, @@trancount);
-- set nocount on added to prevent extra result sets from
-- interfering with select statements.
--set nocount on;
-- hard code inserted data.
insert into userinfo values('cris', 1);
exec spadddatatouserinfo
insert into userinfo values('ada', 32);
commit transaction
end try
begin catch
print 'error in [spmultidatatouserinfo]: ' + error_message();
if (@@trancount > 0)
rollback transaction
print 'rolled back successful in spmultidatatouserinfo transactions: ' + convert(varchar, @@trancount);
end catch
end
go

现在我们增加了事务计数器的判断,当计数器为0时,不进行事务回滚,这样就没有了之前事务计数器异常了。
SQL Transcation的一些总结分享
图9 存储过程执行消息

现在对于事务我们有了进一步的了解,而且把以上的事务定义成为一个日常通用的模板,如下给出了一个基本的transaction模板。

复制代码 代码如下:

-- =============================================
-- transaction temp
-- =============================================
begin try
begin transaction
--
-- you code here.
--
commit transaction
end try
begin catch
if (@@trancount > 0)
-- adds store procedure
-- writes the error into errorlog table.
rollback transaction
end if
end catch


1.1.3 总结
事务是作为单个逻辑工作单元执行的一系列操作。可以是一条sql语句也可以是多条sql语句。
事务具有四个特性
原子性:不可分隔、成则具成、败则具败。
一致性:事务在完成时,必须使所有的数据都保持一致状态
隔离性:独立的执行互不干扰。由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。
持久性:务完成之后,它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。
应用程序主要通过指定事务启动和结束的时间来控制事务。
启动事务:使用 api 函数和 transact-sql 语句,可以按显式、自动提交或隐式的方式来启动事务。
结束事务:您可以使用 commit(成功) 或 rollback(失败) 语句,或者通过 api 函数来结束事务。
事务模式分为:显示事务模式、隐式事务模式、自动事务模式。在sql常用的是显示模式。
创建事务的原则:
尽可能使事务保持简短很重要,当事务启动后,数据库管理系统 (dbms) 必须在事务结束之前保留很多资源、以保证事务的正确安全执行。
特别是在大量并发的系统中, 保持事务简短以减少并发 资源锁定争夺,将先得更为重要。
1、事务处理,禁止与用户交互,在事务开始前完成用户输入。
2、在浏览数据时,尽量不要打开事务
3、尽可能使事务保持简短。
4、考虑为只读查询使用快照隔离,以减少阻塞。
5、灵活地使用更低的事务隔离级别。
6、灵活地使用更低的游标并发选项,例如开放式并发选项。
7、在事务中尽量使访问的数据量最小。