Dynamics NAV developer on retainer: Codeunit 50210 MODIFY loop on 11,294 Sales Shipment Header records holds single SQL Server transaction for 52 minutes — warehouse receipt posting blocked during December month-end — Microsoft Dynamics NAV developer on monthly retainer

Microsoft Dynamics NAV 2013 R2 • SQL Server 2012 • C/AL • Codeunit 50210 • Table 110 • FINDSET/MODIFY • COMMIT omission • SQL Server row lock contention • retainer billing

At Riverside Timber & Lumber, the December month-end freight billing run produced 35 to 40 error dialogs in the warehouse during purchase receipt posting. Warehouse supervisors paging the Microsoft Dynamics NAV developer retainer reported that receipt confirmation was failing intermittently for the entire 52 minutes the billing codeunit was running — then clearing immediately once the codeunit finished. The retainer engagement traced the disruption to a single missing COMMIT call in a NAV C/AL loop.

Riverside ran Microsoft Dynamics NAV 2013 R2 (Build 35942) on SQL Server 2012 Standard, with a custom Codeunit 50210 “Shipment Completion Poster” written by a prior contractor in NAV 2013 early in the company’s growth. The codeunit ran on the last business day of each month, triggered by the freight billing manager from a custom NAV menu. Its purpose was to mark completed monthly shipments as freight-invoiced: it read all records from Table 110 (Sales Shipment Header) where a custom Boolean field — Field 50000 “Freight Invoiced” — was FALSE and “Shipping Date” fell within the just-completed month, set “Freight Invoiced” to TRUE on each record, and called MODIFY. No COMMIT appeared anywhere in the loop.

How C/AL transaction scope works in NAV

In NAV C/AL, every database write operation — MODIFY, INSERT, DELETE — is part of the current open transaction unless COMMIT is called explicitly. NAV’s application server (the NST, NAV Service Tier) holds one transaction per session. When a codeunit calls MODIFY on a record, that write is added to the session’s transaction but not committed to SQL Server until the codeunit either calls COMMIT or exits cleanly (at which point NAV issues an implicit commit). Until that commit, SQL Server holds an exclusive row lock (KEY lock) on every modified record. The locks are not released incrementally as the loop progresses — they accumulate for the entire codeunit run.

For small volumes, this is invisible. A codeunit processing 200 records completes in two seconds; the 200 locks are held and released before anyone notices. For large volumes, the single-transaction design becomes a multi-minute lock hold. Every other SQL Server session trying to read or write the same table with any lock incompatibility is blocked for the duration of the entire run.

How the volume grew and the symptom pattern escalated

When Codeunit 50210 was written in early 2013, Riverside processed approximately 800 shipments per month. The codeunit ran in under 4 minutes. No one noticed the missing COMMIT.

By January 2015, monthly shipment volume had grown to 3,847. The codeunit took 14 minutes. A few warehouse users reported occasional “Another user has modified the record” messages during the run, but the messages cleared on retry and were attributed to user error.

By September 2016, volume reached 8,219 shipments per month. Run time: 31 minutes. The freight billing manager started scheduling the run at 7:00 PM to avoid overlapping with warehouse shift end. Complaints continued but were absorbed.

By December 2017, volume reached 11,294 shipments. December had a higher shipping volume than any prior month due to seasonal orders. The codeunit ran for 52 minutes. Warehouse supervisors were still working at 6:30 PM posting purchase receipts for the day’s incoming inventory. For the full 52 minutes, 30 to 40 percent of those receipt postings either failed with a deadlock error dialog or stalled for 15 to 30 seconds before completing. Thirty-five to forty error dialogs were counted across the warehouse team. The December purchase receipt run was not completed until 8:10 PM — the supervisors had to return and repost after the billing codeunit finished.

Diagnosis: SQL Server lock views during the run

The NAV developer retainer connected to SQL Server Management Studio during a reproduction run (using a copy of the December data in a test environment). Twenty-five minutes into the run, a query against sys.dm_tran_locks confirmed the accumulation:

SELECT COUNT(*) AS lock_count,
       resource_type,
       request_mode,
       request_status,
       request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_type = 'KEY'
  AND  request_mode  = 'X'
