實驗：事務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（一）課題內容