主题：事务隔离级别的练习

可以同时打开两个查询分析器窗体模拟多个事务并发运行

练习1——未提交读 连接1 use students go

连接2 use students go

select * from stu where name='黎明' set transaction isolation level read uncommitted go

连接1 begin tran update stu set name='黎明' go

连接2 select * from stu where name='黎明'

连接1 rollback tran go

连接2 select * from stu where name='黎明'

练习2——提交读 连接1 use students go

连接2 use students go

select * from stu where name='黎明' set transaction isolation level read committed go

连接1 begin tran update stu set name='黎明' go

连接2 select * from stu where name='黎明'

连接1 rollback tran go

连接2 select * from stu where name='黎明'

练习3——有问题的提交读（两次读取的东西不一样）和可重复读 连接1 use students go

连接2 use students go update stu set name='JIM' set transaction isolation level read committed go

begin tran select * from stu where name='黎明' go

连接1 update stu set name='黎明' go

连接2 select * from stu where name='黎明' go commit tran go

－－－－－－－－－－可重复读－－－－－－－－－－－ 连接2 set transaction isolation level repeatable read go

begin tran select * from stu where name='黎明' go

连接1 update stu set name='JIM' go

连接2 select * from stu where name='黎明' go

连接2 commit tran go select * from stu where name='黎明' select * from stu where name='JIM'

练习4——有问题的可重复读和可串行化 连接1 use students go

连接2 use students go set transaction isolation level repeatable read go begin tran select * from stu where name like '李%' go

连接1 insert stu values('000111','李树青',1,'1999-9-9',1.76)

连接2 select * from stu where name like '李%' go

－－－－－－－－－－串行化－－－－－－－－－－－（将新的插入记录删除） 连接2 commit tran set transaction isolation level serializable go

begin tran select * from stu where name like '李%' go

连接1 insert stu values('000111','李树青',1,'1999-9-9',1.76)

连接2 select * from stu where name like '李%' go

连接2 commit tran go

连接2 select * from stu where name like '李%' go