2023年西北工业大学数据库实验报告

《数据库原理》实验报告题目：实验一数据库和表的创建与管理学号姓名班级日期孟玉军1001140216.11.10一.实验内容、环节以及结果假设学校允许学生将银行卡和校园卡进行绑定,在Student数据库中有如下的基本表,其中校园卡编号cardid即为学生的学号：。icbccard(studcardid,icbcid,balance)//校园卡ID,工行卡ID,银行卡余额campus_card(studcardid,ba1anee)//校园卡ID,校园卡余额创建数据库代码如下：usesiuden(createtablecampus_card(»studcardidChar(8),balanceDecimal(10.2))createtableicbc_card(。studcardidChar⑻，cbcidChar(10),>1anccDecimal(10.2),)示例数据如下：insertintocampus_cardvalues('2O2300317,30)insertintocampus_cardvalues(z20230032\50)settranisolationlevelreaduncommittedselect*fromcampus_cardwith(hoidlock)wherestudcarc!id=,20230032'waitfordelay*00:00：05'select*fromcampus_cardwith(holdlock)wherestudcardid='20230032'结果显示：studcanSdbalance1['^50032]50.00studcarddbalance120150032"!50.00;没有出现脏数据3).不可反复读begintransettranisolation1eve1readuncommittedec1are@blDecimal(10,2)declare@b2Decinia1(10,2)(hoidlock)seiect@bl=ba1ancefromcampus_cardwith(hoidlock)wherestudcardid='20230032

print('第一次数据：'+convert(varchar,@b1))waitfordelay'00:00:05'seiect@b2=balancefromcampus_cardwith(hold1ock)wherestudcardid='20230032'print('第一次数据：'+convert(varchar,@b2))begintranupdatecampus_cardwith(xlock)setbalance=bcilanee+100wherestudcardid='20230032'waitfordelay'00:00:03,commit结果显示:4|m因消息第一次数据：50.00第一次数据：5。.。。第一次数据：50.00B47\SQLE：,②查询已成功执行.B47\SQLE：消除了反复读错误.构造一个出现死锁的情形。（10分）事务一:

begintransactionselect*fromcampus_cardwith(tablockx)waitfordelay'00:00:05'se1ect*fromicbc_cardwith(tab1ockx)commit事务二：begintransactionseiect*fromicbc_cardwith(tabiockx)waitfordelayz00:00：05'se1ect*fromcampus_cardwith(tabiockx)s

