May 3, 2017 at 11:46 pm
Hello,
I have the following code in a SP to deliver the next available lead for users. There are more than 200 users available. Some users are complaining they are not getting a lead as soon as they execute the SP. But when they re-execute it, they get a lead.
I think the problem is with UPDLOCK. It locks the records (may be escalate it to table level) and when another user requested there are no unlock records to select. Can anyone help me to overcome this issue? I'm running this in SQL 2008R2
DECLARE @LeadId INT
BEGIN TRAN
SELECT TOP 1 @LeadId = LeadId
FROM tblLead WITH (UPDLOCK, READPAST)
WHERE UserId IS NULL
ORDER BY Rank
UPDATE tblLead SET
UserId = 'UserId'
WHERE LeadId = @LeadId
COMMIT TRAN
Thank you
May 4, 2017 at 2:31 pm
Maybe you should consider setting a specific transaction isolation level outside of your transaction instead of at the inner query level? There are multiple options which will, for example, prevent other transactions from reading "dirty" data that you are in the process of reading/modifying. I wish I had more specifics, I will be curious to read the experts' replies on this one.
May 8, 2017 at 5:29 am
I suspect the following will lock all the rows where UserId IS NULL and select the LeadId with the greatest [Rank].
SELECT @LeadId = LeadId
FROM tblLead WITH (UPDLOCK, READPAST)
WHERE UserId IS NULL
ORDER BY [Rank];
You could try:
SELECT TOP (1) @LeadId = LeadId
FROM tblLead WITH (UPDLOCK, READPAST)
WHERE UserId IS NULL
ORDER BY [Rank];
Also, I would try to avoid using explicit transactions.
Try using something like the following which has not been tested:
DECLARE @t TABLE (LeadId int);
WITH UserRow
AS
(
SELECT TOP (1) LeadId, UserId
FROM tblLead WITH (UPDLOCK, READPAST)
WHERE UserId IS NULL
ORDER BY [Rank]
)
UPDATE UserRow
SET UserId = 'UserId'
OUTPUT inserted.LeadId INTO @t;
--select * from @t
May 8, 2017 at 6:17 am
I second the motion to use UPDATE with OUTPUT clause and thus avoid multiple statements and explicit batch transaction.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 8, 2017 at 6:43 pm
Thanks everyone for trying to help me out. It was meant to be take the top 1 in my original query. But could not edit it as I could not find out that option. Apologies...
Ken, I already tried your method and few other ways as well. Still no luck. When checked the execution plan, it says the query is picking 1000 records original and they filtered it down. This is why the whole record set is locked. So I'm still struggling to find a better way to pick top 1 while only locking that row.
Following are few of the methods I tried.
Used row_number() and updated the record later;
SELECT @LeadId = LeadId
FROM (
SELECT LeadId
, row_number() OVER(ORDER BY [Rank] DESC) rn
FROM tblLead WITH (UPDLOCK, READPAST)
WHERE UserId IS NULL
) t
WHERE rn = 1
UPDATE tblLead
SET UserId = @userid
WHERE LeadId = @LeadId
Updating LeadId while selecting it;
UPDATE t
SET UserId = @userid
OUTPUT INSERTED.LeadId
INTO @ttLead (LeadId)
FROM (
SELECT LeadId
, row_number() OVER(ORDER BY [Rank] DESC) rn
FROM tblLead WITH (UPDLOCK, READPAST)
WHERE UserId IS NULL
) t
WHERE rn = 1
Similar to above, but added table hints while updating;
UPDATE t
SET UserId = @userid
OUTPUT INSERTED.LeadId
INTO @ttLead (LeadId)
FROM (
SELECT LeadId
, row_number() OVER(ORDER BY [Rank] DESC) rn
FROM tblLead
WHERE UserId IS NULL
) t
INNER JOIN tblLead l WITH (UPDLOCK, READPAST) ON t.LeadId = l.LeadId
WHERE rn = 1
May 8, 2017 at 11:31 pm
NushAngel - Wednesday, May 3, 2017 11:46 PMHello,I have the following code in a SP to deliver the next available lead for users. There are more than 200 users available. Some users are complaining they are not getting a lead as soon as they execute the SP. But when they re-execute it, they get a lead.
I think the problem is with UPDLOCK. It locks the records (may be escalate it to table level) and when another user requested there are no unlock records to select. Can anyone help me to overcome this issue? I'm running this in SQL 2008R2
DECLARE @LeadId INT
BEGIN TRAN
SELECT @LeadId = LeadId
FROM tblLead WITH (UPDLOCK, READPAST)
WHERE UserId IS NULL
ORDER BY Rank
UPDATE tblLead SET
UserId = 'UserId'
WHERE LeadId = @LeadId
COMMIT TRAN
Thank you
You're almost there. Change the above code to
SELECT TOP(1) @LeadId = LeadId
Switch off lock escalation on your table:
alter table dbo.tblLead SET(LOCK_ESCALATION = DISABLE);
Finally, ensure that your SELECT and UPDATE queries are well supported by indexing: post up execution plans if you're unsure.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 8, 2017 at 11:53 pm
Thanks for the tip Chris. I'll try switching off lock escalation.
May 9, 2017 at 3:40 am
A filtered index might help:
CREATE UNIQUE NONCLUSTERED INDEX UQ_tblLead_Rank_LeadId
ON tblLead ([Rank], LeadId)
INCLUDE (UserId)
WHERE UserId IS NULL;
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply