Locks & It’s Types
In Microsoft SQL Server
Sai Reddy 7/9/25 MS SQL
Locks in SQL Server
What is a Lock in SQL Server?
A lock is a way to control concurrent access to data. When a transaction wants to read or write to a
resource (table, row, page), it acquires a lock. Other transactions are restricted from performing
conflicting operations on the same resource until the lock is released.
Types of Locks in SQL Server
Lock Type Applies To User Command Purpose
Shared (S) SELECT WITH (HOLDLOCK) Read-only access
Prevent concurrent
Exclusive (X) INSERT/UPDATE WITH (XLOCK)
reads/writes
SELECT before
Update (U) WITH (UPDLOCK) Prevent deadlocks
update
Intent (IS,IX) Metadata System-handled Lock hierarchy control
Schema (Sch-
DDL Automatic via DDL Control schema access
S/M)
Bulk Update Bulk Insert WITH (TABLOCK) Speed up inserts
SELECT SET TRANSACTION ISOLATION LEVEL
Key-range Prevent phantom rows
(SERIALIZABLE) SERIALIZABLE
1. Shared Lock (S)
• Purpose: Acquired when a resource is read (e.g., SELECT).
• Allows: Other shared locks.
• Blocks: Exclusive locks (writes).
• When used: During SELECT under default or higher isolation levels like REPEATABLE READ.
Commands & Example:
Sai Reddy
saireddy-dotnetfs
2. Exclusive Lock (X)
• Purpose: Acquired during INSERT, UPDATE, DELETE.
• Blocks: Reads (S) and Writes (X).
• When used: Whenever you modify data.
Commands & Example:
3. Update Lock (U)
• Purpose: Used before modifying data (like in SELECT ... FOR UPDATE in other DBs).
• Avoids: Deadlocks by using U lock first, upgrading to X only if update is needed.
• When used: During SELECT ... FOR UPDATE-like scenarios.
Commands & Example:
4. Intent Locks (IS, IX, SIX)
• Purpose: Shows intention to place locks lower in the hierarchy (e.g., row-level).
• IS (Intent Shared): Planning to acquire S lock at finer level.
• IX (Intent Exclusive): Planning to acquire X lock at finer level.
• SIX: Shared lock with intention for exclusive. Commands & Behavior:
• SQL Server automatically sets intent locks; no direct use needed.
• However, you can observe them:
Sai Reddy
saireddy-dotnetfs
5. Schema Locks (Sch-S, Sch-M)
• Sch-S (Schema Stability): Acquired during query compile.
• Sch-M (Schema Modification): Acquired during schema change (e.g., ALTER, DROP).
• Blocks: Sch-M blocks all access.
Examples:
6. Bulk Update Lock (BU)
• Purpose: Used during bulk insert with TABLOCK.
• Allows: Higher performance; minimal locking.
• When used: During bulk insert operations with minimal logging.
Commands & Example:
7. Key-Range Lock
• Purpose: Prevent phantom reads by locking ranges.
• Used In: SERIALIZABLE isolation level.
• Locks: Not only existing rows, but also possible future inserts in the range.
Commands & Example:
When Are Locks Used?
• During SELECT with appropriate isolation level.
• On INSERT/UPDATE/DELETE operations.
• When accessing indexes or schema objects.
While running transactions or stored procedures.
Sai Reddy
saireddy-dotnetfs
Monitoring Locks
You can monitor active locks using:
Advantages of Locks
• Maintains ACID properties (esp. Isolation & Consistency).
• Prevents dirty reads, lost updates, and phantom reads.
• Ensures safe concurrent data access.
Disadvantages of Locks
• Can lead to blocking or deadlocks.
• Increases latency and may reduce throughput.
• Requires careful transaction management to avoid performance issues.
Why and When Are Locks Important?
• In multi-user environments with concurrent access.
• In critical transactions that must not be interrupted.
• During data migrations, updates, or reporting jobs to ensure consistent results.
• Especially relevant in OLTP systems where many operations occur simultaneously.
What Happens If You Apply a Lock?
• The locked resource becomes restricted to other transactions.
• Depending on the lock type:
o Other reads may or may not proceed.
o Other writes may be blocked or fail.
• Ensures that no conflicting changes are made until the transaction completes (commits or
rolls back).
Sai Reddy
saireddy-dotnetfs