(3s

(3消事状率56,第4行）与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。出现了死锁.运用dbcclog命令查看student数据库的事务日记。（5分）dbcclog(student)CurrentL5NOperationContextIransactionIL)Logblockbeneration1|00000019:0000001b:0001LCX.NULL0000:000002780200000019:0000001b:0002LOP_MODIFY_ROWLCX_BOOT_PA3E0000:000002780300000019:0000001b:0003LOP_MODIFY_ROWLCX_BOOT_PA3E0000:000002780400000019:0000001c:00010000:000002780500000019:0000001c:0002LOP_MODIFY_HEADERLCX_PFS0000:000000000600000019:0000001c:0003LOP_SET_BITSLCX.PFS0000:000000000700000019:0000001c:0004LOPJNSERT.ROWSLCX.CLUSTERED0000:000002780800000019:0000001c:0005LOP_SET_BITSLCX.PFS0000:000000000900000019:0000001c:00060000:0000027801000000019:0000001c:0007LCX.PFS0000:0000000001100000019:0000001c:0008LOPINSERTROWSLCXCLUSTERED0000:000002780二.实验中出现的问题以及解决方案（对于未解决问题请将问题列出来）除了标题内容以外,该部分内容中还可以写对于实验的•些感受,建议，意见等。学会了一些事务解决的操作，理解了运用锁机制、数据库的隔离级别去解决问题。批阅者：批阅日期:实验成绩:批注:insertintocampus_cardva1ues('2O230033',70)insertintoicbc_cardvalues(120230031,,u,10001insertintoicbc_cardvallies,20230032',",1000)insenintoicbc_cardva1ues('202300331000)针对以上数据库按照规定完毕下列实验：.编写一个事务解决(begintran)实现如下的操作:某学号为20230032的学生要从银行卡中转账200元到校园卡中，若中间出现故障则进行rollback。(15分)代码：usestudentbegintransactionzhuanzhanggodeclareddecima1(10,2)select@x=balancefromicbc_cardwherestudcardid='20230032，set@x=@x-200if(@x>=0)beginupdateicbc_cardsetba1ance=@xwherestudcardid=/20230032'updatecampuscardsetbalance=balanee+200wherestudcardid='20230032'conunittranendeIsebeginPrint'余额局限性，不能转账，ro11backtranend结果显示:studcardd<bddbalance0►2015003120150031011000.00201500322015003201800.002015003320150033011000.00*NULLNULLNULL>Dx/B4ASQLEXPRESS.student-dbo.icbc.cardB47\SQLEXPRESS.st^-dbo.cam]studcarddbalance50030.002015003220150033250.0070.00npD米NULLNULL.针对本题的数据库和表，分别用品体的例子展现四种数据不一致问题：丢失修改、读脏数据、不可反复读和幻读（删除和插入）。（40分，每种数据不一致10分）1）.丢失修改：执行两段代码begintransactiondeclare©adecima1（10,2）select@a=balancefromicbc_cardwherestudcardid=*20230032'waitforde1ay'00：00：05'updateiebecardsetbalance=@a+1wherestudcardid=,20230032'

conunitse1ect*fromicbc_cardbegintransactiondeclare@bdecimal(10,2)select@b=ba1ancefromicbc_cardwherestudcardid=/20230032,waitfordelay'00：00:05'updateicbccardsetba1ance=@b+2wherestudcardid='20230032'commitse1ect*fromicbc_card两段代码执行后的结果:'川s结果五消息balancebalance1000.001002.001000.00balance1000.00balance1000.001002.001000.001[26156631-]20150031012'"i?i懿35”"20150032013201500332015003301出现r丢失修改2).读脏数据:begintransactiondeclare@bdecimal(10,2)seiect@b=balaneefromicbc_cardwherestudcardid=,20230032zupdateicbc_cardsetbalance=@b*2wherestudcardid='20230032,begintransactionseieel*fromicbc_cardcommitrollbackselect*fromicbc_card结果显示:3),不可反复读：begintransactionselect*fromiebecard

begintransactiondeclare@bdccima1(10,2)se1ect@b二balaneefromicbc_cardwherestudcardid='20230032,updateicbc_cardsetbalance=@b*2wherestudcardid='20230032'commitselect*fromicbc_cardcommit结果显示:balance删除:begintransactionselect*fromiebeccirdfromiebeccirdbegintransactiondeletefromiebecardwherestudcardid='20230()32commitse1ect*fromicbccardcommit结果显示：studcardidicbddbalance插入：begintransactionselect*fromicbc_cardbegintransactioninsertintoicbc_cardvalues。20230034','',1000)commitselect*fromicbc_cardcommit结果显示:1国结果以消息1studcardidicbcidbalance1I20150031|20150031011000.0022015003320150033011000.00studcardidicbcidbalance1j20150031|20150031011000.0022015003420150034011000.003I2015003320150033011000.003.运用锁机制、数据库的隔离级别等，设计方案分别解决上述丢失修改、读脏数据和不可反复读(或者幻读)的数据不一致问题。(30分，每种数据不一致1。分，提醒可以用sp」ock系统存储过程查看当前锁状况)).丢失修改：begintransactiondec1are@bdecimal(10,2)se1ect@b=ba1aneefromicbc_cardwith(xlock)wherestuclcardid=*20230032'waitfordelay'00：0