create table sales(客户代号 char(5)PRIMARY KEY,数量 int null) insert into sales values ('A0001',0) alter procedure modi_m AS declare @i int declare @sl int set transaction isolation level read committed select @i=1 while(@i <=3000) begin begin tran select @sl = 数量 from sales with (tablockx) where 客户代号 = 'A0001' waitfor delay '00:00:00.002' update sales set 数量=@sl - 1 where 客户代号 = 'A0001' commit tran select @i = @i+1 end alter procedure modi_a AS declare @i int declare @sl int set transaction isolation level read committed select @i=1 while(@i <=3000) begin begin tran select @sl = 数量 from sales with (tablockx) where 客户代号 = 'A0001' waitfor delay '00:00:00.002' update sales set 数量=@sl +1 where 客户代号 = 'A0001' commit tran select @i = @i+1 end alter procedure dirt_wroll as declare @i int declare @sl int set transaction isolation level read committed select @i = 1 while(@i <=32000)begin select @i=@i+1 begin tran select @sl = 数量 from sales where 客户代号 = 'A0001' update sales set 数量= @sl+1 where 客户代号='A0001' waitfor delay '00:00:00.001' rollback tran end alter procedure dirt_r as declare @i int declare @sl int set transaction isolation level read committed select @i=1 while(@i<=120000) begin select @i = @i +1 begin tran select @sl = 数量 from sales where 客户代号 = 'A0001' if(@sl <> 0) raiserror('发生了脏读！',16,1) commit tran end alter procedure rep_r as declare @i int declare @sl int declare @s2 int set transaction isolation level repeatable read select @i=1 while(@i<=30000)begin select @i = @i+1 begin tran select @sl = 数量 from sales where 客户代号 = 'A0001' waitfor delay '00:00:00.001' select @s2 = 数量 from sales where 客户代号='A0001' if(@sl <> @s2) raiserror ('发生不可重复读！',16,1) commit tran end alter procedure rep_w as declare @i int declare @s1 int declare @s2 int set transaction isolation level repeatable read select @i = 1 while(@i<=10000) begin begin tran select @s1=数量 from sales where 客户代号 = 'A0001' waitfor delay '00:00:00.002' update sales set 数量 = @s1+1 where 客户代号 = 'A0001' commit tran select @i=@i+1 end update sales set 数量=0 where 客户代号 = 'A0001' select * from sales create procedure huany_I as declare @i int set transaction isolation level repeatable read delete from sales where(客户代号 = 'A1111') select @i = 1 while(@i<=3000) begin begin tran insert into sales (客户代号,数量) values ('A1111',10000) commit tran waitfor delay '00:00:00.001' select @i=@i+1 end create procedure huany_U as declare @i int declare @j int set transaction isolation level repeatable read delete from sales where(客户代号 = 'A1111') select @i = 1 while(@i<=300) begin begin tran update sales set 数量=数量+3 where(客户代号 = 'A1111') select @j = 0 select @j = COUNT(*) from salse where(客户代号 = 'A1111' and 数量=10000) if(@j>0) raiserror('发生了幻影！',16,1) commit tran waitfor delay '00:00:00.001' select @i=@i+1 end
