SQL Server Deadlock on a Materialized View: Root Cause Analysis and Fix

During peak business hours, a random SQL Server deadlock started causing slowness and intermittent failures in one of our busiest core services. The failures were rare enough to resist local reproduction, yet showed up frequently in customer-facing workflows.

Everything pointed toward the usual suspects at first: lock ordering, lock escalation, overly broad transactions. The deadlock graph revealed something less common: a four-session, three-page lock conversion cycle inside indexed-view maintenance.

Background

One core table sits on the hottest path in the application. Every search request reads from it. Scheduled batch jobs continuously perform bulk updates against the same rows. During peak traffic, thousands of concurrent read and write operations compete for the same underlying pages.

Direct queries against the base table were too slow for search latency targets, so an indexed view sat in front of the busy rows:

SELECT ...
FROM IndexedView WITH (NOEXPAND)

NOEXPAND forced SQL Server to read the materialized indexed view instead of expanding it back into the base table. Search latency improved. Concurrent writers still had to keep the base table, and therefore the indexed view, consistent.

Symptoms

Deadlocks spiked during batch processing windows. Connection timeouts were repeatedly logged, a consequence of multiple sessions participating in the deadlocks and exhausting the connection pool.

Retries usually succeeded on the second attempt. That masked most failures, but it increased latency, amplified database load during batch windows, and treated the symptom rather than the underlying concurrency issue.

CPU stayed normal. Blocking sessions were short-lived and rarely stacked into long wait chains. Monitoring didn't show a broad lock-contention crisis. Only specific combinations of bulk writers and indexed-view readers failed. Unrelated read paths kept working fine, and writes outside the hot table were fine too.

Investigation

Rather than jump to a fix, I worked through the usual hypotheses one by one.

Hypothesis 1: Lock escalation

Bulk updates often push SQL Server past its lock threshold. When that happens, row or page locks escalate toward an object or table lock, and the deadlock surface becomes much broader.

If escalation had been the cause, the deadlock graph would have shown OBJECT, TABLE, or PARTITION resources. It didn't. Every contended resource across every session was a PAGE. There was no table-level lock acquisition, and no escalation event correlated with the victims. Ruled out.

Hypothesis 2: Lock ordering

A classic circular wait looks like this: transaction A locks table 1 and waits on table 2, transaction B locks table 2 and waits on table 1, and neither can proceed.

I checked execution plans, ORM-generated SQL, and call paths. Every session, writer and readers alike, accessed objects in the same statement-level order. The deadlock graph agreed at first glance: no reverse ordering across different tables in the SQL itself.

Hypothesis 2 appeared ruled out. It turned out to be correct at one level and incomplete at another, for reasons that only showed up once I read the actual resource list instead of the summary.

Hypothesis 3: Execution plans

Plan changes can widen scans, change lock granularity, or pull unexpected indexes into the path. I compared plans for the failing readers and writer across healthy and failing windows.

Plans were stable. Predicates and indexes matched expectations. NOEXPAND continued to hit the indexed view as designed. Nothing in the plan cache explained a new circular wait across different objects. Ruled out.

Reading the deadlock graph

Here is where the tidy two-session story breaks down. The real graph had four sessions and three pages, not two of each.

One session was the writer, mid-UPDATE, holding locks on two different pages: Page A and Page B. Two sessions were readers, each already holding a shared (S) lock on a third page, Page C, from earlier in their own scan. A fourth session was a second, unrelated reader, blocked on Page A behind the writer, with no path back into the cycle at all.

A simplified, anonymized resource node from the actual graph. This is Page C:

<!-- Resource names have been masked, to protect product details -->
<pagelock
  fileid="1"
  pageid="..."
  dbid="..."
  objectname="dbo.IndexedView_PageC"
  mode="SIU"
>
  <owner-list>
    <owner id="reader2" mode="S" />
    <owner id="reader3" mode="S" />
  </owner-list>
  <waiter-list>
    <waiter id="writer"
            mode="IX"
            requestType="convert" />
  </waiter-list>
</pagelock>

Two readers already own S on this page. The writer already owns a compatible lock and is requesting conversion to IX. It can't proceed, because both readers still hold S.

That's one half of the picture. The other half is on Page B, where the roles reverse:

<pagelock
  fileid="1"
  pageid="..."
  dbid="..."
  objectname="dbo.IndexedView_PageB"
  mode="IX"
>
  <owner-list>
    <owner id="writer" mode="IX" />
  </owner-list>
  <waiter-list>
    <waiter id="reader2" mode="S" requestType="wait" />
    <waiter id="reader3" mode="S" requestType="wait" />
  </waiter-list>
</pagelock>

Here the writer already owns IX, fresh, not a conversion, and both readers are queued behind it waiting for a plain S grant they need to keep scanning.

The full cycle, across all three pages:

Two interlocking cycles share the writer as the common node. Writer and Reader 2 cycle through Page B and Page C: the writer's IX on Page B blocks Reader 2's request for S, and Reader 2's S on Page C blocks the writer's conversion request there. Writer and Reader 3 form the same shape through the same two pages.

