Regarding the this question I want to ask
Comparing two queries:
(1)
DELETE dbA.dbo.tableA
FROM dbA.dbo.tableA a WITH(NOLOCK)
JOIN dbB.dbo.tableB b WITH(NOLOCK)
ON
b.colA = a.colA
AND b.colB = a.colB
and
(2)
DELETE FROM dbA.dbo.tableA
WHERE EXISTS
(
SELECT *
FROM dbB.dbo.tableB b WITH(NOLOCK)
where
b.colA = dbA.dbo.tableA .colA
AND b.colB = dbA.dbo.tableA .colB
)
it is clear, that the queries do the similar work, if we not consider concurrent writes which can lead to dirty reads.
BUT i have a doubt
was I right with the following comment?
note, that there will be placed a shared (upgradeable?) lock @ tableA from the very
beginning of the statement (2), instead of two NOLOCK tables in join (1),
which will place the first lock – UX lock only after there will be
found the first row to delete
Best Answer
No.
At least in my testing both of them have identical execution plans and identical locking behaviour. Both place an
IX
lock ontableA
and a schema stability lock onTableB
straight away and then follow the same behaviour in doing the clustered index scan onTableA
takingIU
locks on the page,U
locks on thekey
, then in the event of a match converting the page lock toIX
then the key lock toX
prior to deleting the row. This is exactly the same pattern as if theNOLOCK
hint ontableA
is removed entirely so it is pointless in this case.My original results had some differences between the two queries in that the Join version has a series of apparently aquiring and immediately releasing schema stability locks on
TableA
that didn't show up in theEXISTS
version. That seems to be related to whether the "Include Actual Execution Plan" option is enabled in SSMS. Today if I have this option enabled this series appears in the locking info for both of them so not sure why originally it only appeared in one. Maybe some timing issue.You can see this locking information using
TF1200
as below (with "Include Actual Execution Plan" option disabled).Code
ObjectIds
LockRes
Output (All three queries)