数据库--安全性

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