Cfrgtkky

I have a SQL code as below

#table creation

CREATE TABLE TAB

([i_task_id] [int] IDENTITY(1,1) NOT NULL,

[i_priority] [tinyint] NULL,

[i_status] [smallint] NULL

CONSTRAINT [PK_TAB] PRIMARY KEY CLUSTERED 

(

[i_task_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = 

OFF, 

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]



#non-clustered index on i_status

IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = 'IX_status' AND 

OBJECT_ID = OBJECT_ID('TAB'))

BEGIN

CREATE NONCLUSTERED INDEX IX_status

ON [dbo].[TAB] (i_status)

END



#values in the table#

INSERT INTO TAB(i_priority,i_status)

VALUES (31,1),(30,1),(31,1),(20,1),(10,1)



#select query#

SELECT TOP 1 i_task_id

FROM TAB with (ROWLOCK,UPDLOCK,READPAST)

WHERE i_status = 1

ORDER BY i_priority desc,i_task_id asc

Locking does not work while executing the above query in 4 different sessions. I get result in one session where as nothing is returned in other 3 sessions.

When i tried to change the ORDER BY condition as below

 ORDER BY i_task_id asc,i_priority desc

then the query worked and returned 4 different results in 4 sessions.

My requirement is if several users try to access the same select query at the same time, there should not be any deadlock situation(i.e each user should be provided the unlocked row).Also,each user should be given different task id in different sessions.

Will the locking with order by non-clustered columns work?

Thanks for your time!

I would opt for marking the row as "in process" (e.g. having a status for the equivalent of "checked out") as opposed to relying solely on row locking:

;WITH x AS 

(

  SELECT TOP (1) i_task_id, i_status

    FROM dbo.TAB WITH (ROWLOCK,UPDLOCK,READPAST)

    WHERE i_status = 1

    ORDER BY i_priority DESC,i_task_id ASC

)

UPDATE x SET i_status = 2 -- in process? locked?

  OUTPUT inserted.i_task_id

  WHERE i_status = 1;

No blocking or deadlocking in my tests. The 6th session won't block or deadlock either, it will just return no rows.