INNER JOIN and locks

Question

I have two tables TableA and TableB which have information I want to retrieve and update concurrently. When I use

SELECT TOP 2 SomeFieldA
FROM TableA WITH (ROWLOCK , UPDLOCK , READPAST)

everything works fine and Process 1 sees, say, rows 1 and 2, while Process 2 sees, say, rows 3 and 4. This is the expected behaviour. Also, when I execute EXEC sp_lock I see only two KEY entries. However, when I change the statement to

SELECT TOP 2 SomeFieldA
FROM TableA WITH (ROWLOCK , UPDLOCK , READPAST)
INNER JOIN
Table B WITH ( ROWLOCK , UPDLOCK , READPAST )
ON TableA.ID = TableB.IDRef`

the first process sees rows 1 and 2, but process 2 sees nothing. Executing sp_lock shows that now all the rows have been blocked. Why is this happening?

Edit: Execution plan:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1617.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="2" StatementEstRows="2" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00670141" StatementText="SELECT TOP 2 * FROM Request R WITH ( ROWLOCK , UPDLOCK , READPAST) INNER JOIN Options O WITH ( ROWLOCK , UPDLOCK , READPAST ) ON (R.RequestID = O.RequestID)&#xD;&#xA;&#xD;" StatementType="SELECT" QueryHash="0xA35BE09F9DD52334" QueryPlanHash="0x95BEDE8C14AB4C68">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="3" CompileCPU="3" CompileMemory="160">
            <RelOp AvgRowSize="58" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00670141">
              <OutputList>
                <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="RequestID" />
                <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="DateEntered" />
                <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="Priority" />
                <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Options]" Alias="[O]" Column="RequestID" />
                <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Options]" Alias="[O]" Column="SomeOptions" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Top RowCount="false" IsPercent="false" WithTies="false">
                <TopExpression>
                  <ScalarOperator ScalarString="(2)">
                    <Const ConstValue="(2)" />
                  </ScalarOperator>
                </TopExpression>
                <RelOp AvgRowSize="58" EstimateCPU="7.524E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00670121">
                  <OutputList>
                    <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="RequestID" />
                    <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="DateEntered" />
                    <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="Priority" />
                    <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Options]" Alias="[O]" Column="RequestID" />
                    <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Options]" Alias="[O]" Column="SomeOptions" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="0" ActualExecutions="1" />
                  </RunTimeInformation>
                  <NestedLoops Optimized="false">
                    <Predicate>
                      <ScalarOperator ScalarString="[TestDatabase].[dbo].[Options].[RequestID] as [O].[RequestID]=[TestDatabase].[dbo].[Request].[RequestID] as [R].[RequestID]">
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Options]" Alias="[O]" Column="RequestID" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="RequestID" />
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Predicate>
                    <RelOp AvgRowSize="45" EstimateCPU="0.0001603" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="3">
                      <OutputList>
                        <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Options]" Alias="[O]" Column="RequestID" />
                        <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Options]" Alias="[O]" Column="SomeOptions" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="0" ActualExecutions="1" />
                      </RunTimeInformation>
                      <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Options]" Alias="[O]" Column="RequestID" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Options]" Alias="[O]" Column="SomeOptions" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[TestDatabase]" Schema="[dbo]" Table="[Options]" Index="[PK__Options__33A8519A1DE57479]" Alias="[O]" IndexKind="Clustered" />
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="20" EstimateCPU="8.51E-05" EstimateIO="0.0032035" EstimateRebinds="0" EstimateRewinds="1.33333" EstimateRows="6" LogicalOp="Table Scan" NodeId="3" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.00340207" TableCardinality="6">
                      <OutputList>
                        <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="RequestID" />
                        <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="DateEntered" />
                        <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="Priority" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="8" ActualEndOfScans="1" ActualExecutions="2" />
                      </RunTimeInformation>
                      <TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="RequestID" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="DateEntered" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" Column="Priority" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[TestDatabase]" Schema="[dbo]" Table="[Request]" Alias="[R]" IndexKind="Heap" />
                      </TableScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </Top>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

SQL:

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

CREATE TABLE Options
(
    RequestIDRef    INT 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, 'a' )
INSERT INTO Options VALUES ( 2, 'b' )
INSERT INTO Options VALUES ( 3, 'c' )
INSERT INTO Options VALUES ( 4, 'd' )

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

Now,

BEGIN TRANSACTION

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

WAITFOR DELAY '00:00:02.5'

COMMIT TRANSACTION

on Query1 returns 2 and 1, as expected, but on Query2 it returns nothing. However, if I remove the INNER JOIN and the second table it works and returns (2,1) in Query1 and (3,4) in Query2.

Answer 1

Logically, the Cartesian product of A and B is restricted to the intersection or matching rows.

To find these matching rows, TableA.ID and TableB.IDRef are looked at. A table scan is required on at least one of the tables if there is no index. So all rows are locked for the scan

So both TableA.ID and TableB.IDRef need to have indexes. I suspect that TableA.ID already has as PK but TableB.IDRef doesn't.

The TOP is applied later BTW

It's similar to my answer here where there is TOP with no ORDER BY and no index: ORDER BY and WITH(ROWLOCK, UPDLOCK, READPAST). If you added ORDER BY to the 1st query, then the 2nd process won't see anything too most likely.

Edit: for your update, SELECT * will invalidate index usage and cause scan: the indexes aren't much use with SELECT * because they aren't covering