《数据库》实验9-事务.doc

1、实验：事务1一、实验目的与任务目的：旨在训练学生能创建带有事务的存储过程及了解事务的并发情况。任务：根据需求，写出满足条件的存储过程。任务1：在SchoolInfo数据库中执行如下语句：create table Ticket(TicketInfo varchar(20) primary key,TicketLeaveNum int)insert into Ticket values(G0001,10)任务2:测试事务执行情况。create proc prcInsertStudentasbegin trybegin transactionINSERT INTO Student VALUES(A0

2、0010,Andy,21,男,Nanjing,90,1)INSERT INTO Student VALUES(A00011,mary,21,女,Nanjing,150,1)commit transactionend trybegin catchrollback transactionend catch以上事务执行时，A00011号记录的成绩值超出了范围，编译器报错，此时会被Catch语句捕获，回滚事务，数据库中两条记录都不能插入。可将A00011号记录的成绩改为90，重新测试存储过程，两条语句同时被成功插入数据库。任务3: 创建事务，假设给定学号的学生将从原系转入给定的系，则相应的系总人数将发

3、生变化，如果两系相差人数小于10，则允许该生转系，否则回滚事务。假设系表Department(DepID,DepName,Total)答案：create proc prcTransferStuID char(10),DepName varchar(20)asbegin try if exists(select * from Student where StuID=StuID) if exists(select * from Department where DepName=DepName) begin begin transaction declare OriginDepID int sele

4、ct OriginDepID=DepID from Student where StuID=StuID declare NewDepID int select NewDepID=DepID from Department where DepName=DepName update Student set DepID=NewDepID where StuID=StuID declare OriginTotal int declare NewTotal int update Department set Total=Total-1 where DepID=OriginDepID update Dep

5、artment set Total=Total+1 where DepID=NewDepID select OriginTotal=Total from Department where DepID=OriginDepID select NewTotal=Total from Department where DepID=NewDepID if(NewTotal-OriginTotal=-10 and NewTotal-OriginTotal100 begin Rollback Transaction trnTransaction1 commit Transaction endelse com

6、mit Transaction任务5: 在编译器中使两个事务同时执行，查看数据库情况CREATE PROC prcSellTicketTicketInfo varchar(20), -车次信息TicketNum int -售出张数ASBegin tranDeclare TicketLeaveNum intSelect TicketLeaveNum = TicketLeaveNum from TicketWhere TicketInfo = TicketInfo -waitfor delay 00:00:20.005Update Ticket set TicketLeaveNum = Ticke

7、tLeaveNum - TicketNumWhere TicketInfo = TicketInfo -If(TicketLeaveNum TicketNum )begin rollback tran print 售票失败 endElse begin Commit tran print 售票成功 end以上程序如果多个窗口同时执行，数据会产生错误吗？如何修改以上程序？答案：会产生错误。使用锁或设置事务的隔离级别。-加锁alter PROC prcSellTicketTicketInfo varchar(20), -车次信息TicketNum int -售出张数ASbegin tryif exi

8、sts(select * from Ticket where TicketInfo = TicketInfo)beginBegin transelect TicketLeaveNum from Ticket with (tablockx)Declare TicketLeaveNum intSelect TicketLeaveNum = TicketLeaveNum from TicketWhere TicketInfo = TicketInfo -waitfor delay 00:00:20.000Update Ticket set TicketLeaveNum = TicketLeaveNu

9、m - TicketNumWhere TicketInfo = TicketInfo -If(TicketLeaveNum TicketNum )beginrollback tranprint 售票失败endElse beginCommit tranprint 售票成功endendelseprint 信息不存在end trybegin catchrollback tranprint 服务器忙，售票失败end catch-设置事务隔离级别alter PROC prcSellTicketTicketInfo varchar(20), -车次信息TicketNum int -售出张数ASbegin

10、tryif exists(select * from Ticket where TicketInfo = TicketInfo)beginset transaction isolation level repeatable readBegin tranDeclare TicketLeaveNum intSelect TicketLeaveNum = TicketLeaveNum from TicketWhere TicketInfo = TicketInfo -waitfor delay 00:00:20.000Update Ticket set TicketLeaveNum = Ticket

11、LeaveNum - TicketNumWhere TicketInfo = TicketInfo -If(TicketLeaveNum TicketNum )beginrollback tranprint 售票失败endElse beginCommit tranprint 售票成功endendelseprint 信息不存在end trybegin catchrollback tranprint 服务器忙，售票失败end catch三、内容与要求 课题名称事务1 （一）课题内容 按照任务1至任务5逐步完成，完成后学生可以创建带有事务的存储过程及了解事务的并发情况。 （二）课题要求按照实验任务书逐步完成实验（三）考核及报告要求 1. 考核能独立完成任一项任务。2. 报告要求提交一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附带每个任务的实验结果。四、主要仪器设备硬件：PC机软件：SQL SERVER 2005