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.

SELECT  'Locks' AS Locks ,
        spid ,
        nt_username ,
        name ,
        hostname ,
        loginame ,
        waittime ,
        open_tran ,
        CONVERT(VARCHAR, GETDATE() - last_batch, 114) AS TimeSinceLastCommand ,
        CASE req_mode
          WHEN 0 THEN 'Not granted'
          WHEN 1 THEN 'Schema stability'
          WHEN 2 THEN 'Schema modification'
          WHEN 3 THEN 'Intent shared'
          WHEN 4 THEN 'Update'
          WHEN 5 THEN 'Exclusive'
          WHEN 6 THEN 'Intent Shared'
          WHEN 7 THEN 'Intent Update'
          WHEN 8 THEN 'Intent Exclusive'
          WHEN 9 THEN 'Shared Intent Update'
          WHEN 10 THEN 'Shared Intent Exclusive'
          WHEN 11 THEN 'Update Intent Exclusive'
          WHEN 12 THEN 'Bulk operation'
          WHEN 13 THEN 'RangeS_S (Shared Key-Range and Shared Resource lock)'
          WHEN 14 THEN 'RangeS_U (Shared Key-Range and Update Resource lock)'
          WHEN 15 THEN 'RangeI_N (Insert Key-Range and Null Resource lock)'
          WHEN 16 THEN 'RangeI_S. Key-Range Conversion lock'
          WHEN 17 THEN 'RangeI_U. Key-Range Conversion lock'
          WHEN 18 THEN 'RangeI_X. Key-Range Conversion lock'
          WHEN 19 THEN 'RangeX_S. Key-Range Conversion lock,'
          WHEN 20 THEN 'RangeX_U. Key-Range Conversion lock'
          WHEN 21 THEN 'Exclusive Key-Range and Exclusive Resource lock)'
          ELSE STR(req_mode)
        END AS LockMode
FROM    master..syslockinfo
        LEFT JOIN sysobjects so ON so.id = rsc_objid
        LEFT JOIN master..sysprocesses sp ON sp.spid = req_spid
		-- WHERE spid = @@SPID
        AND rsc_dbid = DB_ID()

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

SELECT * FROM sampleschema.tablename (UPDLOCK)
EXEC sp_lock @@SPID

SELECT * FROM sampleschema.tablename 
EXEC sp_lock @@SPID
SQL Server Locking Diagram

SQL Server Locking Diagram