S Q L数据库中事务的实现方法

文/蔡义忠 熊发涯

本文主要探讨事务的定义，事务的基本特征，以及在S Q L S e r v e r数据库的存储过程中的具体实现方法。还将探讨事务在执行过程中失败了，在存储过程中是怎样判断及怎样具体处理的方法。

笔者在开发一个《说课比赛网络评分系统》时，遇到需要对多个数据库表同时操作的情况，如果用普通的做法，顺序对各个数据库表进行操作，就可能会在极端情况下，出现操作一部分数据库表时操作被中断，导致数据库中的信息出现不一致，甚至出现“脏数据”，从而影响软件系统的稳定运行。如果使用事务来处理对多个数据库表的操作就可以避免在极端情况下的错误产生。

事务是对数据库执行的一个操作单位，它是以逻辑顺序完成的工作单元或工作序列，无论是用户手工操作，还是由程序进行的自动操作。

一个事务可以是一个或多个DML语句。在管理事务时，任何指定的事务都必须作为一个整体来完成，否则其中任何一条语句都不会完成。下面是事务的基本特征：

1．所有的事务都有开始和结束。例如，在SQL Server中事务用BEGIN TRANSACTION开始。

2.事务可以被保存或撤消。例如，在SQLS e r v e r 中撤消事务的方法用R O L L B A C KT R A N S A C T I O N 。提交事务用COMMIT TRANSACTION。通常撤消或保存事务是放在一个条件判断语句中来执行的。

3．如果事务在中途失败，事务中的任何部分都不会被记录到数据库中，这样可以很好的保证数据的完整性。

注意：事务控制命令只与DML 命令INSERT、UPDATE 和DELETE配合使用，如我们不会创建表之后使用COMMIT 语句，因为当表被创建之后，它会自动被提交给数据库。类似地，不能使用ROLLBACK 语句来恢复被撤消的表。

当事务完成后，事务信息被保存在数据库里的指定区域或临时回退区域。所有的修改都被保存到这个临时回退区域，直到事务控制命令出现。当事务控制命令出现时，所做的修改要么被保存到数据库中，要么被放弃，然后临时回退区域被清空。

以具体的应用实例来进一步解释事务的使用方法。在我最近开发的《说课比赛网络评分系统》中，要求一个院校最多只能报四门课程参加湖北省高职高专院校说课比赛。那么，在数据库系统中，增加一个院校表信息的同时，就得在用户表和课程表中同时增加四条记录信息，并且一个新增的参赛教师信息对应一个新增的参赛课程信息。这个功能对应的界面如图1所示。

这个功能需求，用存储过程及事务来实现，具体代码如下：

注意：SQL Server的注释是用“--”开头，读者可以通过看注释来理解事务的实现方法。

图1 通过事务增加院校的界面

--Proc_VS_SchoolAddTran

功能：添加一个学校的记录的同时，添加四个教师记录和四门课程记录，每个教师对应着一门课程，其中第一个教师为默认的该校联系负责人。

Create Procedure [dbo].[Proc_VS_SchoolAddTran] --创建存储过程

(

@SchoolName [varchar](50), --学校名称

@SchoolAddr [varchar](250) --学校地址

)

As

BEGIN --存储过程的实现开始

BEGIN TRANSACTION--启动事务

DECLARE @ERROR INT --定义整型变量存储失败错误编号

DECLARE @SCHOOLID INT --定义学校编号

DECLARE @TEACHERID INT --定义教师编号

SET @ERROR=0 --初始化记录错误编号的变量值为0--根据存储过程的参数@SchoolName,@SchoolAddr来插入一条学校信息记录。

Insert Into [dbo].[tb_VS_School](

[SchoolName],

[SchoolAddr]

)

Values

(

@SchoolName,

@SchoolAddr

)

SET @ERROR=@ERROR+@@ERROR --保存当前操作返回的错误信息编号，注意没错误时@@ERROR返回为0，有错误时，@@ERROR返回为非零的正整数

SET @SCHOOLID= @@identity --保存当前插入记录的自动生成的院校ID号

--插入第一个教师的初始信息Insert Into [dbo].[tb_VS_User](

[UserTypeID], --教师类型

[Password], --参赛教师的登陆密码，默认为123456

[Status], --参赛教师的申报信息状态，默认为0，即未审核[SchoolID]--参赛教师所在学校的编号，即上面生成新院校的ID号)

Values(

1,

'123456',

0,

@S C HO O LI D

)

SET @ERROR=@ERROR+@@ERROR --保存当前操作返回的错误信息编号

SET @TEACHERID=@@identity --保存当前插入记录的自动生成的教师ID号

Update [dbo].[tb_VS_School]

Set

[ConnectManID]=@TEACHERID

Where SchoolID=@SCHOOLID

SET @ERROR=@ERROR+@@ERROR

Insert [dbo].[tb_VS_Course] --插入一门新课程信息

(

[CourseName],--课程名

[FromSchoolID], --所在院校编号，即上面的新增院校ID编号

[FromTeacherID], --所属教师编号，即上面的新增教师ID编号

[Status] --状态信息，初始为0，表示未审核

)

Values

(

'某课程',

@SCHOOLID,@TEACHERID,

0

)

SET @ERROR=@ERROR+@@ERROR

--增加第二名第三名第四名教师及课程的操作代码同上，在此处略过。

--由于只要有一个Insert语句执行出现失败，@@ERROR

都会产生一个正整数，所以每次操作结束都用

@ERROR=@ERROR+@@ERROR记录下@ERROR的值是否发生变化，如果@ERROR始终为零，则一切正常，可以提交事务，完成整个操作，反之，则回滚事务，放弃所做的修改。

IF(@ERROR=0)

BEGIN

COMMIT TRANSACTION

Return @SCHOOLID

END

ELSE

BEGIN

ROLLBACK TRANSACTION Return 0

END

END

go

--最后用exec来执行测试一下Proc_VS_SchoolAddTran的功能

--exec Proc_VS_SchoolAddTran '黄冈技工学院','黄冈市黄州区'

事务处理在数据库应用中极为重要，是保证数据库应用系统安全稳定运行的重要手段。本文中的小案例中的判断事务执行的成功或失败是通过记录每个操作语句执行后返回的系统错误号@@ERROR来判断，只要有一次出现@@ERROR返回值不是0，则最终的@ERROR变量就不能为0，也就可以判断事务是否失败。注意区别，@ERROR是我定义的变量，@@ERROR是系统返回的错误编号，执行正常时@@ERROR返回为0,执行不正常@@ERROR返回为非0。