Locking is the essential because to manage the transaction concurrency in multi-user environment.
Locks are in-memory structure of 96 bytes in size which has its owner, resource type, lock modes.
SELECT * FROM SYS.DM_TRAN_LOCKS
Shared locks(S)
SELECT
Exclusive Locks(X)
During the data modification operators such as insert, update or Delete.
This prevent one object to be modified by different session.
Update locks(U):
Intent Locks(IS,IXMIU)
These locks indicate locks on the child objects.
If row has (X) lock, it would introduce(IX) the locks on page, table and database.
Schema Lock(Sch-S and Sch-M)
These are basically used when create objects or alter the schema structure of the objects
Lock Hiearchy:
The lock hierarchy starts from higher level database level to lower level up to row.
Locks will always acquire from top to bottom.
In case of DDL-Database (S)-Table/Page(IS)-Row(S)
In case of DML-Database-Table(IXorIU)–Row
—
–Shared Lock—
begin tran
select * from Person.Person
with (holdlock) where BusinessEntityID=9871
Select resource_type, request_mode,resource_description from
sys.dm_tran_locks
–select * from sys.dm_tran_locks
—where resource_type=’database’
rollback
Lock escalation:
Keep transaction shorter
Divide large transaction into small transaction.
Azure SQL Database 