A deadlock occurs when
tran 1 has a lock on object A and cannot continue without also obtaining a lock on object B
and
tran2 has a lock on object B and cannot continue without also obtaining a lock on object A
You state "During this transaction, the database is locked so that other process can't access the
database". This shouldn't happen. Can you provide more details?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden