Minimizing duplicate key errors when multiple processes encounter race condition situations via a shell-insert / lock-and-update concurrency pattern

Nextworld Platform How Tos
1

Purpose

This pattern reduces the likelihood of duplicate-key and Modified-By-Another-User failures on tables where records need to be created and there may be multiple processes wanting to create the same record. We have seen this frequently in Directory when a record needs created in Directory on behalf of an external system. While race conditions between the processes creating such records can’t be eliminated using this approach, the window of contention can be shortened. Rather than the passive “fetch, and insert if absent” approach, which leaves the possibility of failure open for the life of the entire transaction, this pattern narrows the failure window to a single insert.

The pattern

  1. Fetch and lock the target record (Limit 1).
  2. If it doesn’t exist, call a logic block in a new transaction boundary and insert a shell record that satisfies the unique criteria. The shell doesn’t need to be complete - it only needs enough information to commit and to signal to other transactions that the record now exists.
  3. Swallow any error the insert throws (Adopt All Messages = false), on the understanding that it should only ever fail on a duplicate key. (Take more care here if pre-insert triggers exist.)
  4. Once that insert commits, fetch and lock the record in the parent transaction and update it as needed.

This lets multiple processes update the same record concurrently without any of them having to decide between insert and update, and with no path to failure. A lock timeout is still possible if too many transactions contend for the same record at once.

Example: reference-record generation

// Code that must obtain/generate a reference record
Fetch and Lock from ReferenceRecords (unique fetch expression; Limit 1)
{
    // record exists — update if necessary
    // store id for this transaction
}
No data
{
    Call InsertReferenceRecord (new DB transaction; Adopt All Messages = false)
    {
        Insert record into ReferenceRecords
    }
    Fetch and Lock from ReferenceRecords (unique fetch expression; Limit 1)
    {
        // update if necessary
        // store id for this transaction
    }
}
...
// Insert into [target table] using the ReferenceRecord

Reading it:

  • The first fetch/lock checks whether a control reference record already exists. If it does, use it for this transaction.
  • If it doesn’t, the new transaction boundary call inserts the record. This shortens the duplicate-key window to the duration of that single insert rather than the duration of the whole parent transaction.
  • If a duplicate error happens anyway, it is swallowed and ignored.
  • On return, the second fetch/lock/update finishes the work of creating the record.

Either route guarantees a reference record has been created for the transaction without failure by the time the target insert runs — as long as no Modified-By-Another-User or similar system error intervenes.

Notes and considerations

  • The specific error doesn’t matter. If the insert succeeds, a record is committed and immediately locked. If it fails — duplicate key or otherwise — the failure is ignored and the code fetches again, waiting to obtain the lock.
  • Origin. The pattern comes from Item Balances (the WriteItemBalance code). When many concurrent Item Ledger Transactions need the same Item Balances record and each recognizes it must insert, this pattern keeps any of them from failing.
  • Cost. It adds one or two extra DB operations per transaction whenever a transaction thinks it needs to insert. On low-volume tables this isn’t worthwhile — it rarely saves a user from an error and always adds slightly to lock wait times. Reserve it for high-volume tables that must avoid Modified-By-Another-User and duplicate-key errors, accepting the occasional lock timeout in exchange.
  • Deadlocks are not a concern as long as only one record is locked at a time. Fetch and Lock should always use Limit 1 unless there is a specific, well-understood reason to do otherwise.
1 Like