SQL Server Locking

Microsoft Link :


Locks held by Current Processes:
SQL Server 2008 R2

SQL Team Primer on Locks :


Kalen Delany Crisp explanation on Update Locks:


UPDATE locks are not really a separate kind of lock, but rather are a hybrid of SHARED and EXCLUSIVE locks. And contrary to what you might think, UPDATE locks are not just acquired for UPDATE operations.  A transaction acquires this kind of lock when SQL Server executes a data modification operation but first needs to perform a search to find the resource to modify.

Locks Acquired by a SPID:
Quick and dirty way but gives you good idea about resources involved.

SQL Server Locking Diagram

SQL Server Locking Diagram