Duplicate Device Serial Numbers in Test Results: Detect the Collision Before Shipment
Prevent duplicate product serials at allocation, verify the programmed value against that allocation and keep uncertain attempts reserved until reconciled. A test-result database that merely stores the serial reported by a board can faithfully record the same mistaken serial for many units. Uniqueness must be enforced across the intended identity scope before shipment.
The concrete host example uses Python 3.12.14 with SQLite 3.53.1. Eight bounded transaction, constraint and readback-state controls passed in an owned local database. No physical serial was written, no irreversible identifier or credential was provisioned and no factory or shipment system was changed.
Define the allocation scope and owner
Specify what the serial names: complete product, main assembly or another managed object. State whether uniqueness is global within the product program or scoped by a documented namespace. A serial that is unique only within one station’s spreadsheet may collide when another station uses the same sequence.
Assign an authoritative allocator and an explicit relationship to the pre-provisioning unit identity. Include format, case sensitivity, leading-zero treatment and any permitted normalization. Converting every serial to an integer can erase meaningful leading zeros or collapse distinct strings.
Keep the allocation rule separate from authentication. A unique public serial does not prove device ownership or authorize a secure operation. Secrets and irreversible hardware provisioning require their own reviewed process.
Reserve before writing and make request recovery idempotent
Create a durable reservation binding serial, intended unit and allocation-request identity. If the requester loses the response, a retry of the same request should recover the same binding. It should not allocate a second serial merely because the first response was missing.
Reject reuse of a request identity with a different unit or requested meaning. An idempotency key is useful only when the server compares the associated content. Preserve a conflict rather than silently treating the latest request as authoritative.
Use a transaction or equivalent concurrency control for the authoritative reservation. A separate “is this serial unused?” query followed later by an unprotected insert leaves a race. The SQLite transaction reference documents one simultaneous writer and the immediate transaction mode’s possible busy result; the application must handle that result rather than assuming allocation succeeded.
Make missing identifiers fail visibly
The SQLite CREATE TABLE reference distinguishes uniqueness and non-null constraints. In ordinary SQLite tables, some primary-key declarations still allow NULL unless the appropriate non-null or table rules apply. UNIQUE also treats NULL values as distinct.
The host fixture therefore used an explicitly non-null text serial primary key, non-null unique unit identity and non-null unique request identity. These constraints express the narrow example’s one-to-one allocation contract. A real assembly-replacement policy may require additional historical tables rather than weakening uniqueness silently.
Reject blank, malformed and unauthorized namespace values at the application boundary as well. NOT NULL does not reject an empty string, and a syntactically valid serial can still belong to the wrong product program. Database constraints supplement the identity policy rather than defining it completely.
Read the executed concurrency and collision controls
Two SQLite connections opened the same owned file with a zero busy timeout. Connection A began an immediate transaction and reserved S100 for U1 under request Q1. Connection B’s competing immediate transaction was rejected as locked while A held the write transaction.
After A committed, B recovered Q1’s existing S100/U1 binding. Inserts attempting to allocate S100 to another unit, allocate another serial to U1 or use a NULL serial were rejected. The database still contained exactly one allocation row.
These are deterministic local controls, not a throughput benchmark or a distributed allocator test. They establish the exercised SQLite locking/constraint behavior and supplied schema, not resilience to every process crash, storage fault or replicated-service race.
Separate reserved, uncertain and verified states
| Allocation state | Meaning | Reuse rule |
|---|---|---|
| Reserved. | Serial belongs to the intended unit’s approved attempt. | Not available to another unit. |
| Programming outcome unknown. | A write may have reached the device, but completion evidence is missing. | Remain reserved pending reconciliation. |
| Readback confirmed. | The observed value matches the allocation under the qualified readback procedure. | Remain bound to the unit. |
| Retired or replaced. | An approved lifecycle event changed service status or association. | Follow the explicit historical reuse policy; never infer availability from inactivity. |
The fixture changed S100 to outcome_unknown and confirmed that its reservation remained present. It rejected a supplied readback of S999 for U1, then accepted the supplied S100 value and advanced the state. Those were string/state operations; no device supplied the readback.
Together with the earlier transaction and constraint checks, all eight expected controls passed. The example does not include a serial-freeing operation, deliberately avoiding the assumption that a timeout means the identifier was never used.
Qualify the physical readback and clone paths
Read through the deployed product path after the programming operation and any required reset. Establish that the response belongs to the current physical unit and comes from the intended persistent field rather than a command echo, cache or temporary RAM value.
Test clean imaging, repeated allocation requests, interrupted provisioning and replacement assemblies on recoverable development hardware under the approved workflow. Include a factory image accidentally containing a fixed sample serial. A unique allocator cannot prevent duplicates if a later image overwrites every unit with the same constant.
Also test the failure path when the serial field is missing or unreadable. Do not substitute a shared placeholder that can escape into accepted results. Keep the unit in a clearly unresolved state until identity is restored through the authorized process.
Reconcile allocation, readback and shipment evidence
Before release, compare the authoritative allocation with observed product identity and the unit’s accepted test record. Search for one serial associated with multiple active physical units and for one unit unexpectedly associated with multiple active serials.
When a collision is discovered, preserve both records and investigate allocation, cloning, readback freshness and label associations. Do not automatically renumber devices or overwrite old results to make the query return no duplicates.
The deliverable is a reservation/readback/reuse contract with tested concurrency boundaries and a retained identity history. It detects collisions before they become ambiguous field devices, while keeping the consequential act of changing a real identifier inside its separately approved provisioning procedure.
