Type of locks in SQL Server

 SQL Server lock categories:

Ø  Shared (S) – Read operations. SQL Server places a Shared Lock on the row/page/table. Multiple users can read the same data simultaneously. No one can modify the data until the read operation completes.

Ø  Exclusive (X) – Insert/Update/Delete. Used when data is being INSERTED, UPDATED, or DELETED. SQL Server places an Exclusive Lock. No other session can read (under some isolation levels) or modify the row.

Ø  Update (U) – Prevent deadlocks during updates. Before converting to an Exclusive Lock, SQL Server first acquires an Update Lock.

Ø  Intent (IS, IX, SIX) – Indicate lower-level locks. SQL Server uses these internally to indicate that lower-level locks exist. Table lock indicating, "I have Shared Locks somewhere inside this table."

Ø  Schema (Sch-S, Sch-M) – Metadata protection. Acquired during query compilation and execution. when we are altering the tables, I mean doing DDL on the table Schema Modification Lock (Sch-M) acquired. It blocks: SELECT, INSERT, UPDATE, DELETE. Everything waits to complete it.

Ø  Bulk Update (BU) – Bulk load operations.

Ø  Key Range Locks – Serializable isolation. It prevents the phantom reads.

Ø  RID/KEY Locks – Row-level locks. Most granular lock. In Heap Table RID Lock acquired while Clustered Index KEY Lock acquired.

Ø  PAG Locks – Page-level locks. Locks an entire data page (8 KB).

Ø  TAB Locks – Table-level locks. Locks the entire table. No other user can access it.

Lock Compatibility Matrix (Most Important for Interviews)

Requested \ Existing	S	U	X
S	✅	✅	❌
U	✅	❌	❌
X	❌	❌	❌

Interpretation

• Shared + Shared = Allowed
• Shared + Update = Allowed
• Shared + Exclusive = Blocked
• Exclusive + Anything = Blocked