SQL Server Locking Explained

← Home

1. Why Locking is Needed

SQL Server uses locks to maintain data consistency and isolation between transactions. Without locks, multiple users could update the same row simultaneously, causing issues like dirty reads, lost updates, or inconsistent data.

2. Types of Locks

Lock Type	Purpose	Example
Shared (S)	Read-only access. Multiple transactions can read simultaneously.	`SELECT` query
Exclusive (X)	Write access. Only one transaction can modify data at a time.	`UPDATE, DELETE, INSERT`
Update (U)	Prevents deadlocks when a transaction plans to update a row.	Often used with `SELECT ... FOR UPDATE`
Intent Locks (IS, IX)	Show intention to acquire a finer-grained lock (row/page).	SQL Server manages hierarchical locks efficiently
Schema locks	Protect schema changes like adding or altering columns.	`ALTER TABLE`
Bulk Update (BU)	Used during bulk copy operations.	`BULK INSERT`

3. Lock Levels / Granularity

Row-level: Locks a single row.
Page-level: Locks an 8 KB page.
Table-level: Locks the entire table.
Database-level: Rare, locks the whole database.

SQL Server tries to use the smallest possible lock, but may escalate to table-level if too many locks are held (lock escalation).

4. Locks and CRUD Operations

INSERT: Exclusive locks on inserted rows.
SELECT: Shared locks unless using WITH (NOLOCK) or snapshot isolation.
UPDATE: Uses update locks, escalates to exclusive on actual update.
DELETE: Exclusive locks on deleted rows.

5. Deadlocks

A deadlock occurs when two transactions wait on each other’s locks indefinitely:

Transaction A: locks Row 1 → wants Row 2
Transaction B: locks Row 2 → wants Row 1

SQL Server automatically detects this and kills one transaction to resolve the deadlock.

6. Lock Hints

WITH (NOLOCK) – read without locking (may read uncommitted data)
WITH (UPDLOCK) – acquire update lock
WITH (XLOCK) – force exclusive lock

7. Visualizing Locks

Shared Locks (S) – Multiple Reads

Row A
---------
| Data  |
---------
[Transaction 1: S]  Row A  [Transaction 2: S]

Both can read simultaneously; no writes allowed.

Exclusive Locks (X) – Writing

[Transaction 1: S]  Row A  [Transaction 2: S]  --> Transaction 3 waits
[Transaction 3: X]  Row A

Exclusive lock prevents other reads or writes until the transaction completes.

Update Locks (U) – Prevent Deadlocks

Transaction 4: SELECT * FROM Table WHERE ID=1 WITH (UPDLOCK)
[U] Row is locked to prevent exclusive writes by others.
Converts to exclusive lock when UPDATE happens.

Deadlock Scenario

Row A        Row B
------       ------
Transaction 5: X --> Row A, wants Row B
Transaction 6: X --> Row B, wants Row A

Both wait indefinitely. SQL Server resolves by killing one transaction.