Problem Description
Currently, Business Central developers face a major scalability bottleneck when implementing parallel worker patterns. When multiple background sessions (Job Queue) attempt to reserve the next available rows (e.g., via FindSet(true) or UpdLock), it often triggers SQL Lock Escalation.
This leads to two critical issues:
- Blocking Parallelism: Workers wait for each other instead of skipping already locked rows.
- Blocking Inbound Data: The escalated table lock prevents external APIs or users from inserting new records (Inserts), causing timeouts in high-volume scenarios like E-commerce integrations.
Proposed Solution
Introduce a native AL property or method to support the SQL READPAST hint. This would allow a FINDSET operation to silently skip records that are already locked by another session.
Example of a potential new syntax:
// Proposed enhancement
MyTable.SetRange(Status, Status::Ready);
MyTable.ReadIsolation := IsolationLevel::UpdLock;
MyTable.SkipLocked(true); // New property to trigger READPAST
if MyTable.FindSet(50) then
repeat
// Process records without blocking other workers or new inserts
until MyTable.Next() = 0;
Business Value/ Impact
- Infinite Scalability: Enables true "Worker-Queue" architectures where hundreds of sessions can process the same table simultaneously.
- System Stability: Eliminates the "Table Lock" death spiral in high-load scenarios.
- Performance: Reduces overhead for developers who currently use complex workarounds (like GUID-based pre-marking) which are less efficient than native SQL READPAST.