MSSQL 存储过程加了事务,为什么还会同时执行。 10C

MSSQL 存储过程加了事务,为什么还会同时执行。求大神指导。
我希望是执行完一次再执行一次,可是现在会两次同时执行。

ALTER PROCEDURE [dbo].sp_in_post,
@user nvarchar,
@errcode [int] OUTPUT,
@errtxt char OUTPUT
)
AS
set @errcode=0;
declare @stock nvarchar(10) ,@company nvarchar(50),@type nvarchar(10),@location nvarchar(10),@lot nvarchar(30)
declare @odate date,@cursor_row int,@fyt_pn nvarchar(30),@cus_code nvarchar(30)
declare @issqty_mo2 int ,@qty int,@user_name nvarchar(30),@new_lot nvarchar(30),@statues nvarchar(30)
declare @goods_type nvarchar(10)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
begin transaction tran_unpublish_db
--判断单据是否确认
if exists(select * from dbo.MT_In_head where dno=rtrim(@dno) and ([status]='已复核' or ISNULL(review_by,'')<>''))

begin
set @errcode=1
set @errtxt='该单据已复核!!'
end
if not exists(select * from MT_In_detail where dno=rtrim(@dno))
begin
set @errcode=11
set @errtxt='该单据不存在数据,不能复核!'
end
if exists(select * from MT_In_detail where dno=rtrim(@dno) and ISNULL(qty,0)=0)
begin
set @errcode=11
set @errtxt='该单据存在数量为0数据,不能复核!'
end
if @errcode=0
begin
select @stock=stock,@company=company,@cus_code=cus_code,@goods_type=rtrim(goods_type),@statues=status from MT_In_head where dno=rtrim(@dno)

if @goods_type='货物' and @errcode=0
begin
    --先判断是否启用供应商批号
    if exists(select * from MT_Customer where code=rtrim(@cus_code) AND IsAction='1')
    begin
        if exists(select * from MT_In_detail where dno=rtrim(@dno) AND ISNULL(LOT,'')='')
        begin
            set @errcode=13
            set @errtxt='该供应商启用了供应商批号,请先录入!'
        end
    end
    else
    begin
        exec dbo.sp_get_doucno @cus_code,'批号',@new_lot output
        update MT_In_detail set lot=@new_lot where dno=rtrim(@dno)
        if @@error<>0
        begin
            set @errcode=14
            set @errtxt='更新库存表出错!'
        end 
    end
end



--循环更新
declare MT_In_head scroll cursor
for
    select fyt_pn,qty,(case when isnull(@new_lot,'')='' then lot else @new_lot end),isnull(location,'') from MT_In_detail where dno=rtrim(@dno)
OPEN MT_In_head
set @cursor_row=@@CURSOR_ROWS 
    close MT_In_head
    if @cursor_row=0
    begin
        set @errcode=2
        set @errtxt='游标没数据!'
    end
    OPEN MT_In_head 
FETCH FIRST FROM MT_In_head INTO @fyt_pn,@qty,@lot,@location
while @@FETCH_STATUS = 0 and @errcode=0
begin
    if @goods_type='货物'
    begin
        ---如果库存表存在则更新,不存在则插入
        if exists(select * from MT_Inventory where company=@company and stock=@stock and fyt_pn=@fyt_pn and lot=@lot  and isnull(location,'')=@location)    
        begin
            update MT_Inventory set qty=qty+@qty where company=@company and stock=@stock and fyt_pn=@fyt_pn and lot=@lot and isnull(location,'')=@location
            if @@error<>0
            begin
                set @errcode=3
                set @errtxt='更新库存表出错!'
                break 
            end         
        end
        else
        begin
            insert into MT_Inventory(id,company,stock,location,fyt_pn,qty,lot)
            values (newid(),@company,@stock,@location,@fyt_pn,@qty,@lot)
            if @@error<>0
                begin
                    set @errcode=4
                    set @errtxt='插入库存表出错!'
                    break 
                end
        end 
    end
    else
    begin
        ---如果库存表存在则更新,不存在则插入
        if exists(select * from MT_Inventory where company=@company and stock=@stock and fyt_pn=@fyt_pn )   
        begin
            update MT_Inventory set qty=qty+@qty where company=@company and stock=@stock and fyt_pn=@fyt_pn
            if @@error<>0
            begin
                set @errcode=3
                set @errtxt='更新库存表出错!'
                break 
            end         
        end
        else
        begin
            insert into MT_Inventory(id,company,stock,fyt_pn,qty,lot)
            values (newid(),@company,@stock,@fyt_pn,@qty,'')
            if @@error<>0
                begin
                    set @errcode=4
                    set @errtxt='插入库存表出错!'
                    break 
                end
        end 
    end 
    FETCH NEXT FROM MT_In_head INTO @fyt_pn,@qty,@lot,@location
end
close MT_In_head 
deallocate MT_In_head 
if @errcode=0
begin   
    select @user_name=RealName from Base_User where Account=@user

    update MT_In_head set review_by=@user,review_by_username=@user_name,review_dt=getdate(),status='已复核' where dno=rtrim(@dno)        
    if @@error<>0
    begin
        set @errcode=5          
        set @errtxt='复核失败!'           
    end 

end
if @goods_type='货物' and @errcode=0
begin
    exec sp_IN_post_rongqi @dno,@errcode,@errtxt
end

end
insert into in_log(gtime,dno,statues,r_by,ecode,etxt)values(GETDATE(),@dno,@statues,@user,@errcode,@errtxt)

if @errcode=0
commit transaction tran_unpublish_db
else
rollback transaction tran_unpublish_db
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

4个回答

你有没有关闭MySQl数据库的自动提交属性

qq_42384465
qq_42384465 是MSSQL 不是MySQl数据库呢。请问自动提交属性是在哪里?
大约 2 年之前 回复

是MSSQL 不是MySQl数据库呢。请问自动提交属性是在哪里?

你在使用事物的时候,有一点还忽略了,只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行,一般SELECT ... 不会受到同步影响。

qq_42384465
qq_42384465 能不能详细说下我这应该怎么去改。我是新手,我对这个还不是很了解。
大约 2 年之前 回复

跟你举个例子吧:
如果你执行下面这样的语句:

begin tran
SELECT * FROM [Table];
waitfor delay '00:00:20';
commit tran

然后再执行

 SELECT * FROM [Table];

你会发现第二个SQL语句并没有阻塞,这是因为没有在查询数据的时候把数据锁住。
接下来我们使用表级别锁:

    begin tran
    SELECT * FROM [Table] WITH (TABLOCKX);
    waitfor delay '00:00:20';
    commit tran;

会发现其余对该表的操作都要等待该操作完成后,才可以继续执行。

qq_42384465
qq_42384465 事务不是自动会锁表的吗?需要加这个?
大约 2 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问