Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jan 6 '12 at 15:32

This question came from our site for professional and enthusiast programmers.

    
NOLOCK on DELETE's is deprecated; why would you want to delete uncommitted data anyway? –  SqlACID Jan 8 '12 at 17:10
    
Its not me, I just interested in right answer –  Oleg Dok Jan 8 '12 at 17:13

1 Answer 1

up vote 8 down vote accepted

No.

At least in my testing both of them have identical execution plans and identical locking behaviour. Both place an IX lock on tableA and a schema stability lock on TableB straight away and then follow the same behaviour in doing the clustered index scan on TableA taking IU locks on the page, U locks on the key, then in the event of a match converting the page lock to IX then the key lock to X prior to deleting the row. This is exactly the same pattern as if the NOLOCK hint on tableA 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 the EXISTS 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

SET NOCOUNT ON;

CREATE TABLE tableA(
colA INT,
colB INT,
PRIMARY KEY(colA,colB));

CREATE TABLE tableB(
colA INT,
colB INT,
PRIMARY KEY(colA,colB));

SELECT OBJECT_ID('tableA') AS tableA,
       OBJECT_ID('tableB') AS tableB;

INSERT INTO tableA VALUES (0,0),(1,1),(2,2),(3,3);
INSERT INTO tableB VALUES (1,1),(2,2),(4,4),(5,5),(6,6),(7,7);

SELECT *, %%lockres%%  AS lockres
FROM tableA


DECLARE @JoinSQLNoLockTableA nvarchar(max) = N'
DELETE tableA
FROM tableA a WITH(NOLOCK)
JOIN tableB b WITH(NOLOCK)
  ON 
      b.colA = a.colA
  AND b.colB = a.colB;'

DECLARE @ExistsSQL nvarchar(max) = N'
DELETE FROM tableA 
WHERE EXISTS
(
  SELECT *
  FROM tableB b WITH(NOLOCK) 
  where
      b.colA = tableA.colA 
  AND b.colB = tableA.colB 
);'  

DECLARE @JoinSQLWithoutNoLockTableA nvarchar(max) = REPLACE(@JoinSQLNoLockTableA,'tableA a WITH(NOLOCK)', 'tableA a')

/*Run the commands first with the trace flag off so the locking
info is less full of irrelevant stuff about plan compilation */
EXEC (@JoinSQLNoLockTableA);
INSERT INTO tableA VALUES (1,1),(2,2);
EXEC (@ExistsSQL);
INSERT INTO tableA VALUES (1,1),(2,2);
EXEC (@JoinSQLWithoutNoLockTableA);
INSERT INTO tableA VALUES (1,1),(2,2);


/*Run with TF1200 on*/

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@JoinSQLNoLockTableA - Start';
EXEC (@JoinSQLNoLockTableA);
PRINT '@JoinSQLNoLockTableA - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

INSERT INTO tableA VALUES (1,1),(2,2);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@ExistsSQL - Start';
EXEC (@ExistsSQL);
PRINT '@ExistsSQL - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

INSERT INTO tableA VALUES (1,1),(2,2);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@JoinSQLWithoutNoLockTableA - Start';
EXEC (@JoinSQLWithoutNoLockTableA);
PRINT '@JoinSQLWithoutNoLockTableA - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

DROP TABLE tableA,
           tableB;

ObjectIds

tableA      tableB
----------- -----------
308196148   372196376

LockRes

colA        colB        lockres
----------- ----------- ------------------------------
0           0           (00009620dd9a)
1           1           (02006d47cbee)
2           2           (040060eff172)
3           3           (06009b88e706)

Output (All three queries)

Process 52 acquiring Sch-S lock on OBJECT: 23:372196376:0  (class bit0 ref1) result: OK

Process 52 acquiring IX lock on OBJECT: 23:308196148:0  (class bit2000000 ref1) result: OK

Process 52 acquiring IU lock on PAGE: 23:1:14144 (class bit0 ref1) result: OK

Process 52 acquiring U lock on KEY: 23:72057594051231744 (00009620dd9a) (class bit0 ref1) result: OK

Process 52 releasing lock on KEY: 23:72057594051231744 (00009620dd9a)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (02006d47cbee) (class bit0 ref1) result: OK

Process 52 acquiring IX lock on PAGE: 23:1:14144 (class bit2000000 ref0) result: OK

Process 52 acquiring X lock on KEY: 23:72057594051231744 (02006d47cbee) (class bit2000000 ref0) result: OK

Process 52 releasing lock reference on KEY: 23:72057594051231744 (02006d47cbee)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (040060eff172) (class bit0 ref1) result: OK

Process 52 acquiring X lock on KEY: 23:72057594051231744 (040060eff172) (class bit2000000 ref0) result: OK

Process 52 releasing lock reference on KEY: 23:72057594051231744 (040060eff172)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (06009b88e706) (class bit0 ref1) result: OK

Process 52 releasing lock on KEY: 23:72057594051231744 (06009b88e706)

Process 52 releasing lock reference on PAGE: 23:1:14144

Process 52 releasing lock on OBJECT: 23:372196376:0 
share|improve this answer
    
Yep, in 2008R2 I have little bit other output, but it is, regarding the question, generally the same. Thanks. +1 and answered –  Oleg Dok Jan 6 '12 at 12:08
    
@OlegDok - Do you still see that series of acquiring Sch-S lock on tableA then releasing it again on the join version that doesn't show up in the EXISTS version? –  Martin Smith Jan 6 '12 at 14:11
    
No, Schema locks placed only on TableB in both cases, and - very interesting, sometimes locking behavior is just the same in both cases, sometimes JOIN versions has twice (!) less locks, than EXISTS version. Depending on real data layout? –  Oleg Dok Jan 6 '12 at 15:08
    
@OlegDok - Did you notice my original script had an error? I forgot to add back in the deleted row at one point. Not sure which version if any you are using. –  Martin Smith Jan 6 '12 at 15:11
    
yep, corrected that also, don't worry 8-) Rewritten that every delete script run in its own transaction, which rolls back in the end - that is for sure, that all the data returning to original places –  Oleg Dok Jan 6 '12 at 15:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.