请教一下我这个存储过程有没有可能会死锁定(100)

  • 主题发起人 主题发起人 bsense
  • 开始时间 开始时间
B

bsense

Unregistered / Unconfirmed
GUEST, unregistred user!
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOALTER PROCEDURE proc_product_otherin @bill_no varchar(20)as declare @confirms int; declare @totalsum float; declare @subtotal float; declare @product_id varchar(10); declare @qty float; declare @price float; declare @house_id varchar(6); BEGIN TRANSACTION select @confirms = confirms ,@totalsum = total_sum from product_otherin where bill_no = @bill_no;--已经审核 if @confirms = 1 begin COMMIT TRANSACTION return 0; end; declare cur1 cursor for select house_id,product_id,qty,price,total_sum from product_otherin_detail where bill_no=@bill_no;--- 增加库存,修改成本 open cur1; fetch cur1 into @house_id,@product_id,@qty,@price,@subtotal ; while @@fetch_status=0 --成功读 begin exec proc_append_product @product_id,@house_id; --商品是否存在 --计算加权平均成本 -- 2009-6-13 修改 stock_qty - @qty 为 stock_qty + @qty update product_stock set cost_price = case when ( stock_qty + @qty)<>0 then abs(( cost_sum + @subtotal)/( stock_qty + @qty)) else 0 end, stock_qty = stock_qty + @qty where product_id = @product_id and house_id = @house_id ; if @@error<>0 begin ROLLBACK TRANSACTION close cur1 deallocate cur1 return -1 end--2009-6-16 重新计算成本金额 update product_stock set cost_sum = stock_qty * cost_price where product_id = @product_id and house_id = @house_id ; if @@error<>0 begin ROLLBACK TRANSACTION close cur1 deallocate cur1 return -1 end fetch cur1 into @house_id,@product_id,@qty,@price,@subtotal ; end; close cur1; deallocate cur1; --释放 --产生付款单xxx --产生 应该付款纪录xxx--审核 标志 update product_otherin set confirms=1 where bill_no = @bill_no; if @@error<>0 begin ROLLBACK TRANSACTION return -1 end COMMIT TRANSACTION GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO================delphi 调用procedure Tfrm_productotherin.sbConfirmClick(Sender: TObject);var bill_no: string;begin //没有记录 if zquerymaster.RecordCount < 1 then exit; // if zquerymaster.FieldByName('confirms').AsInteger = 1 then exit; //审核过了 if application.MessageBox('是否审核这张业务单据?提示:审核之后将不能修改。','询问',MB_ICONQuestion + MB_YESNO) = ID_NO Then exit; bill_no := zquerymaster.fieldbyname('bill_no').AsString; try ZStoredProc1.ParamByName('@bill_no').Value := bill_no; ZStoredProc1.ExecProc; showmessage('审核成功完成!'); except raise; showmessage('审核失败!'); exit; end; //refresh 会自动去掉param 打开 zquerymaster.close; zquerydetail.Close; zquerymaster.ParamByName('bill_no').AsString := bill_no; zquerydetail.ParamByName('bill_no').AsString := bill_no; zquerymaster.Open; zquerydetail.Open; inherited;end;
 
事务里面用游标,看着怪怪的感觉,个人认为有必要修改一下。
 
3x 已经优化成若干个insert,update语句
 
后退
顶部