我在事务锁定方面遇到了一个奇怪的问题，我无法指出问题来自哪里。
我们的客户报告说，他们无法使用系统的一页，并且超时了。此页执行一个存储过程，该过程涉及 CTE，但根本不涉及任何事务。请参阅下面的存储过程：
ALTER PROCEDURE [dbo].[X_WMS_GET_SALESORDERS_CUSTOM]
@SelectedLocations VARCHAR(100) = null,
@FromDueDate DateTime= NULL,
@ToDueDate DateTime = NULL,
@SearchTerm VARCHAR(200) = NULL,
-- Dynamic filters
-- * Display name will be the parameter name without the @FILTER_ and underscores(_) are replaced by white space( ).
-- * BIT datatype is the equivalent of boolean or char(1) 'Y' or 'N'. This will display a Yes/No dropdown selection on UI.
@FILTER_Order_Date_from DATETIME = NULL,
@FILTER_Order_Date_to DATETIME = NULL,
@FILTER_Account VARCHAR(200) = NULL,
@FILTER_Locked BIT = NULL,
@FILTER_Stocks_Ready BIT = NULL,
@FILTER_Zones VARCHAR(MAX) = NULL,
-- Pagination parameters
@START INT = 0,
@LENGTH INT = 0, -- // 0 = ALL
@SEARCH VARCHAR(250) = '',
@SORTCOLUMN VARCHAR(250) = 'Order No',
@SORTDIRECTION INT = 0 -- // ASC = 0, DESC <> 0
AS
BEGIN
DECLARE @ZONEDESC VARCHAR(50)
SELECT @ZONEDESC = X_DESCRIPTION FROM X_WMS_ZONES WHERE X_ZONE_ID = @FILTER_Zones;
-- select * from X_WMS_SALESORD_VW
WITH TEMPCTE_RESULTS as (
SELECT '' AS [__HiddenColumns__|ACCNO|LOCNO|]
,SH.SEQNO AS [Order No]
,DR.ACCNO
,DR.NAME AS Account
,SL.LOCATION as [LOCNO]
,MAX(SLOC.LNAME) AS Location
,SH.ORDERDATE AS [Order Date]
,SH.DUEDATE AS [Due Date]
,CASE WHEN RECORDID IS NULL THEN 'N' ELSE 'Y' END AS [Locked]
,CASE WHEN SUM(ISNULL(BA.SOH,0) - ISNULL(BA.TOPICK, 0)) > 0 THEN 'Y' ELSE 'N' END AS [Stocks Ready]
,STUFF((SELECT ', ' + _XZ.X_DESCRIPTION
FROM SALESORD_LINES _SL
LEFT JOIN (SELECT * from(select *, ROW_NUMBER() OVER (PARTITION BY StockCode ORDER BY Preference ASC) AS rn FROM X_WMS_BIN_ASSIGNMENT WITH(NOLOCK)) BAIN where rn=1 ) _BA ON _BA.STOCKCODE = _SL.STOCKCODE
LEFT JOIN dbo.X_WMS_BIN_LOCS AS _BL WITH(NOLOCK) ON _BL.BINID = _BA.BINID
LEFT JOIN dbo.X_WMS_ZONES AS _XZ WITH(NOLOCK) ON _XZ.X_ZONE_ID = _BL.ZONEID
WHERE (_SL.HDR_SEQNO = SH.SEQNO)
GROUP BY _XZ.X_DESCRIPTION
ORDER BY _XZ.X_DESCRIPTION FOR XML PATH(''), TYPE ).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, '') AS Zones
,ROW_NUMBER() OVER(ORDER BY
CASE WHEN @SORTCOLUMN = 'SEQNO' AND @SORTDIRECTION = 0 THEN SH.SEQNO END ASC,
CASE WHEN @SORTCOLUMN = 'SEQNO' AND @SORTDIRECTION <> 0 THEN SH.SEQNO END DESC,
CASE WHEN @SORTCOLUMN = 'NAME' AND @SORTDIRECTION = 0 THEN DR.NAME END ASC,
CASE WHEN @SORTCOLUMN = 'NAME' AND @SORTDIRECTION <> 0 THEN DR.NAME END DESC,
CASE WHEN @SORTCOLUMN = 'LOCNAME' AND @SORTDIRECTION = 0 THEN MAX(SLOC.LNAME) END ASC,
CASE WHEN @SORTCOLUMN = 'LOCNAME' AND @SORTDIRECTION <> 0 THEN MAX(SLOC.LNAME) END DESC,
CASE WHEN @SORTCOLUMN = 'ORDERDATE' AND @SORTDIRECTION = 0 THEN SH.ORDERDATE END ASC,
CASE WHEN @SORTCOLUMN = 'ORDERDATE' AND @SORTDIRECTION <> 0 THEN SH.ORDERDATE END DESC,
CASE WHEN @SORTCOLUMN = 'DUEDATE' AND @SORTDIRECTION = 0 THEN SH.DUEDATE END ASC,
CASE WHEN @SORTCOLUMN = 'DUEDATE' AND @SORTDIRECTION <> 0 THEN SH.DUEDATE END DESC,
CASE WHEN @SORTCOLUMN = 'LOCKED' AND @SORTDIRECTION = 0 THEN CASE WHEN RECORDID IS NULL THEN 'N' ELSE 'Y' END END ASC,
CASE WHEN @SORTCOLUMN = 'LOCKED' AND @SORTDIRECTION <> 0 THEN CASE WHEN RECORDID IS NULL THEN 'N' ELSE 'Y' END END DESC,
CASE WHEN @SORTCOLUMN = 'STOCKS READY' AND @SORTDIRECTION = 0 THEN CASE WHEN SUM(ISNULL(BA.SOH,0) - ISNULL(BA.TOPICK, 0)) > 0 THEN 'Y' ELSE 'N' END END ASC,
CASE WHEN @SORTCOLUMN = 'STOCKS READY' AND @SORTDIRECTION <> 0 THEN CASE WHEN SUM(ISNULL(BA.SOH,0) - ISNULL(BA.TOPICK, 0)) > 0 THEN 'Y' ELSE 'N' END END DESC,
SH.SEQNO ASC
) AS ROWNUM
,COUNT(*) OVER() AS TotalCount
,COUNT(*) OVER() AS TotalFilteredCount
FROM dbo.SALESORD_HDR AS SH
INNER JOIN dbo.DR_ACCS AS DR WITH(NOLOCK) ON DR.ACCNO = SH.ACCNO
INNER JOIN dbo.SALESORD_LINES AS SL WITH(NOLOCK) ON SL.HDR_SEQNO = SH.SEQNO AND SL.UNSUP_QUANT > 0
INNER JOIN STOCK_ITEMS SI WITH(NOLOCK) ON SI.STOCKCODE = SL.STOCKCODE
LEFT OUTER JOIN dbo.OBJECT_LOCK WITH(NOLOCK) ON dbo.OBJECT_LOCK.OBJECT_ID = 'Order' AND dbo.OBJECT_LOCK.RECORDID = SH.SEQNO
LEFT OUTER JOIN dbo.X_WMS_PICK_DETAILS PD WITH(NOLOCK) ON PD.ORDERNO = SL.HDR_SEQNO AND PD.SOLINEID = SL.SOLINEID
INNER JOIN dbo.STOCK_LOCATIONS AS SLOC WITH(NOLOCK) ON SLOC.X_WMS_USE = 1 AND SLOC.LOCNO = SL.LOCATION
--LEFT OUTER JOIN dbo.X_WMS_SALESORD_LINES_STOCKQTYAVAIL_VW AS SOLSQA ON SOLSQA.HDR_SEQNO = SH.SEQNO
LEFT OUTER JOIN (SELECT * from(select *, ROW_NUMBER() OVER (PARTITION BY StockCode ORDER BY Preference ASC) AS rn FROM X_WMS_BIN_ASSIGNMENT WITH(NOLOCK)) BAIN where rn=1 ) BA ON BA.STOCKCODE = SL.STOCKCODE
INNER JOIN X_WMS_BIN_LOCS BL WITH(NOLOCK) ON BL.BINID= BA.BINID AND BL.LOCATION = SL.LOCATION
WHERE 1=1
AND (SH.STATUS IN (0, 1))
AND (SH.ONHOLD <> 'Y')
AND (SH.LAST_UPDATED < DATEADD(SECOND, - 5, GETDATE()))
AND (SL.ORD_QUANT > 0)
AND (SL.LINETYPE <> 2)
AND (SL.STOCKCODE <> SL.LINKED_STOCKCODE or (SI.STATUS <> 'L'))
AND (@SelectedLocations='0' OR @SelectedLocations IS NULL OR @SelectedLocations = '' OR SL.LOCATION IN (SELECT ID FROM StringToIntList(@SelectedLocations,',')))
AND (@FromDueDate IS NULL OR SH.DUEDATE >= @FromDueDate)
AND (@ToDueDate IS NULL OR SH.DUEDATE <= @ToDueDate)
AND (@SearchTerm IS NULL
OR CONVERT(VARCHAR, SH.SEQNO) LIKE '%' + @SearchTerm + '%'
OR CONVERT(VARCHAR,DR.ACCNO) LIKE '%' + @SearchTerm + '%'
OR DR.NAME LIKE '%' + @SearchTerm + '%')
--AND SL.LOCATION IN( SELECT LOCNO FROM STOCK_LOCATIONS WHERE X_WMS_USE = 1 and X_WMS_INCLUDEONPICK= 1)
-- Dynamic filters
AND (@FILTER_Order_Date_from IS NULL OR SH.ORDERDATE >= @FILTER_Order_Date_from)
AND (@FILTER_Order_Date_to IS NULL OR SH.ORDERDATE <= @FILTER_Order_Date_to)
AND (@FILTER_Account IS NULL OR DR.NAME LIKE '%' + @FILTER_Account + '%')
AND (@FILTER_Locked IS NULL OR CASE WHEN RECORDID IS NULL THEN 0 ELSE 1 END = @FILTER_Locked)
--AND (@ZONES IS NULL OR SLSZ.STOCKZONES LIKE '%' + @ZONEDESC + '%')
AND (PD.PICKQTY IS NULL OR(PD.PICKQTY < SL.ORD_QUANT ) OR (PD.PICKQTY=0 and PD.QTYREGISTER=0))
GROUP BY SH.SEQNO, DR.ACCNO, DR.NAME, SL.LOCATION, SH.ORDERDATE, SH.DUEDATE, dbo.OBJECT_LOCK.RECORDID
HAVING (SUM(SL.ORD_QUANT) - SUM(SL.SUP_QUANT) - SUM(SL.BKORD_QUANT) - SUM(ISNULL(PD.PICKQTY, 0)) > 0)
AND (@FILTER_Stocks_Ready IS NULL OR CASE WHEN SUM(ISNULL(BA.SOH,0) - ISNULL(BA.TOPICK, 0)) > 0 THEN 1 ELSE 0 END = @FILTER_Stocks_Ready)
)
--
SELECT _TR.*
INTO #TMP
FROM TEMPCTE_RESULTS _TR
WHERE 1=1
AND (ISNULL(@LENGTH, 0) = 0 OR ROWNUM BETWEEN @START + 1 AND (@START + @LENGTH))
ORDER BY ROWNUM
IF(ISNULL(@FILTER_Zones,'') != '')
BEGIN
UPDATE #TMP SET ZONES = @ZONEDESC
END
SELECT * FROM #TMP
DROP TABLE #TMP
END
当页面仍然关闭时，我尝试在 SQL 管理上手动运行存储过程，并在 1 或 2 秒内执行，没有问题。我正在使用 ASP.Net MVC 来显示此存储过程的结果。
我重新启动了服务器，但问题仍然存在。 我能够解决它的唯一方法是修改存储过程上的任何行，执行它并撤消更改并再次执行，然后它神奇地解决了问题。
有人遇到过这种情况吗？任何帮助将不胜感激，因为我被要求明天报告刚刚发生的事情，我需要在我们的会议上说些什么:(
蒂亚
- 在某些地方，您使用了"选择 *"，仅选择必填列。2.从页面命中查询时，从 SQL 探查器或活动监视器监视查询。3.如果遇到死锁，请从 SQL 探查器进行监视以了解确切原因。