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!