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!

  • Does each session then update the status to something when it starts? How about when it's done? How is each session holding those locks? Can you show the transaction parts of the code too? Are you sending the i_task_id to the application and handling transaction logic there? – Aaron Bertrand Nov 12 at 12:28
  • It's not about clustered-v-nonclustered, it's about your indexes not covering the "order by i_priority" ordering and thus causing a full index scan which in turn causes the whole table to get locked until the end of transaction. – sharptooth Nov 12 at 14:39

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.