INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

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.

What you will learn

Distinguish a one-way wait from a cycle, then choose an investigation using observations from the same incident.

Prerequisite

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

Read the terms as different concepts
TermMeaningWhat it does not establish
LockA mechanism protecting a resource from conflicting workWhether anyone is waiting
BlockingA request waits behind an incompatible held lockWhether dependencies form a cycle
DeadlockA cycle prevents the participating tasks from proceedingThe 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

Example W: B → A means B waits for A to release

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.

  1. 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.

Two requests concerning document 103
PointAB
W1Begins a transaction and updates 103Has not requested the resource
W2Remains uncommitted and retains protectionRequests an update of 103 and waits
W3Ends with COMMIT or ROLLBACKCan proceed after incompatible protection is released

Held resources can turn the wait into a cycle

Example D: a dependency cycle A → B → A

Session A

Holds 103 and requests 104, held by B.

Session B

Holds 104 and requests 103, held by A.

  1. A → BWaits for 104 to be released
  2. 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.

How the cycle forms
PointAB
D1Updates and retains 103Not started
D2Still retains 103Updates and retains 104
D3Requests 104 and waits for BStill retains 104
D4Still waits for BRequests 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

Example conditions and mechanisms to check separately
ConditionDistinction
READ COMMITTED, RCSI OFFConventional reads use shared locks and may wait behind conflicting writes
READ COMMITTED, RCSI ONReads use committed row versions as of statement start; not every lock or wait disappears
SQL Server 2022The conventional mechanism used here; optimized locking is unavailable
SQL Server 2025Optimized 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

  1. Bind database, time, waiting session, wait category and blocker to the same incident.
  2. Follow the chain to its head; inspect open transactions and preceding work, not only the currently running statement.
  3. 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.
  4. 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.

Example W has only B → A. What follows?
Read the answer and reason

Answer: Blocking exists, but no cycle is shown. · There is no A-to-B dependency in the stipulated facts.

D establishes A → B and B → A. What does the diagram establish?
Read the answer and reason

Answer: Dependencies form a cycle; the victim is unspecified. · The holdings establish a cycle, not the victim-selection conditions.

Does enabling RCSI remove every lock and wait?
Read the answer and reason

Answer: No; separate read behavior from remaining contention. · RCSI changes READ COMMITTED reads, not every source of waiting.

A short current statement belongs to a session blocking others. What next?
Read the answer and reason

Answer: Inspect its open transaction and contemporaneous holdings and requests. · A short current statement does not establish a short transaction or recent lock acquisition.

Check, then explain in your own words

  1. Explain who waits for whom in W.
  2. Trace D around the cycle without swapping the held 103 and 104 resources.
  3. Do not invent a victim, completion time or live success.
  4. 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.