Topic lessonTopic: SQL Server performance and concurrency
SQL Server locking, blocking and deadlocks: two-session examples
Locks protect data, blocking is a wait for a conflicting request, and a deadlock is a cycle of dependencies. Follow two sessions through held resources and pending requests instead of treating every slow query as the same problem.
Distinguish a one-way wait from a cycle, then choose an investigation using observations from the same incident.
A session represents server-side work for a connection; a transaction groups changes for commit or rollback. The independent examples below stipulate SQL Server 2022 behavior without connecting to a live database or session.
Separate protection, waiting and a cycle
| Term | Meaning | What it does not establish |
|---|---|---|
| Lock | A mechanism protecting a resource from conflicting work | Whether anyone is waiting |
| Blocking | A request waits behind an incompatible held lock | Whether dependencies form a cycle |
| Deadlock | A cycle prevents the participating tasks from proceeding | The chosen victim or the business impact |
A lock is not itself a fault. A long wait and a circular wait are different findings. Do not diagnose CPU, I/O or network delays as lock waits without evidence.
A holds a resource while B waits
Session A
Has changed document 103 and has not committed.
Session B
Requests an update of the same document 103; its request conflicts with A.
- B → AWaits for protection of document 103 to be released
The arrow identifies the blocking dependency, not a network path or completed sequence. A is not waiting for B.
Example W uses an independent CorpDocs-W database with a conventional disk-based table, READ COMMITTED, READ_COMMITTED_SNAPSHOT (RCSI) OFF, and no escalation or other contention. “Document 103” is a teaching resource label, not an observed KEY, PAGE or OBJECT lock.
| Point | A | B |
|---|---|---|
| W1 | Begins a transaction and updates 103 | Has not requested the resource |
| W2 | Remains uncommitted and retains protection | Requests an update of 103 and waits |
| W3 | Ends with COMMIT or ROLLBACK | Can proceed after incompatible protection is released |
Held resources can turn the wait into a cycle
Session A
Holds 103 and requests 104, held by B.
Session B
Holds 104 and requests 103, held by A.
- A → BWaits for 104 to be released
- B → AWaits for 103 to be released
D is independent of W, not an automatic continuation of it. Both holdings and requests are stipulated, establishing the cycle.
| Point | A | B |
|---|---|---|
| D1 | Updates and retains 103 | Not started |
| D2 | Still retains 103 | Updates and retains 104 |
| D3 | Requests 104 and waits for B | Still retains 104 |
| D4 | Still waits for B | Requests 103 and waits for A |
When SQL Server detects a deadlock, it selects a victim transaction, rolls it back and returns error 1205. This diagram does not determine the victim. An application must consider retrying the whole failed transaction, backoff, bounded attempts and effects outside the database; blindly rerunning only its last statement is insufficient.
Reading behavior and engine version change the mechanics
| Condition | Distinction |
|---|---|
| READ COMMITTED, RCSI OFF | Conventional reads use shared locks and may wait behind conflicting writes |
| READ COMMITTED, RCSI ON | Reads use committed row versions as of statement start; not every lock or wait disappears |
| SQL Server 2022 | The conventional mechanism used here; optimized locking is unavailable |
| SQL Server 2025 | Optimized locking is available, default OFF in SQL Server; check database settings and prerequisites |
Optimized locking changes protection mechanics, including transaction-ID locking. Do not transfer Azure SQL defaults to self-managed SQL Server or RDS. Changing RCSI also requires review of read semantics and operational impact, rather than an ad hoc intervention.
Collect related holdings, requests and transaction context
- Bind database, time, waiting session, wait category and blocker to the same incident.
- Follow the chain to its head; inspect open transactions and preceding work, not only the currently running statement.
- For a deadlock, read holdings, requests and victim in xml_deadlock_report. SQL Server system_health captures it by default, subject to access and retention.
- Review impact before improving transaction duration, access order or queries; compare later observations under equivalent conditions.
Identify the state from holdings and waits
Choose an option to see the result and explanation.
Check, then explain in your own words
- Explain who waits for whom in W.
- Trace D around the cycle without swapping the held 103 and 104 resources.
- Do not invent a victim, completion time or live success.
- Include engine version, RCSI and transaction duration among investigation conditions.
Check your understanding: B waits for A. Does that establish that A waits for B?
No. A single dependency does not establish a cycle.
Inspect both transactions’ holdings and requests at the same point. A deadlock conclusion requires evidence of a cycle.
Sources and conditions checked
Primary references are collected here with their scope, conditions and corresponding sections. Independent expert review and live-environment validation have not been performed.
Scope and conditionsConventional locks, READ COMMITTED/RCSI, row versioning and NOLOCK conditions; not live validation.
Used in this lessonSeparate protection, waiting and a cycle Reading behavior and engine version change the mechanics Collect related holdings, requests and transaction context Identify the state from holdings and waits
- Microsoft: Deadlocks guideChecked:
Scope and conditionsCycles, victim rollback/error 1205, xml_deadlock_report, system_health and retry considerations under documented SQL Server conditions.
Used in this lessonSeparate protection, waiting and a cycle Held resources can turn the wait into a cycle Collect related holdings, requests and transaction context Identify the state from holdings and waits
Scope and conditionsConflicting holdings/requests, head blockers and open-transaction investigation; fictional sessions are not observations.
Used in this lessonSeparate protection, waiting and a cycle Collect related holdings, requests and transaction context Identify the state from holdings and waits
- Microsoft: Optimized lockingChecked:
Scope and conditionsSQL Server 2025 availability/default OFF, ADR prerequisite and RCSI-dependent LAQ; distinguish SQL 2022 and Azure defaults.
Used in this lessonReading behavior and engine version change the mechanics