Avoiding Record Locking Contention: Batch Processing with ReadUncommitted and ReadIsolation

When a background job queue or heavy integration process iterates through open documents in Business Central, it frequently encounters records currently being edited by users. Under default read isolation settings, your background code can stall entirely, waiting for user transactions to release locks. In this installment of The Performance Stack, we examine how explicit read isolation controls in AL prevent lock contention, when this pattern is appropriate, and how to verify its impact on database concurrency.

The Problem

Consider a background job designed to process open Sales Orders. The requirement is straightforward: query all unposted orders where the shipment date is past due, calculate extended metrics, and update staging statuses. During normal processing, when users are actively modifying sales headers in the UI, your job queue task experience intermittent delays. By default AL record retrieval operation apply shared read locks (repeatable read or serializable behaviour on the platform and query patterns) to ensure transaction consistency. When your background routine attempts to read a record that a user or another job currently has open and locked in an active transaction, your read operation blocks. It must wait until the other transaction commits or rolls back.

When AL code executes a Find() or Next() without an explicit isolation override, SQL Server ensures that data read cannot be changed by other transactions until the read transaction completes. This prevents “dirty reads” but introduces read blocking.

Resource Impact

• Throughput: Collapses when concurrent users and background jobs contend for the same master or document tables.
• Execution Time: Increases dramatically not due to CPU or algorithm complexity, but due to wait time (SQL lock wait queues).
• Maintainability: Often misdiagnosed as missing database indexes when the root cause is locking contention.

The Fix

When a batch job only needs to read snapshot data for reporting or preliminary aggregation and will re-fetch or validate the record inside an isolated write transaction later, you can instruct the platform to bypass read locks using ReadIsolation.

Setting SalesHeader.ReadIsolation := IsolationLevel::ReadUncommitted tells the database engine to return data immediately, even if another transaction is modifying it, without placing shared locks or waiting for active write transaction to clear.

When not to use it..

ReadUncommitted exposes your code to uncommited, transitional data changes that might subsequently be rolled back by the user.

• Financial Posting: Never use ReadUncommited when reading ledger entries, customer balances, or inventory availability prior to posting.
• Write Workflows: If your loop immediately updates the records being read, bypassing isolation on the read phase can cause race conditions.

The Bottom Line

Separate read intent from write intent. When designing background tasks, clearly distinguish between exploratory read passes (which can tolerate relaxed isolation) and transactional write passes (which require strict ACID compliance).