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
你有没有关闭MySQl数据库的自动提交属性
是MSSQL 不是MySQl数据库呢。请问自动提交属性是在哪里？
你在使用事物的时候，有一点还忽略了，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般SELECT ... 不会受到同步影响。
跟你举个例子吧：
如果你执行下面这样的语句：
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;
会发现其余对该表的操作都要等待该操作完成后，才可以继续执行。