西北工业大学数据库实验报告DB7.docx

西北工业大学数据库实验报告DB7.docx

《西北工业大学数据库实验报告DB7.docx》由会员分享，可在线阅读，更多相关《西北工业大学数据库实验报告DB7.docx（17页珍藏版）》请在冰豆网上搜索。

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

《数据库原理》实验报告

题目：

实验七:

事务与并发控制

学号

姓名

班级

日期

一、实验内容、步骤以及结果

假设学校允许学生将银行卡和校园卡进行绑定，在student数据库中有如下的基本表，其中校园卡编号cardid即为学生的学号：

icbc_card（studcardid,icbcid,balance）//校园卡ID，工行卡ID，银行卡余额

campus_card（studcardid,balance）//校园卡ID，校园卡余额

数据库创建的代码：

usestudent

createtablecampus_card

（studcardidChar（8）,

balanceDecimal（10,2））

createtableicbc_card

（

studcardidChar（8）,

icbcidChar（10）,

balanceDecimal（10,2）

）

执行结果：

示例数据插入代码：

insertintocampus_cardvalues（'20150031',30）

insertintocampus_cardvalues（'20150032',50）

insertintocampus_cardvalues（'20150033',70）

insertintoicbc_cardvalues（'20150031','2015003101',1000）

insertintoicbc_cardvalues（'20150032','2015003201',1000）

insertintoicbc_cardvalues（'20150033','2015003301',1000）

插入结果：

针对以上数据库按照要求完成下列实验：

1.编写一个事务处理（begintran）实现如下的操作：

某学号为20150032的学生要从银行卡中转账200元到校园卡中，若中间出现故障则进行rollback。

（15分）

usestudent

begintransactionrecharge

go

declare@xdecimal（10,2）

select@x=balance

fromicbc_card

wherestudcardid='20150032'

set@x=@x-200

if（@x>=0）

begin

updateicbc_cardsetbalance=@xwherestudcardid='20150032'

updatecampus_cardsetbalance=balance+200wherestudcardid='20150032'

committran

end

else

beginprint'余额不足，不能转账'

rollbacktran

end

2.针对本题的数据库和表，分别用具体的例子展现四种数据不一致问题：

丢失修改、读脏数据、不可重复读和幻读（删除和插入）。

（40分，每种数据不一致10分）

1.丢失修改：

事务1：

（把学号为20160031的银行卡金额加一元，中间增加5秒的延时，在延时过程中，执行事件2）

begintransaction

declare@adecimal（10,2）

select@a=balance

fromicbc_cardwherestudcardid='20150031'

waitfordelay'00:

00:

05'

updateicbc_card

setbalance=@a+1wherestudcardid='20150031'

commit

select*fromicbc_card

事务2：

（把学号为20160031的银行卡金额加两元，中间没有延时）

begintransaction

declare@bdecimal（10,2）

select@b=balance

fromicbc_cardwherestudcardid='20150031'

updateicbc_card

setbalance=@b+2wherestudcardid='20150031'

commit

select*fromicbc_card

事务1执行结果：

事务2执行结果：

事务1代码中间有延时5s，在这5s内，执行事务2，事务2执行完成后会丢失事务1的执行结果，最终结果不为1000+1+2=1003，而为1002。

2.读脏数据：

事务1：

（给卡号为20150031的学生卡余额加500，之后延时10s再回滚）

settranisolationlevelreaduncommitted

begintran

updateicbc_card

setbalance=balance+500

wherestudcardid='20150031'

selectbalance

fromicbc_card

wherestudcardid='20150031'

waitfordelay'00:

00:

10'

rollback;

select*fromicbc_card

事务2：

（在事务1的延时过程中，对卡号为20150031的卡余额进行查询）

settranisolationlevelreaduncommitted

selectbalance

fromicbc_card

wherestudcardid='20150031'

事务1执行结果：

（回滚之前余额为1501，回滚后余额为1001）

事务2执行结果：

（读到了事务1回滚前的脏数据，结果是1501）

在事务1延时的10s内执行事务2,事务2执行结果为1501，不为1001，说明读了脏数据。

3.不可重复读：

事务1：

（对卡号为20150031的卡余额进行两次读，中间有5s延时）

declare@bint

select@b=balance

fromicbc_card

wherestudcardid='20150031'

print@b

waitfordelay'00:

00:

05'

select@b=balance

fromicbc_card

wherestudcardid='20150031'

print@b

事务2：

（在事务1延时过程中执行事务2，对卡号为20150031的卡余额进行修改）

updateicbc_card

setbalance=balance+300

wherestudcardid='20150031'

事务1执行结果：

两次读出的数据不一致，说明不可重复读。

4.幻读：

1）插入：

事务1：

（对卡号为20150031的卡余额进行两次查询，中间有10s延时）

selectbalance

