ORDER BY and UPDLOCK—A (Non-)Working Example

Although I cannot disclose information about the real tables (if I could, I would) causing the problems described in my previous question, I built from scratch a (non-)working example which reproduces exactly (data-type by data-type, column size by column size) the problem I am facing. So I created a new database named "QueueTest" and ran the following script:

CREATE TABLE Request
(
    RequestID   BIGINT PRIMARY KEY,
    Priority    TINYINT,
    DateEntered DATETIME
)

CREATE TABLE Options
(
    RequestIDRef    BIGINT PRIMARY KEY,
    SomeOptions     NVARCHAR(MAX)
)

ALTER TABLE Options ADD

    CONSTRAINT FK_REQUESTIDREF FOREIGN KEY ( RequestIDRef ) REFERENCES [Request] ( RequestID )

GO

INSERT INTO Request VALUES ( 1, 2, GETDATE() )
INSERT INTO Request VALUES ( 2, 1, GETDATE() )
INSERT INTO Request VALUES ( 3, 3, GETDATE() )
INSERT INTO Request VALUES ( 4, 2, GETDATE() )

INSERT INTO Options VALUES ( 1, 'Options1' )
INSERT INTO Options VALUES ( 2, 'Options2' )
INSERT INTO Options VALUES ( 3, 'Options3' )
INSERT INTO Options VALUES ( 4, 'Options4' )

CREATE NONCLUSTERED INDEX IX_OPTIONS_REQUESTIDREF ON [Options] ( RequestIDRef )
CREATE NONCLUSTERED INDEX IX_REQUEST_PRIORITY_DATEENTERED ON [Request] ( Priority , DateEntered ) INCLUDE ( RequestID )

After that I opened two new queries, Query1 and Query2, and ran the following scripts to simulate the problem:

BEGIN TRANSACTION

SELECT TOP 2 RequestID FROM ( Request R WITH ( ROWLOCK , UPDLOCK , READPAST ) INNER JOIN Options O WITH ( ROWLOCK , UPDLOCK , READPAST ) ON ( R.RequestID = O.RequestIDRef ) ) ORDER BY Priority ASC , DateEnt开发者_运维问答ered ASC

WAITFOR DELAY '00:00:02.500'

COMMIT TRANSACTION

The results I got were

Query1 | Query 2
   2   |
   1   |

while I was, of course, expecting

Query1 | Query 2
   2   |    4
   1   |    3

So, I assume that even though I have the indexes described above, Query1 is still locking the whole table. Here is the execution plan for both queries:

Execution plan

StmtText
-----------------------
SET SHOWPLAN_TEXT ON

(1 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

BEGIN TRANSACTION

SELECT TOP 2 RequestID FROM ( Request R WITH ( ROWLOCK , UPDLOCK , READPAST ) INNER JOIN Options O WITH ( ROWLOCK , UPDLOCK , READPAST ) ON ( R.RequestID = O.RequestIDRef ) ) ORDER BY Priority ASC , DateEntered ASC

(2 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(TOP EXPRESSION:((2)))
       |--Nested Loops(Inner Join, WHERE:([QueueTest].[dbo].[Options].[RequestIDRef] as [O].[RequestIDRef]=[QueueTest].[dbo].[Request].[RequestID] as [R].[RequestID]))
            |--Index Scan(OBJECT:([QueueTest].[dbo].[Request].[IX_REQUEST_PRIORITY_DATEENTERED] AS [R]), ORDERED FORWARD)
            |--Clustered Index Scan(OBJECT:([QueueTest].[dbo].[Options].[PK__Options__5366EEE80BC6C43E] AS [O]))

(4 row(s) affected)

StmtText
---------------------------------

WAITFOR DELAY '00:00:02.500'

COMMIT TRANSACTION

(2 row(s) affected)

Please notice that when I ran both queries with SET SHOWPLAN_TEXT ON I think the WAITFOR DELAY instruction was not respected-in fact, the first query completed immediately, so that when I ran the second one it had already completed.

What can be causing the locking problem? Please help me since I can't really figure it by myself.

Edit: Please note that I can't create a view with a SELECT TOP 2 instruction. The 2 here is for illustration purposes, while in the real situation it is a parameter passed to a stored procedure which then runs this SELECT. Please note also that creating an indexed view using

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW TestView WITH SCHEMABINDING AS

    SELECT RequestID , Priority , DateEntered FROM( dbo.Request R INNER JOIN dbo.Options O ON ( R.RequestID = O.RequestIDRef ) )

GO

CREATE UNIQUE CLUSTERED INDEX IX_TESTE_1 ON TestView ( RequestID )
CREATE NONCLUSTERED INDEX IX_TESTE_2 ON TestView ( Priority ASC , DateEntered ASC ) INCLUDE ( RequestID )

and then selecting from TestView WITH ( ROWLOCK , UPDLOCK , READPAST ) also didn't work, neither with nor without the ORDER BY clause.

You have a "Clustered Index Scan" and an "Index Scan" which blocks all rows in both tables As I said in my previous answers.

Also from previous answers:

1. Use an indexed view
2. Don't have hints in view
3. Add NOEXPAND hint when you call the view

Finally, the index IX_REQUEST_PRIORITY_DATEENTERED does not have the JOIN condition. This may remove one of the Scans

If you won't listen to the "view" bit above, then try both of these

CREATE NONCLUSTERED INDEX IX_REQUEST_PRIORITY_DATEENTERED ON [Request]
  (RequestID , Priority , DateEntered )


CREATE NONCLUSTERED INDEX IX_REQUEST_PRIORITY_DATEENTERED ON [Request]
  (Priority , DateEntered , RequestID )