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 started appearing frequently in customer-facing workflows.

At first glance, everything pointed toward the usual suspects: lock ordering, lock escalation, or overly broad transactions. The deadlock graph ultimately revealed something much less common: a four-session, three-page lock conversion cycle involving indexed-view maintenance.

Background

The product is centered on one core table that 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 data structures.

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 in the application, a consequence of multiple sessions participating in the deadlocks and causing connection pool exhaustion.

Retries usually succeeded on the second attempt. Although that masked most failures, 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 did not show a broad lock-contention crisis.

Interestingly, only specific combinations of bulk writers and indexed-view readers failed. Unrelated read paths kept working fine, and writes outside the hot table were also fine.

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/table lock, and deadlock surfaces become much broader.

If escalation had been the cause, the deadlock graph would have shown OBJECT, TABLE, or PARTITION resources. It did not — every contended resource across every session in the graph was a PAGE. There was also no table-level lock acquisition, and no escalation event correlated with the victims. Hypothesis 1 was ruled out.

Hypothesis 2: Lock ordering

Classic circular waits look like this:

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: there was 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 suddenly 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.

Hypothesis 3 was 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, call them 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 resource node from the actual graph, anonymized. This is the page later referred to as 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>

Read top to bottom: two readers already own S on this page. The writer already owns a compatible lock on the page 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 a different page entirely, 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.

One detail that initially surprised me was that the deadlock graph wasn't a simple "reader blocks writer, writer blocks reader" pattern. The cycle only became obvious after tracing the owner and waiter lists across multiple page resources. Looking at any single page in isolation made the locking appear perfectly reasonable.

Redrawn as the actual cycle, with the real object and process names swapped for generic ones:

Two interlocking cycles share the writer as the common node:

  • W ↔ R2, via Page B and Page C. The writer's IX on Page B blocks R2's request for S. R2's S on Page C blocks the writer's conversion request there. Each waits on the other.
  • W ↔ R3, the same shape, same two pages. R3 also holds S on Page C and also wants S on Page B.

R1 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 R1 in return. R1 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 R1 itself contributes nothing to the standoff. SQL Server still picked it as a victim, because from R1'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 the reason 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 occurs inside the storage engine rather than as SQL visible to the application. The storage engine is free to acquire page locks according to its physical access path, which may 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 that maintenance in a way no amount of consistent SQL-level ordering controls for.

So the lock ordering check passed, and it 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. In this graph, 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, the way the earlier sequence diagram walks a two-table deadlock, just with more hops.

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. All of this had to happen while none of the sessions had finished, so nothing could release early and break the loop.

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/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, and writers updated the same table, but the read/write overlap was spread across the access patterns the base table encouraged. Contention was usually tolerable.

With the indexed view and NOEXPAND, the picture changes:

Readers no longer expanded the view at query time. 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

During the investigation, NOLOCK inevitably came up as a potential workaround.

While it would reduce shared locking, 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 was not acceptable. Deadlocks would drop, correctness would regress.

NOLOCK solved a blocking symptom by abandoning the consistency requirement the application actually had, and it would have done so regardless of whether the underlying deadlock was the simple two-session kind or the four-session cycle actually found here.

Evaluating solutions

I needed:

  • readers that do not 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
OptionProsConsVerdict
NOLOCKLess blockingDirty readsRejected
RCSIEasy database flagStatement-level consistency onlyRejected
Per-connection SnapshotFlexibleExtra round trips per checkoutRejected
Dedicated Snapshot poolCorrect semanticsAdditional datasourceChosen

Why RCSI lost: statement-level snapshots allow a later statement in the same transaction to see newer committed data than an earlier one. I needed one snapshot for the whole transaction.

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
  • eliminate update conflicts (SQL Server error 3960 still applies)
  • 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 do not 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:

@SnapshotReadOnly

Internally it composes @ReadOnly and @Transactional against the Snapshot datasource. 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 (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 is not free. Each modified row versions into tempdb.

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 as below:

  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 is 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
  • sys.dm_db_file_space_usage

Additional monitoring was set up for:

  • SQL Server error 3960 (Snapshot connections used for writes)
  • Deadlock count during batch windows (confirm cycles involving reader S locks stayed gone)
  • Search error rate and retry counts on the Snapshot read pool
  • Version store cleanup latency, to ensure long-running Snapshot transactions weren't delaying cleanup

Long-running Snapshot transactions can delay version-store cleanup, so monitoring transaction duration is just as important 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 separation, no accidental mixed pool usage, less datasource ceremony in day-to-day feature work.

Key takeaways

  • Avoid confirmation bias, especially when a check like "lock ordering was consistent" is true at the SQL level but blind to page order inside indexed-view maintenance.
  • Trust the deadlock graph over folklore, and trust the full resource list over the summary. PAGE + convert, repeated across more than one page, told the real story.
  • A victim isn't necessarily a cycle participant. Some are just chained behind one.
  • NOLOCK is a consistency decision, not a deadlock silver bullet.
  • Pick Snapshot vs RCSI from transaction semantics, not operational convenience.
  • Enforce read/write boundaries in code, and later in service topology, when isolation differs by path.
  • Indexed views can change where contention happens, not just how fast queries run, and can turn a single writer's maintenance path into a cycle across several readers at once.