成本核算程式執行某個存儲過程一直阻塞,排查發現類似以下語句阻塞:
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
同樣的程式、存儲過程其它工廠核算時沒有問題,即其它資料庫沒有出現阻塞,隻在這個資料庫阻塞,可能跟資料庫設定有關,原理需要再研究。