本节介绍数据库事务中的SQL SERVER 事务的定义
本实验的目的:熟悉SQL SERVER 的事务控制语言,能够熟练使用事务控制语言来编写事务处理程序。
事务的原理解析:
1.事务的概念:
事务(TRANSACTION)是一组单一逻辑工作单元的操作集合,是采用高级数据操纵语言或编程语言书写的用户程序 ,并由事务开始BEGIN TRANSACTION 和事务结束END TRANSACTION 来界定全体操作的集合。
2.事务的性质::
数据库管理系统为了实现数据库系统的完整性,事务的ACID性质是数据库事务处理的基础,具有如下性质。
原子性:要求事务的全部操作要么在数据库中 全部正确地反映出来,要么全部不反映。
一致性:数据库中数据不因事务的执行而受到破坏,事务执行的结果应当使得数据库由一种一致性达到另一种 新的一致性。数据的一致性保证数据库的完整性。
隔离性:事务的并发执行与这些事务单独执行的结果一样。在多个事务并发时,各个事务不必关心其他事务的执行,如同在单个用户环境下执行一样。事务的隔离性是事务并发控制技术的基础。
持久性:事务对数据库的更新应永久的反映在数据库中。一个事务一旦完成其全部操作之后,对数据库所有更新操作的结果将在数据库中永久存在,及时以后发生故障也应保留这个事务的执行结果。持久性的意义在于保证数据库具有可恢复性。
以下是本次实验的练习与习题答案:(school表在此并未发出,请参考之前的练习)
USE SCHOOL
--4.1.4实验练习
--假设学校将学生的银行卡 和校园卡进行了绑定,允许学生直接从银行卡转账到校园卡中。假设某学号为05212222的学生需要从银行卡中转账100元到校园卡中,编写事务处理程序,实现这一操作。
--(1)采用隐式事务方式来实现事务编程。
UPDATE ICBC_CARD
SET RESTORED_MONEY = RESTORED_MONEY-100
WHERE STU_CARD_ID = '05212222'
UPDATE STU_CARD
SET REMAINED_MONEY = REMAINED_MONEY+100
WHERE CARD_ID = '05212222'
--*****注:以上的代码中采取隐式事务方法,如果对该学生的银行卡数据更新成功,会自动向数据库提交。假若后来对该学生 校园卡的数据更新未能成功,则会造成转账失败,但前一操作已经提交,无法还原。这就可能造成对数据库状态与事实语义不一致。
--*****注:针对以上问题,需要设法将这若干条SQL语句组合成一个独立的事务,这样才能保证各个操作步骤要么同时成功,要么一起失败。这就需要在之后的(2)中的显式事务方法来处理该事务。
--(2)采用显式事务用户定义事务的方法来实现事务编程。
BEGIN TRAN
UPDATE ICBC_CARD
SET RESTORED_MONEY = RESTORED_MONEY-100
WHERE STU_CARD_ID = '05212222'
UPDATE STU_CARD
SET REMAINED_MONEY = REMAINED_MONEY+100
WHERE CARD_ID = '05212222'
COMMIT TRAN
--*****注:为了完全控制事务并定义多个操作步骤组成的逻辑工作单元,可以采用显式用户定义事务的方式来实现用户期望的逻辑操作。
--(3)事务与批命令。
UPDATE COURSES SET HOUR = 96 WHERE CID = '10001'
INSERT TEACHERS VALUES('1234567890','MY','MY@ZSU.EDU.CN',3000)
SELECT TOP 10 * FROM TEACHERS(HOLDLOCK)
GO
--*****注:批处理是由一条或多条TRANSACT SQL 语句或命令组成,能够成组的运行,用于向SQL SERVER 提交成组的TRANSACT SQL 的语句组,由GO语句来终止语句组。批处理经过整体编译一次成为一个执行计划,并一次将整个执行计划执行完毕。
--*****注:出发皮命令没有固有的事务性质,除显式的定义由几个语句构成的单个事务,否则批命令中的每条语句都是一个互相独立的事务,每条语句单独完成或单独失败,而且批命令中的一个事务失败,不影响其他语句的执行。
--(4)嵌套事务的编程。
SELECT 'BEFORE TRANSACTION: ' AS HINT,@@TRANCOUNT AS TRANACTIONCOUNT
BEGIN TRAN
SELECT 'THE FIRST TRANSACTION STARTS: ' AS HINT,@@TRANCOUNT AS TRANACTIONCOUNT
SELECT TOP 3 * FROM CHOICES
BEGIN TRAN
SELECT 'THE SECOND TRANSACTION STARTS: ' AS HINT,@@TRANCOUNT AS TRANACTIONCOUNT
COMMIT TRAN
SELECT 'THE SECOND TRANSACTION COMMITS: ' AS HINT,@@TRANCOUNT AS TRANACTIONCOUNT
ROLLBACK TRAN
SELECT 'THE FIRST TRANSACTION ROLLBACK: ' AS HINT,@@TRANCOUNT AS TRANACTIONCOUNT
--*****注:嵌套事务主要是为了支持存储过程中的一些事务,这些事务可以从事务中已有的进程中调用,也可以从没有活动的事务进程中调用。嵌套事务队伍COMMIT TRANSACTION语句的每个调用都对应 于最后执行的BEGIN TRANSACTION 语句,也就是最内层的事务。
--(5)在存储过程、触发器中使用事务编程。
CREATE TRIGGER TD_COURSE ON COURSES
FOR DELETE
AS
DECLARE @INFO VARCHAR(255)
SELECT @INFO = '触发器中的事务数据为: ' +CONVERT(VARCHAR(2),@@TRANCOUNT)
PRINT @INFO
RETURN
--*****注:触发器是一种特殊类型的存储过程,主要用于完成 数据库中对象的完整性。在表中进行数据修改时自动执行,触发器被视为执行数据修改事务的一部分,与数据修改语句在同一事务空间中执行。由于触发器已经在事务情景中操作,因此事务中要的事务控制语句只有ROLLBACK或者SAVE TRAN,不需要发出BEGIN TRAN。
PRINT '删除操作以前触发器中的事务数为: ' +CONVERT(VARCHAR(2),@@TRANCOUNT);
DELETE FROM COURSES
WHERE CID = '10052'
PRINT '删除操作之后触发器中的事务数为: ' +CONVERT(VARCHAR(2),@@TRANCOUNT);
--*****注:以上代码为COURSES的删除操作,创建一个触发器,然后执行一个删除操作,观察事务数目的变化。在删除操作执行的过程中,触发器得到执行,而且事务的数目为1,这就验证了是触发器事务是数据修改事务的一部分。
CREATE PROCEDURE INSERTCOURSEINFO
@COURSEID CHAR(10),
@COURSENAME VARCHAR(30),
@HOUR INT,
@RETURNSTRING VARCHAR(100)
AS
BEGIN TRAN
IF EXISTS(SELECT CID FROM COURSES WHERE CID = @COURSEID)
BEGIN
SELECT @RETURNSTRING = '课程信息已经存在'
GOTO ONERROR
END
--新增课程信息
INSERT INTO COURSES VALUES(@COURSEID,@COURSENAME,@HOUR)
IF @@ERROR<>0
BEGIN
SELECT @RETURNSTRING = '新增课程信息失败'
GOTO ONERROR
END
SELECT @RETURNSTRING = '新增课程信息成功'
COMMIT TRAN
--错误处理
ONERROR:
ROLLBACK TRAN
--*****注:同样以上代码为从事务中调用存储的过程,也可以在存储过程中启动事务,而且这是经常在数据库开发过程中应用到的。因为在存储过程中使用事务,可以提高数据库操作的效率,可以方便维护。
--(6)命令事务与事务保存点。
--方法①:
BEGIN TRAN TRAN_UPD_COURSES
UPDATE COURSES
SET HOUR = 60
WHERE CID = '10052'
BEGIN TRAN TRAN_UPD_TEACHERS
INSERT INTO TEACHERS
VALUES('1234567890','ZS','my@zsu.deu.cn',3000)
IF @@ERROR!=0
BEGIN
--撤销事务
ROLLBACK TRAN TRAN_UPD_TEACHERS
PRINT '更新教师表失败'
RETURN
END
--提交内层事务
COMMIT TRAN TRAN_UPD_TEACHERS
--提交外层事务
COMMIT TRAN TRAN_UPD_COURSES
--*****注:在编写大的存储过程、唱的批处理,以及大量事务嵌套的时候,一个常见的问题就是代码的可读性差。为了改进代码的可读性,在进行事务编程的时候,可以对事务进行命名清晰的标志事务,提示用户代码的逻辑性。
--*****注:命名事务是通过在BEGIN TRAN 语句中为事务命名,来标志整个事务逻辑的工作单元。通过对事务命名,使得每个事务都易于识别,这对于事务嵌套更加重要。
--方法②:
BEGIN TRAN TRAN_UPD_COURSES
UPDATE COURSES
SET HOUR = 45
WHERE CID = '10052'
--设置事务保存点
SAVE TRAN TRAN_UPD_COURSES_DONE
INSERT INTO TEACHERS
VALUES('1234567890','ZS','my@zsu.deu.cn',3000)
IF @@ERROR!=0 OR @@ROWCOUNT>1
BEGIN
--撤销事务
ROLLBACK TRAN TRAN_UPD_TEACHERS_DONE
PRINT '更新教师表信息失败!'
RETURN
END
--提交事务
COMMIT TRAN TRAN_UPD_COURSE
--*****注:此方法是事务保存点,事务保存点提供一种在事务中标记用ROLLBACK撤销事务工作点的方法。利用事务保存点,可提交事务开始处至保存点的部分事务,而将事务的其他部分撤销。
运行结果请读者自试。