数据库事务和锁

1、西北师范大学计算机科学与工程学院学生实验报告学号201171030116专业计算机科学与技术班级11级计师班姓名课程名称数据库应用与技术课程类型专业限选实验名称第九章 事务和锁实验目的： 事务是由一系列的数据操作命令组成，是数据库应用程序的基本逻辑单元。SQL SEVER 2005在对数据库进行操作时，通过事务来保证数据的一致性和完整性。在SQL SEVER 2005中，以事务为基本操作单位，使用锁来实现并发控制。实验内容：第一题：在score表上执行UPDATE语句的事务UP_score，并执行。use teachinggobegin Tran UP_score update dbo.sco

2、re set usually=77,final=88 where studentno='0824113307' and courseno='c05109'commit Tran UP_scorego实验结果：第二题：练习使用ROLLBACK TRANSLATION 语句回滚事务，并查看。use teachinggobegin Tran insert into course values('c05109','国际贸易','必修',32,2.0); save Tran save1; declare from cours

3、e where courseno='c05137' rollback tran save1;commit Trango实验结果：第三题：练习在student表上创建嵌套事务，分别在内层和外层设置回滚点，检测回滚对表数据的影响。use teachinggobegin Tran Tran1 save Tran save1; begin Tran Tran2 update student set sname='赵平茵',point=997,phone='053179372727' where studentno='0828261367'

4、 select * from student where studentno='0828261367' save tran save2; update student set sname='孙释远',point=987,phone='053179372727' where studentno='082826367'rollback Tran save1;commit Tran Tran1;select * from student where studentno='082826367'go实验结果：第四题：练习在s

5、tudent表上创建嵌套事务，并利用系统变量TRANCOUNT编程，检测嵌套事务的执行情况。use teachinggobegin tran tr1print '1st begin tran:trancount=' +cast(trancount as nvarchar(10);save tran save1;begin tran tr2 print '2nd begin tran:trancount=' +cast(trancount as nvarchar(10); update student set sname='赵平茵',point=9

6、97,phone='053179372727' where studentno='0828261367'commit tran tr2;print '1st commit tran:trancount=' +cast(trancount as nvarchar(10);rollback tran save1;print 'rollback tran:trancount=' +cast(trancount as nvarchar(10);commit tran tr1;print 'after commit tran tr1:trancount=' +cast(trancount as nvarchar(10);select * from student where studentno='0828261367'go实验结果：第五题：练习