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

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

if query looks like:

delete tablea bring together tableb b on a.somecolumn = b.somecolumn

is tableb locked duration takes delete records tablea, or not? if is, table-level, page-level, or row-level locking?

i'm using sql server 2008 r2, , i'm having hard time finding info when , how sql server uses locking in general, resources related much appreciated.

most likely, shared locks issued tableb, same types of locks issued if ran select. however, specific type of lock taken, , duration of lock vary based on individual scenario, should perform next validation exact reply question:

first, set transaction isolation level repeatable read. cause shared locks held until transaction completes, opposed beingness held duration of read under read committed.

we raising isolation level can examine locks issued @ later time... because of this, you don't want test on production system.

next, begin transaction.

then, issue delete command in question.

now, check sys.dm_tran_locks. highly recommend using select statement provided @martin smith in answer.

finally, commit or rollback close transaction , release locks.

the results of select using sys.dm_tran_locks give details on types of locks taken, , on objects taken on.

sql-server tsql sql-server-2008-r2