Reader 1 is different, and worth calling out on its own. It's blocked on Page A behind the writer's IX, but nothing in the graph shows the writer waiting on Reader 1 in return. Reader 1 isn't structurally part of either cycle. It's stuck for the same reason everything downstream of a deadlock is stuck: the writer it depends on is never going to free up, but Reader 1 itself contributes nothing to the standoff. SQL Server still picked it as a victim, because from Reader 1's side the wait was never going to resolve either.

Why "consistent lock ordering" didn't save us

This is the nuance that got missed on the first pass, and it's why the ordering check passed clean while the deadlock still happened.

Application-level lock ordering, the order the SQL statements touch tables in, was consistent across every session. That check was correct, and it still wasn't sufficient. Even when every session issues SQL in the same logical order, application code has no control over the order in which the storage engine acquires locks on individual pages while maintaining an indexed view.

Indexed view maintenance happens inside the storage engine, not as SQL visible to the application. The storage engine acquires page locks according to its physical access path, which can differ from the logical ordering implied by the SQL statement. The deadlock graph showed exactly that kind of page-level interaction: two readers, each partway through scanning different subsets of view pages, ended up interleaved with maintenance in a way no amount of consistent SQL-level ordering controls for.

The lock ordering check answered a narrower question than it looked like it answered. It rules out ordering violations visible and controllable in application code. It says nothing about the order the storage engine touches pages internally while maintaining an indexed view, and that's exactly where this cycle formed.

Real root cause

A multi-page lock conversion cycle, created during indexed-view maintenance.

What the graph showed before the convert

Under READ COMMITTED, readers acquire shared locks to prevent dirty reads. Both readers had already acquired S on Page C earlier in their own scans, before the writer ever reached that page. The writer, elsewhere in its own execution, had already acquired IX on Pages A and B to perform its update.

By the time the writer's maintenance work reached Page C and needed to convert its lock there to IX, both readers already owned S on it. By the time each reader's scan needed a fresh S grant on Page B, the writer already owned IX there. The order in which each session happened to reach each page is what closed the loop.

Why this is harder to see than the classic case

If this were two sessions and one page, the fix would be obvious from the graph alone. With four sessions and three pages, the cycle isn't visible from any single resource node in isolation. You have to walk the owner and waiter lists across every node in the resource list and follow the chain by hand.

Why cycles like this are uncommon, and hard to reproduce locally

Several conditions had to align at once:

1. Two or more readers had to already hold S on the same page the writer would later need to convert on.
2. The writer had to already hold IX on a different page those same readers still needed.
3. None of the sessions could finish early, or the loop would have broken on its own.

Under light load, readers finish and release before a writer's maintenance work reaches the same pages, or the writer finishes before readers arrive. Under heavy mixed read and write pressure on a narrow set of hot pages behind an indexed view, that window widens enough for the cycle to close. That's why this surfaced in production but resisted a simple local repro. It needed batch-window concurrency across multiple real sessions, not a single slow query against a single page.

Why the indexed view changed everything

Without the indexed view, search queries scanned or sought rows directly against the base table. Locking followed ordinary read patterns, writers updated the same table, and the read and write overlap was spread across the access patterns the base table encouraged. Contention was usually tolerable.

With the indexed view and NOEXPAND, readers no longer expanded the view at query time, and every one of them landed on the same narrow set of materialized pages instead. The writer still had to maintain those pages, in an order dictated by the storage engine, not by the readers' query shape. During peak batch windows, several sessions ended up holding compatible shared locks on overlapping storage at once, and the writer's maintenance path needed to convert through exactly those pages to finish.

Why NOLOCK was never an option

NOLOCK inevitably came up as a potential workaround during the investigation.

It would reduce shared locking, but it fundamentally changes read semantics. NOLOCK is READ UNCOMMITTED: dirty reads, non-repeatable reads, and phantoms are all on the table.

The Search API needed a consistent transactional view across statements inside a transaction. Returning faster, possibly wrong answers wasn't acceptable. Deadlocks would drop, but correctness would regress, regardless of whether the underlying deadlock was the simple two-session kind or the four-session cycle found here.

Evaluating solutions

I needed readers that don't take shared locks on data rows, at any page, in any cycle shape. Writers that keep existing READ COMMITTED behavior. Transaction-level consistency for multi-statement reads. Minimal application churn.

RCSI lost because its snapshots are per statement, not per transaction. A later statement in the same transaction can see newer committed data than an earlier one did. I needed one snapshot for the whole transaction, which is what Snapshot Isolation gives you and RCSI doesn't.

Per-connection SET TRANSACTION ISOLATION LEVEL SNAPSHOT on every borrow meant two round trips per checkout and a fragile lifecycle. A dedicated Hikari pool with connectionInitSql avoided that tax.

Why Snapshot Isolation solved this deadlock

Many readers will leave thinking Snapshot fixes deadlocks generally. It doesn't.

Snapshot Isolation does not eliminate writer-writer deadlocks, doesn't eliminate update conflicts (SQL Server error 3960 still applies), and doesn't eliminate lock ordering deadlocks across different resources.

