Does it use a lock to delete a table that is * not * removed from?
If my request looks like this:
DELETE a
FROM TableA AS a
JOIN TableB as b
ON a.SomeColumn = b.SomeColumn
is it TableB
blocked for the time it takes to delete all records from TableA
, or not? If so, is it a table-level, page-level, or row-level locking?
I am using SQL Server 2008 R2 and I have a hard time finding good information on when and how SQL Server uses locking in general, so any resources related to this would be much appreciated.
source to share
Most likely, shared locks will be issued on TableB
, the same types of locks that will be released as if you were running SELECT
. However, the specific type of blocking and the length of blocking will depend on your individual scenario, so you should perform the following check to get an accurate answer to your question:
First SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
. This will cause shared locks to be held until the transaction completes, rather than just holding for the entire read time, as in READ COMMITTED
.
We're raising the isolation level so we can check which locks were released later ... but because of that, you probably don't want to do this test on a production system.
Further BEGIN TRANSACTION
.
Then run the command DELETE
.
Now sys.dm_tran_locks
. I highly recommend using the operator SELECT
provided by @Martin Smith in this answer .
Finally, COMMIT
or ROLLBACK
to close the transaction and release the locks.
The results SELECT
using sys.dm_tran_locks
will give you detailed information about the types of locks and what objects were made.
source to share