The Read-Blocking Trap: Why SQL Isolation Settings Matter More Than Your Loop.

I was checking a system where users were complaining about random UI freezes. When they click a simple action, the page would spin for a while, but if they tried again, it was instant. The culprit was a heavy background billing run that was updating rows and the default SQL isolation was forcing innocent read only users to stand in the line to wait for those write locks to clear.

⚠️ The Problem: When readers wait on writers
We spend a lot of time optimizing AL code, but we often forget about SQL isolation behavior. By default, when a write transaction is active, SQL Server locks those resources. Any read query trying to access that same data is blocked.
This creates a massive tail-latency problem. Your queries aren’t actually slow; they are just sitting in a queue waiting for a lock to release. The result? Unpredictable timeouts, frustrated users, and a job queue that slows to a crawl during peak hours.

🛠️ The Fix: Read Committed Snapshot + Surgical AL Transactions
If you control the SQL server (on‑prem) – enable RCSI for the BC database. That immediately reduces read-waits caused by write locks.
Regardless of hosting – design AL to be read‑friendly:
Keep write transactions short and explicit. Commit early for non‑critical persistence.
Avoid rendering or heavy CPU work inside transactions.
Use read patterns that minimize lock escalation (prefetch keys, use FindSet/Next properly, and avoid row‑by‑row updates inside read loops).

☣️ The Trap
While enabling RCSI OnPremise is a magic bullet for read blocking, it shifts the workload to tempdb to store row versions. If your SQL server is already starved for disk I/O, watch out you might swap lock contention for disk bottlenecks.

💡 The Bottom Line
Make reads cheap and writes short. If you can’t change SQL isolation (SaaS), design AL to avoid long transactions. If you can (on‑prem), RCSI is a powerful lever – but watch tempdb.