GROUP BY resource_type, request_mode, request_status, request_session_id
ORDER BY lock_count DESC;

Result at the 25-minute mark: 8,712 exclusive KEY locks on SPID 87 (the NAV service tier session running Codeunit 50210), all with request_status = GRANT. A second query against sys.dm_exec_requests showed 14 warehouse SPIDs (88–104) with wait_type = LCK_M_X and wait_time between 8,200ms and 29,400ms — all waiting on the same table being modified by SPID 87. By the end of the 52-minute run, the lock count had grown to 11,294 — every Sales Shipment Header record modified in the loop, held exclusively for the entire duration.

The source of the problem in C/AL was a single review of Codeunit 50210:

// Codeunit 50210 — original code (no COMMIT in loop)
ShipmentHeader.SETRANGE("Freight Invoiced", FALSE);
ShipmentHeader.SETRANGE("Shipping Date", StartDate, EndDate);
IF ShipmentHeader.FINDSET(TRUE, TRUE) THEN
  REPEAT
    ShipmentHeader."Freight Invoiced" := TRUE;
    ShipmentHeader.MODIFY;
  UNTIL ShipmentHeader.NEXT = 0;

FINDSET(TRUE, TRUE) in NAV C/AL means: find the first matching record, lock all matching records in the set for update (TRUE), and allow modification (TRUE). The MODIFY call updates each record in the SQL Server transaction but does not release the lock. The loop runs to completion before the implicit commit at codeunit exit releases all 11,294 locks simultaneously.

The fix: COMMIT every 1,000 records with restart tracking

The NAV developer retainer added COMMIT at intervals of 1,000 records and a restart mechanism so that a mid-run failure would not force a full re-run from the beginning:

// Codeunit 50210 — fixed code (COMMIT every 1,000 records)
CommitCounter := 0;

ShipmentHeader.SETRANGE("Freight Invoiced", FALSE);
ShipmentHeader.SETRANGE("Shipping Date", StartDate, EndDate);

// Resume from last committed shipment if prior run was interrupted
IF FreightPostLog.GET(RunDate) THEN
  IF FreightPostLog."Last Committed Shipment No." <> '' THEN BEGIN
    ShipmentHeader.SETFILTER("No.", '>%1', FreightPostLog."Last Committed Shipment No.");
    MESSAGE('Resuming from shipment %1', FreightPostLog."Last Committed Shipment No.");
  END;

IF ShipmentHeader.FINDSET(TRUE, TRUE) THEN
  REPEAT
    ShipmentHeader."Freight Invoiced" := TRUE;
    ShipmentHeader.MODIFY;
    CommitCounter := CommitCounter + 1;
    IF CommitCounter MOD 1000 = 0 THEN BEGIN
      // Log the last committed shipment number for restart
      IF NOT FreightPostLog.GET(RunDate) THEN BEGIN
        FreightPostLog.INIT;
        FreightPostLog."Run Date" := RunDate;
        FreightPostLog.INSERT;
      END;
      FreightPostLog."Last Committed Shipment No." := ShipmentHeader."No.";
      FreightPostLog."Last Committed At"           := CURRENTDATETIME;
      FreightPostLog.MODIFY;
      COMMIT;
      CommitCounter := 0;
    END;
  UNTIL ShipmentHeader.NEXT = 0;

// Final commit for the last partial batch
COMMIT;

The restart logic relies on the fact that setting “Freight Invoiced” to TRUE is idempotent — re-running a shipment that was already marked does no harm; the FINDSET filter SETRANGE("Freight Invoiced", FALSE) already excludes committed records. The SETFILTER("No.", '>%1', ...)... restart filter skips already-committed records if Sales Shipment Header’s primary key (“No.”) is sequential, which it is for this client. For installations where the primary key is not guaranteed sequential, the restart filter must be based on the explicitly logged “last committed” boundary or a separate processed-flag field.

Choosing the right COMMIT interval

The COMMIT interval for a NAV C/AL MODIFY loop involves a tradeoff between three competing factors:

