我有两个表TableA
和TableB
,它们含有我想要同时检索和更新的信息。当我使用
SELECT TOP 2 SomeFieldA
FROM TableA WITH (ROWLOCK , UPDLOCK , READPAST)
一切都正常,进程1看到第1行和第2行,进程2看到第3行和第4行,这是期望的行为。当我执行EXEC sp_lock
时,只看到两个KEY
条目。但是,当我将语句更改为
SELECT TOP 2 SomeFieldA
FROM TableA WITH (ROWLOCK , UPDLOCK , READPAST)
INNER JOIN
Table B WITH ( ROWLOCK , UPDLOCK , READPAST )
ON TableA.ID = TableB.IDRef`
第一个进程看到了第1行和第2行,但第2个进程什么也没看到。执行sp_lock
命令显示现在所有的行都被阻塞了。为什么会出现这种情况?
编辑:执行计划:
<?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)

" 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 )
现在
,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
在Query1中,返回了2和1,符合预期,但是在Query2中没有返回任何结果。然而,如果我移除INNER JOIN
和第二个表格,它就可以工作,并在Query1中返回(2,1),在Query2中返回(3,4)。