CODEWITHSUNDEEP
sql-serversql-server-2005
milkboneUnderwear
milkboneUnderwear

Reputation: 191

SQL Queue processing with UPDLOCK, READPAST and still getting deadlocks

The following stored procedure is working like a champ. This stored procedure facilitates queue processing for thousands of records and frequently fires simultaneously (sometimes threaded up to 7 or 8 times at any given instant):

Alter Procedure sp.QueueProcessSingle
As
DECLARE @aid int
update TABLE1 set [active] = 1, @aid = aid where aid=(
SELECT     TOP 1 aid
FROM         TABLE1 t1 WITH (UPDLOCK, READPAST)

WHERE
(t1.status is NULL) AND (t1.groupcount = 1) AND (t1.active = 0)
order by recorddate, recordtime)

update TABLE1 set status = 'Getting worked on' where @aid = aid

I'm happy with the above stored procedure, but it's its 1st cousin that I'm not happy with. A similar stored procedure needs to do the exact same thing as the sp above, only it does it across a group of records. In other words instead of just updating 1 record as "having work performed on it, therefore don't grab this record again when the stored procedure executes again" it needs to update 2 or 3 or 4 or more records that have similar data (same name and phone).

Here's how I have the bastard cousin sp now and I'm getting deadlocks:

Alter Procedure sp.QueueProcessMultiple
As
DECLARE @aid int
DECLARE @firstname nvarchar(50)
DECLARE @lastname nvarchar(50)
DECLARE @phone nvarchar(50)
update TABLE1 set active = 1, @aid = aid where aid=(
SELECT     TOP 1 aid
FROM         TABLE1 t1 WITH (UPDLOCK, READPAST)

WHERE
(t1.status is NULL) AND (t1.groupcount > 1) AND (t1.active = 0)
order by recorddate, recordtime)

UPDATE TABLE1 set status = 'Getting worked on' where @aid = aid

/****** Ok, now I have updated the "parent" record of the group which has the earliest date and time but now I also need to update the other records that are in this group*****/

SELECT @firstname = firstname, @lastname = @lastname, @phone = phone 
FROM TABLE1 WITH (UPDLOCK, READPAST)
WHERE @aid = aid

UPDATE TABLE1 set status = 'Getting worked on', active = 1 where @firstname = firstname AND @lastname = lastname AND @phone = phone AND status is NULL AND active = 0

And as is often the case and part of the beauty of stackoverflow, just typing this up is shedding a bit of light on it for me. It seems like it would be better to update the whole group at once instead of just updating the "parent" record of the group and then updating all records in the table that have matching data of the parent record. Now how to do that? I'll keep looking and will post a solution if/when I get it, but any input would be much appreciated. Thanks!

Upvotes: 1

Views: 1579

Answers (1)

gbn
gbn

Reputation: 432471

Should be able to do this in one statement using DENSE_RANK to get the first aid.
This works if the collected rows all have the same (recorddate, recordtime)
Also, you need ROWLOCK too

update
    t1
set
    set status = 'Getting worked on',
    active = 1,
    @aid = aid, @firstname = firstname, @lastname = lastname, @phone = phone 
FROM
    (
    SELECT
         DENSE_RANK() OVER (ORDER BY recorddate, recordtime) AS rn, 
         aid, firstname, lastname, status, active
    FROM TABLE1 t1x WITH (UPDLOCK, READPAST, ROWLOCK)
    WHERE
        (t1x.status is NULL) AND (t1x.groupcount > 1) AND (t1x.active = 0)
    ) t1;
WHERE
    rn = 1

Upvotes: 2

Related Questions