成本核算程式執行某個存盤程序一直阻塞，排查發現類似以下陳述句阻塞：
select tbl1.product_id,sum(isnull(tbl1.qty,0) * isnull(tbl2.unit_other_cost,0)) as other_cost from tbl1.p_id=@pId and tbl1.if_stock=0 and exists(select 1 from tbl3 inner join tbl4 on tbl3.c1=tbl4.c2 where tbl4.c3=0 and tbl3.p_id=tbl1.p_id and tbl3.product_id=tbl1.product_id) group by tbl1.product_id
其中引數@pId是存盤程序的傳入引數，測驗發現將條件中的@pId改成具體的值，直接執行SQL陳述句也會阻塞，但是加上變數定義就不會了：
declare @newpId int set @newpId=99 select tbl1.product_id,sum(isnull(tbl1.qty,0) * isnull(tbl2.unit_other_cost,0)) as other_cost from tbl1.p_id=@newpId and tbl1.if_stock=0 and exists(select 1 from tbl3 inner join tbl4 on tbl3.c1=tbl4.c2 where tbl4.c3=0 and tbl3.p_id=tbl1.p_id and tbl3.product_id=tbl1.product_id) group by tbl1.product_id
最終決定存盤程序里重新定義個變數，賦值為傳入引數，將重新定義的變數作為條件值，問題解決，
declare @newpId int set @newpId=@pId select tbl1.product_id,sum(isnull(tbl1.qty,0) * isnull(tbl2.unit_other_cost,0)) as other_cost from tbl1.p_id=@newpId and tbl1.if_stock=0 and exists(select 1 from tbl3 inner join tbl4 on tbl3.c1=tbl4.c2 where tbl4.c3=0 and tbl3.p_id=tbl1.p_id and tbl3.product_id=tbl1.product_id) group by tbl1.product_id
同樣的程式、存盤程序其它工廠核算時沒有問題，即其它資料庫沒有出現阻塞，只在這個資料庫阻塞，可能跟資料庫設定有關，原理需要再研究，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/509066.html
標籤：SQL Server