Locking Hierarchy

SQL Server uses multi level of locking to allow different locks on various level of objects.

1. Lower level of locking on RID or KEY
RID - Row at heap (Actually doesn't have clustered Index)
KEY - Row at Clustered Index

2. Higher level of locking on Database

Hierarchies:
- Database (Highest level of locking)
- Database File
- Object
- Extent
- Page
- RID Or KEY (Lowest level of locking)

Note: SQL Server automatically decides on what level of lock should be placed to minimize the locking overhead.

Various lock(s) held in all the Database

To findout what are all the various Request Modes/Locks held in various level(Key, Page,...Etc.,) in all the Database(s)

Select DB_Name([Database Id]) [Database Name],[Request Mode], [DATABASE], [FILE], [OBJECT], [PAGE], [KEY], [EXTENT], [RID], [APPLICATION], [METADATA], [HOBT], [ALLOCATION_UNIT]
From
(
select Distinct o.resource_database_id [Database Id],
o.request_mode [Request Mode],
o.resource_type [Resource Type],
Count(1) [Counts]
from sys.dm_tran_locks o
Group by o.resource_database_id, o.request_mode, o.resource_type
) as IQ
PIVOT
(
SUM([Counts]) For [Resource Type] In ([DATABASE], [FILE], [OBJECT], [PAGE], [KEY], [EXTENT], [RID], [APPLICATION], [METADATA], [HOBT], [ALLOCATION_UNIT])
)as OQ

The following screen shows as follows... 

Database1 hold 1 IX lock on OBJECT, PAGE level, 4 RangeS-S locks on KEY level, 3 RangeX-X locks on KEY level, 2 SHARED(S) lock on DATABASE lavel.
Database 2 hold 1 SHARED(S) lock on DATABASE level.

--Result