mjhjmtu

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.