CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Erika E
Erika E

Reputation: 337

Does delete with join lock the table that is *not* being deleted from?

If my query looks like:

DELETE a
FROM TableA AS a
JOIN TableB as b
ON a.SomeColumn = b.SomeColumn

is TableB locked for the duration that it takes to delete all the records from TableA, or not? If it is, is it a table-level, page-level, or row-level locking?

I'm using SQL Server 2008 R2, and I'm having a hard time finding good information about when and how SQL Server uses locking in general, so any resources related to that would be much appreciated.

Upvotes: 3

Views: 1002

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

Most likely, shared locks will be issued to TableB, the same types of locks that would be issued as if you ran a SELECT. However, the specific type of lock taken, and the duration of the lock will vary based on your individual scenario, so you should perform the following validation to get an exact answer to your question:

First, SET TRANSACTION ISOLATION LEVEL REPEATABLE READ. This will cause shared locks to be held until the transaction completes, as opposed to only being held for the duration of the read as it would be under READ COMMITTED.

We are raising the isolation level so that we can examine what locks were issued at a later time... but because of this, you probably don't want to do this test on a production system.

Next, BEGIN TRANSACTION.

Then, issue the DELETE command in question.

Now, check sys.dm_tran_locks. I would highly recommend using the SELECT statement provided by @Martin Smith in this answer.

Finally, COMMIT or ROLLBACK to close the transaction and release the locks.

The results of the SELECT using sys.dm_tran_locks will give you details on the types of locks taken, and on what objects they were taken on.

Upvotes: 3

Related Questions