sql-server-2012 - 为存储过程实现队列
问题描述
我在 SQL Server 中有一个基本的 SELECT 存储过程,它连接到 MS Access 前端。当多个用户尝试运行相同的存储过程(即 ODBC--调用失败或表当前被锁定)时,用户会收到表锁定。有没有办法实现队列或防止这种情况发生。
我尝试将服务器移近一点,看看是否是延迟问题,并尝试使用 OPTION (MAXDOP 1) 以防是处理器问题。
(@username as varchar(20),
@searchcriteria as varchar(50)
)
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--SET @username = 'caddo';
--SET @searchcriteria = 9;
--SET @portfolio = 'WH – Apollo';
--PRINT '%'+ RIGHT(CAST(@situsloanid as varchar(10)),@loanLength) + '%'
SELECT tblPortfolio.PortfolioID,
tblAssets.AssetID,
tblAssets.ModelEntity,
tblPortfolio.Portfolio,
tblAssets.SitusLoanID,
tblEmployees.Last AS AMIntials,
tblEmployees_1.Last AS TeamLead,
tblStatus.StatusType, tblEmployees.NetworkUserName, tblRegion.ID, tblRegion.Region,
IIf(1=1 Or 0=1,1,IIf(@username=[tblEmployees].[NetworkUserName] Or @username=[tblEmployees_1].[NetworkUserName] Or @username=[tblEmployees_2].[NetworkUserName] Or @username=[tblEmployees_3].[NetworkUserName] Or @username=[tblEmployees_4].[NetworkUserName] Or @username=[tblEmployees_5].[NetworkUserName],1,0)) AS [Select],
tblEmployees_1.NetworkUserName, tblEmployees_2.Last AS PMLead, tblEmployees_3.Last AS Analyst, tblEmployees_4.Last AS Admin, tblEmployees_5.Last AS ComplianceLead, tblAssets.AssetName,
tblAssets.BorrowerName, tblEmployees.compliance AS ComplianceBool, tblAssets.SitusLoanID, tblAssets.Alias
FROM tblAssets LEFT JOIN tblEmployees AS tblEmployees_5 ON tblAssets.ComplianceID = tblEmployees_5.LoginID
LEFT JOIN tblEmployees AS tblEmployees_3 ON tblAssets.AnalystID = tblEmployees_3.LoginID
LEFT JOIN tblEmployees AS tblEmployees_2 ON tblAssets.PMLeaderID = tblEmployees_2.LoginID
LEFT JOIN tblEmployees AS tblEmployees_1 ON tblAssets.AMLeaderID = tblEmployees_1.LoginID
LEFT JOIN tblEmployees ON tblAssets.AssetMngrID = tblEmployees.LoginID
LEFT JOIN tblStatus ON tblAssets.PipelineStatusID = tblStatus.StatusID
LEFT JOIN tblEmployees AS tblEmployees_4 ON tblAssets.ServicerContactID = tblEmployees_4.LoginID
LEFT JOIN tblPortfolio ON tblAssets.PortfolioID = tblPortfolio.PortfolioID
LEFT JOIN tblRegion ON tblAssets.RegionId = tblRegion.ID
WHERE (1=1 )
AND (tblPortfolio.Portfolio Like '%'+ @searchcriteria + '%'
OR tblAssets.SitusLoanID Like '%'+ @searchcriteria + '%'
OR tblAssets.BorrowerName Like '%'+ @searchcriteria + '%'
OR tblEmployees_2.Last Like '%'+ @searchcriteria + '%'
OR tblAssets.AssetName Like '%'+ @searchcriteria + '%'
OR tblAssets.Alias Like '%'+ @searchcriteria + '%'
AND (IIf(1=1 Or 0=1,1,IIf(@username=[tblEmployees].[NetworkUserName]
Or @username=[tblEmployees_1].[NetworkUserName]
Or @username=[tblEmployees_2].[NetworkUserName]
Or @username=[tblEmployees_3].[NetworkUserName]
Or @username=[tblEmployees_4].[NetworkUserName]
Or @username=[tblEmployees_5].[NetworkUserName],1,0)))=1)
ORDER BY tblAssets.AssetName, tblAssets.SitusLoanID
OPTION (MAXDOP 1);
我希望用户能够运行 SP 并且 mot 接收锁定错误,或者将他们放置在队列中以接收预期的数据。
解决方案
推荐阅读
- android - 如何从 Edittext 中检索多行文本?
- angular - 无法在 NativeScript RadDataForm 中设置未定义的属性“loadSuggestionsAsync”
- aem - 如何让“Sling 模型的委托模式”工作?
- html - 当 Ionic 4 中的记录数增加时,如何使滚动按钮自动可见?
- docker - 流浪码头供应商 - 无法卷曲到本地主机
- excel - 如果单元格已经用绿色突出显示,如何忽略它
- java - 用于捕获/记录所有流量的 Netty 处理程序
- excel - 在不存在的链接上使用 XMLHTTP 处理错误
- mysql - 如何在 laravel 控制器中从 5 个表中编写 mysql 连接子查询
- php - 如何导航到正确的产品站点