fromicbc_card

wherestudcardid='20150031'

waitfordelay'00:

00:

10'

selectbalancefromicbc_card

wherestudcardid='20150031'

事务2：

（向icbc_card表插入卡号为20150031的卡数据）

insertintoicbc_card

values（'20150031','2015003101',8000）

事务1执行结果：

两次查询的数据不一致，出现了幻读现象。

2）删除：

事务1：

（对卡号为20150031的卡余额进行两次查询，中间有10s延时）

selectbalance

fromicbc_card

wherestudcardid='20150031'

waitfordelay'00:

00:

10'

selectbalancefromicbc_card

wherestudcardid='20150031'

事务2：

（删除icbc_card表中卡号为20150031的数据）

deletefromicbc_card

wherestudcardid='20150031'

事务1执行结果：

两次查询的数据不一致，出现了幻读现象。

3.利用锁机制、数据库的隔离级别等，设计方案分别解决上述丢失修改、读脏数据和不可重复读（或者幻读）的数据不一致问题。

（30分，每种数据不一致10分，提示可以用sp_lock系统存储过程查看当前锁状况）

（1）丢失修改：

事务1：

（对金额进行修改时增加写锁）

begintransaction

declare@adecimal（10,2）

select@a=balance

fromicbc_cardwith（xlock）

wherestudcardid='20150031'

waitfordelay'00:

00:

05'

updateicbc_card

setbalance=@a+1wherestudcardid='20150031'

commit

select*fromicbc_card

事务2：

（对金额进行修改时增加写锁）

begintransaction

declare@bdecimal（10,2）

select@b=balance

fromicbc_cardwith（xlock）

wherestudcardid='20150031'

updateicbc_card

setbalance=@b+2wherestudcardid='20150031'

commit

select*fromicbc_card

事务1执行结果：

事务2执行结果：

事务1，2均执行完后，数据库中数据最终变为1003.解决丢失修改问题。

（2）读脏数据：

事务1：

（给卡号为20150031的学生卡余额加500，更新数据添加写锁，延时5s再回滚）

settranisolationlevelreaduncommitted

begintran

updateicbc_cardwith（xlock）

setbalance=balance+500

wherestudcardid='20150031'

selectbalance

fromicbc_card

wherestudcardid='20150031'

waitfordelay'00:

00:

05'

rollback;

select*fromicbc_card

事务2：

（在事务1的延时过程中，对卡号为20150031的卡余额进行查询，添加holdlock）

settranisolationlevelreaduncommitted

selectbalance

fromicbc_cardwith（holdlock）

wherestudcardid='20150031'

事务1执行结果：

（回滚之前余额为1501，回滚后余额为1003）

事务2执行结果：

（结果为回滚后最终的1003）

没有出现脏数据。

3.不可重复读：

事务1：

（对卡号为20150031的卡余额进行两次读，中间有5s延时）

begintran

declare@bint

select@b=balance

fromicbc_cardwith（holdlock）

wherestudcardid='20150031'

print@b

waitfordelay'00:

00:

05'

select@b=balance

fromicbc_cardwith（holdlock）

wherestudcardid='20150031'

print@b

commit

事务2：

（在事务1延时过程中执行事务2，对卡号为20150031的卡余额进行修改）

settranisolationlevelreadcommitted

updateicbc_cardwith（xlock）

setbalance=balance+300

wherestudcardid='20150031'

事务1执行结果：

两次读出的数据一致，说明解决了不可重复读。

4.构造一个出现死锁的情形。

（10分）

事务1：

（首先加写锁对校园卡表的数据进行修改，延时5s后加共享锁对银行卡表数据进行查询）

begintran

updatecampus_cardwith（xlock）

setbalance=balance+100

waitfordelay'00:

00:

05'

select*

fromicbc_card

with（holdlock）

rollbacktran

事务2：

（首先加写锁，对银行卡表数据进行修改，延时5s后加共享锁对校园卡表数据进行查询）

begintran

updateicbc_card

with（xlock）

setbalance=balance+200

waitfordelay'00:

00:

05'

select*fromcampus_card

with（holdlock）

rollbacktran

事务1运行结果：

事务2运行结果:

5.利用dbcclog命令查看student数据库的事务日志。

（5分）

dbcclog（student,4）

二、实验中出现的问题以及解决方案（对于未解决问题请将问题列出来）除了标题内容以外，该部分内容中还可以写对于实验的一些感受，建议，意见等。

在解决不可重复读错误时，事务2执行一直无法完成，认为可能是事务1加锁后事务2一直处于等待状态。

经过这次实验，对数据库锁的类型，粒度有了更清晰深刻的认识，对数据库并发查询的各种错误有了更深刻的认识，更加深刻的理解了死锁的原因。

批阅者：

批阅日期：

实验成绩：

批注：