What are the main lock types in SQL Server (shared, exclusive, update, intent)?
Quick Answer
Shared (S) — for reads; multiple readers can hold it. Exclusive (X) — for writes; incompatible with everything. Update (U) — taken while deciding whether to modify a row, prevents a deadlock pattern between two would-be updaters. Intent (IS/IX/IU) — placed higher up (page, table) to signal that a lower-level lock exists, so the engine doesn't have to scan for conflicts.
Shared locks are for reads and multiple readers can share them. Exclusive locks are for writes and block everyone else. Update locks are interesting — they're taken while a statement is figuring out whether it's going to modify a row, and only one transaction can hold one, which prevents the deadlock you'd otherwise get from two transactions both reading then trying to write the same row. Intent locks sit at the page and table level to advertise that there's a finer-grained lock underneath, so the engine can check for conflicts quickly. And lock escalation rolls lots of row locks up into a table lock once you cross a threshold.
- Compatibility: S is compatible with S and U (not X). X is compatible with nothing. U is compatible with S only.
- Update lock's purpose: if two transactions both take an S lock intending to upgrade to X, they deadlock. The U lock is only granted to one, so the other waits — a block, not a deadlock.
- Granularity: row → page → table. Lock escalation converts many fine-grained locks into one coarse lock (default threshold ~5000 locks) — reduces overhead but increases blocking.
- Snapshot isolation / RCSI avoids many read locks by using row versions.