1. Maximum concurrent lock hold. A COMMIT interval of N means at most N exclusive row locks are held at any given moment. For a table that concurrent users access heavily, a smaller interval (500–1,000) reduces the window during which users are blocked.
2. COMMIT overhead. Each COMMIT call issues a SQL Server transaction commit, which has overhead (log flush, lock release, transaction cleanup). For very small intervals (every 10 records), commit overhead can dominate the run. For intervals of 500–2,000, commit overhead is negligible relative to the MODIFY work.
3. Restart granularity. A larger COMMIT interval means more work is re-done if a run is interrupted and restarted. For most month-end batch runs, 1,000-record intervals are fine — restarting from a 1,000-record boundary costs at most 999 re-processed records, which is trivial.

For Riverside Timber, a COMMIT interval of 1,000 was chosen. After the fix: peak SQL Server KEY lock count never exceeded 1,000. Run time fell from 52 minutes to 6 minutes (COMMIT overhead is negligible; the reduced lock contention eliminated the SQL Server wait cascade that had been slowing the original run). Warehouse posting errors: zero for the next three month-end billing runs.

Why COMMIT omission is a recurring NAV retainer issue

The C/AL implicit-transaction behavior is well-documented in the NAV developer guide, but its operational consequences are invisible at small data volumes. Every NAV installation that has grown its data volume since the original customization development is a candidate for this issue. Codeunits written for 500 records have no structural warning when they run against 5,000 records four years later. The SQL Server lock contention only becomes visible when the run duration and lock count cross a threshold where concurrent users are working the same table at the same time.

The pattern is particularly common in month-end batch processing codeunits: inventory revaluation, statement generation, commission calculation, shipment marking — all run at peak user-activity times (end of month, end of quarter) when warehouses, sales teams, and accounting are all working simultaneously. The retainer pattern is: first complaint in year 2 of the installation (run takes 10–15 minutes, a few users complain), ignored or worked around; growing disruption in year 3 (20–35 minutes, recurring complaints, workarounds fail). A retainer developer who has seen this pattern can diagnose and fix it in 4–6 hours.

How HourTab helps Microsoft Dynamics NAV developer retainers

The Riverside Timber engagement totaled 4.5 hours: SQL Server lock analysis (1.0h, querying sys.dm_tran_locks and sys.dm_exec_requests during a reproduction run on test data, confirming 11,294 exclusive KEY locks on SPID 87 and 14 warehouse SPIDs blocked in LCK_M_X wait), C/AL source review (1.0h, identifying the FINDSET loop with no COMMIT in Codeunit 50210, confirming the implicit-transaction scope), COMMIT fix implementation (1.5h, adding COMMIT at 1,000-record intervals, implementing the restart tracking via Table 50210 “Freight Post Log”, handling the final partial-batch commit after the REPEAT/UNTIL loop exits), and testing (1.0h, confirming peak lock count at 1,000, run time at 6 minutes, and zero warehouse posting errors on a test run with full December data volume). Ongoing retainer monitoring: 0.1h per month to confirm the billing codeunit ran without errors and check that no new high-volume MODIFY loops have been added.

In a HourTab retainer log, the investigation session reads: “Codeunit 50210 COMMIT omission: December run 52 minutes, 35-40 warehouse error dialogs; sys.dm_tran_locks SPID 87: 11,294 KEY locks (X, GRANT) during run; 14 warehouse SPIDs blocked LCK_M_X up to 29s; source review: FINDSET loop on Table 110, MODIFY per record, no COMMIT; fix: COMMIT every 1,000 records (CommitCounter MOD 1000); restart table Table 50210 Freight Post Log added (Last Committed Shipment No.); test: peak locks 1,000 (vs 11,294), run time 6m (vs 52m), 0 warehouse errors for 3 month-end runs; 4.5h.” Riverside Timber’s freight billing manager can see the December disruption, the SQL Server root cause, and the fix that converted a 52-minute lock storm into a 6-minute clean run.

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: Microsoft Dynamics NAV developer retainers

See also: SAP ABAP developer retainer · Progress 4GL developer retainer · Baan developer retainer