Sams Teach Yourself Transact-SQL in 21 Days, Second Edition

Testing Lock Interaction

Ever since you executed the delete query, you've been holding the locks open. The session that performed the delete can read through the locks it created. Try running this query from the same window where the delete operation occurred:

select count(*)
from Orders

You should get the value 830, but don't panic if you don't; a row might have been added or deleted from the table. The Orders table starts with 831 rows, but the current transaction deletes one of those rows. This demonstrates an important point about transactions: A process is not blocked by its own locks. Process 53 holds an exclusive lock on a page in the Orders table. That lock will block other readers, but process 53 can read the page and determine the ...