《数据库》课件实验9-事务

实验：事务1一、实验目的与任务目的：旨在训练学生能创建带有事务的存储过程及了解事务的并发情况。任务：根据需求，写出满足条件的存储过程。任务1：在SchoolInfo数据库中执行如下语句：createtableTicket( TicketInfovarchar(20)primarykey, TicketLeaveNumint)insertintoTicketvalues('G0001',10)任务2:测试事务执行情况。createprocprcInsertStudentasbegintrybegintransactionINSERTINTOStudentVALUES('A00010',‘Andy',21,'男','Nanjing',90,1)INSERTINTOStudentVALUES(‘A00011’,‘mary’,21,'女','Nanjing',150,1)committransactionendtrybegincatch rollbacktransactionendcatch以上事务执行时，A00011号记录的成绩值超出了范围，编译器报错，此时会被Catch语句捕获，回滚事务，数据库中两条记录都不能插入。可将A00011号记录的成绩改为90，重新测试存储过程，两条语句同时被成功插入数据库。任务3:创建事务，假设给定学号的学生将从原系转入给定的系，则相应的系总人数将发生变化，如果两系相差人数小于10，则允许该生转系，否则回滚事务。假设系表Department(DepID,DepName,Total)答案：createprocprcTransfer@StuIDchar(10),@DepNamevarchar(20)asbegintryifexists(select*fromStudentwhereStuID=@StuID)ifexists(select*fromDepartmentwhereDepName=@DepName)beginbegintransactiondeclare@OriginDepIDintselect@OriginDepID=DepIDfromStudentwhereStuID=@StuIDdeclare@NewDepIDintselect@NewDepID=DepIDfromDepartmentwhereDepName=@DepNameupdateStudentsetDepID=@NewDepIDwhereStuID=@StuIDdeclare@OriginTotalintdeclare@NewTotalintupdateDepartmentsetTotal=Total-1whereDepID=@OriginDepIDupdateDepartmentsetTotal=Total+1whereDepID=@NewDepIDselect@OriginTotal=TotalfromDepartmentwhereDepID=@OriginDepIDselect@NewTotal=TotalfromDepartmentwhereDepID=@NewDepIDif(@NewTotal-@OriginTotal>=-10and@NewTotal-@OriginTotal<=10)commitTransactionelseRollbackTransactionendelseprint'DepName不存在'elseprint'StuID不存在'endtrybegincatchRollbackTransactionendcatch执行：execprcTransfer'A00001','math'任务4:SC表和Department表需要用下面的事务来更新：事务1:UPDATESCSETScore=Score+1WHEREStuID=‘A00001’andCourseID=1UPDATESCSETScore=Score-1WHEREStuID=‘A00001’andCourseID=2事务2:INSERTINTOSTUDENTVALUES('A00004','mary',21,'男','Nanjing',90,1)UPDATEDepartmentSETTotal=Total+1WHEREDepID=1所有更新应一起来做。对于Department，如果Total值大于100，由第二个事务所产生的改变必须被回滚，而由第一个事务产生的改变是允许的。答案：beginTransactionUPDATESCSETScore=Score+1WHEREStuID='A00001'andCourseID=1UPDATESCSETScore=Score-1WHEREStuID='A00001'andCourseID=2saveTransactiontrnTransaction1INSERTINTOSTUDENTVALUES('A00013','mary',21,'男','Nanjing',90,1)UPDATEDepartmentSETTotal=Total+1WHEREDepID=1if(selectTotalfromDepartmentwhereDepID=1)>100beginRollbackTransactiontrnTransaction1commitTransactionendelsecommitTransaction任务5:在编译器中使两个事务同时执行，查看数据库情况CREATEPROCprcSellTicket@TicketInfovarchar(20),--车次信息@TicketNumint--售出张数ASBegintranDeclare@TicketLeaveNumintSelect@TicketLeaveNum=TicketLeaveNumfromTicketWhereTicketInfo=@TicketInfo--①waitfordelay'00:00:20.005'UpdateTicketsetTicketLeaveNum=@TicketLeaveNum-@TicketNumWhereTicketInfo=@TicketInfo--②If(@TicketLeaveNum<@TicketNum) beginrollbacktranprint'售票失败'endElsebeginCommittranprint'售票成功'end以上程序如果多个窗口同时执行，数据会产生错误吗？如何修改以上程序？答案：会产生错误。使用锁或设置事务的隔离级别。--加锁alterPROCprcSellTicket@TicketInfovarchar(20),--车次信息@TicketNumint--售出张数ASbegintry ifexists(select*fromTicketwhereTicketInfo=@TicketInfo) begin Begintran selectTicketLeaveNumfromTicketwith(tablockx) Declare@TicketLeaveNumint Select@TicketLeaveNum=TicketLeaveNumfromTicket WhereTicketInfo=@TicketInfo--① waitfordelay'00:00:20.000' UpdateTicketsetTicketLeaveNum=@TicketLeaveNum-@TicketNum WhereTicketInfo=@TicketInfo--② If(@TicketLeaveNum<@TicketNum) begin rollbacktran print'售票失败' end Else begin Committran print'售票成功' end end else print'信息不存在'endtrybegincatch rollbacktran print'服务器忙，售票失败'endcatch--设置事务隔离级别alterPROCprcSellTicket@TicketInfovarchar(20),--车次信息@TicketNumint--售出张数ASbegintry ifexists(select*fromTicketwhereTicketInfo=@TicketInfo) begin settransactionisolationlevelrepeatableread Begintran Declare@TicketLeaveNumint Select@TicketLeaveNum=TicketLeaveNumfromTicket WhereTicketInfo=@TicketInfo--① waitfordelay'00:00:20.000' UpdateTicketsetTicketLeaveNum=@TicketLeaveNum-@TicketNum WhereTicketInfo=@TicketInfo--② If(@TicketLeaveNum<@TicketNum) begin rollbacktran print'售票失败' end Else begin Committran print'售票成功' end end else print'信息不存在'endtrybegincatch rollbacktran print'服务器忙，售票失败'endcatch三、内容与要求课题名称——事务1（一）课题内容