It solved this case, both interlocking cycles at once, because the deadlock depended on shared locks acquired by readers on data pages, at multiple pages, across multiple sessions, and Snapshot readers don't acquire shared locks on versioned data rows.

Under Snapshot, a reader consults row versions in tempdb instead of taking S locks that later collide with a writer's conversion elsewhere in its maintenance path. Remove the S locks from Page C for both readers, and the writer's convert request there has nothing left to wait on. Remove the readers' need for a fresh S grant on Page B, and the writer's IX there stops blocking anyone. Both halves of the cycle disappear from the same underlying change, not two separate fixes.

Implementation

Grails already supports multiple datasources. I added a read-only Snapshot pool alongside the existing write pool.

Datasource config, conceptually:

dataSources:
  snapshot:
    readOnly: true
    ...
    properties:
        connectionInitSql: "SET TRANSACTION ISOLATION LEVEL SNAPSHOT"

HikariCP runs connectionInitSql once, when the physical connection is created, not on every checkout. SQL Server keeps isolation level as session state, so every borrow from that pool is already Snapshot-ready. No per-request SET chatter.

To keep writes off that pool, I added a custom annotation:

@SnapshotReadOnly

It composes @ReadOnly and @Transactional against the Snapshot datasource internally. Read services opt in explicitly. Write services keep the default datasource and never see Snapshot connections in normal paths.

If something still tries to write under Snapshot, SQL Server raises error 3960, a snapshot update conflict. The annotation turns that footgun into a reviewable design choice instead of an accident waiting for production. Exhaustive end-to-end and integration tests were added to close the remaining gaps.

Operational notes

Snapshot Isolation isn't free. Each modified row versions into tempdb, and Snapshot readers consult that version store instead of taking locks. Before enablement I sized version-store growth under expected write volume, about 4 GB in projections. Existing tempdb capacity covered it.

Deployment and validation

I enabled Snapshot Isolation during a maintenance window, when batch write volume was predictable and on-call coverage was explicit.

The rollout was phased:

1. Enable database-level Snapshot support, if not already on, and confirm tempdb headroom.
2. Deploy the Snapshot datasource with connectionInitSql, but route only one low-risk read path first.
3. Compare deadlock rate, p95 search latency, and tempdb growth against the prior week.
4. Migrate remaining read paths once behavior stayed stable across a full batch window.

Deadlock rates dropped immediately on the migrated paths, including the multi-session cycle shape described above. Search latency and tempdb utilization stayed within the limits I had modeled. No increase in error 3960 on write paths, which confirmed writes were still on READ COMMITTED.

Rollback plan

Rollback was straightforward: point read services back to the original datasource. No write-path changes meant no data migration and no schema rollback. The Snapshot pool could sit idle without affecting writers.

Planning it that way helped. A fix that's hard to reverse is a fix I hesitate to ship.

Monitoring

Alerting was set for tempdb usage above 10 GB, with DMVs for sharper signals: sys.dm_tran_version_store_space_usage and sys.dm_db_file_space_usage.

Additional monitoring covered SQL Server error 3960 (Snapshot connections used for writes), deadlock count during batch windows to confirm the reader-S-lock cycles stayed gone, search error rate and retry counts on the Snapshot read pool, and version store cleanup latency. Long-running Snapshot transactions can delay version-store cleanup, so monitoring transaction duration mattered as much as monitoring version-store size.

Future improvements

The dual-pool design works inside one Grails app today. The cleaner long-term plan is service separation: a dedicated read service on Snapshot connections, and a dedicated write service on Read Committed connections. Architecture would then enforce the boundary that @SnapshotReadOnly currently encodes in code, CQRS-style, with no accidental mixed pool usage and less datasource ceremony in day-to-day feature work.

Why this matters beyond one deadlock

The specifics here are SQL Server and Grails, but the useful lessons generalize to any concurrency bug that hides behind a plausible-looking check:

• A check that passes at the application level can still be blind to what happens one layer down.
• A deadlock graph's summary view and its full resource list can tell different stories.
• Not every name in a victim list is a participant in the cycle that caused it.

A check that passes at the application level can still be blind to what happens one layer down

Lock ordering was genuinely consistent in the SQL the application issued. That check wasn't wrong, it was just answering a narrower question than it looked like it was answering. Indexed-view maintenance happens inside the storage engine, and nothing in application-level SQL controls the order it touches pages in. Confirming a check is correct isn't the same as confirming it covers the failure mode you're worried about.

A deadlock graph's summary view and its full resource list can tell different stories

Every single resource in this graph was a PAGE, and every conflict was a lock conversion. That pattern, repeated across more than one page, was the actual story. It only showed up by walking the owner and waiter lists on each resource node individually, not by reading whatever summary a tool renders on top of the raw graph.

Not every name in a victim list is a participant in the cycle that caused it

Reader 1 was chained behind the writer, not part of either cycle. Treating every victim as equally implicated would have wasted time looking for a fourth relationship that didn't exist. Separating who is mutually blocking whom from who is just downstream of that is what makes a graph with more than two sessions tractable.