Two Bots, One Spreadsheet
A logistics company runs two processes on two runtime resources. Process A exports shipment data to a shared Excel workbook on a network drive at 09:00. Process B reads that workbook at 09:05 to generate customer notifications. Both processes open the workbook with the same service account. On three occasions in the past month, Process B read a partially written file and sent incorrect notifications to 40 customers. The workbook is also opened manually by an analyst for spot checks. There is no locking, no file-version check, and no alerting when Process B finds unexpected row counts. The queue for Process B has no item-level dependency on Process A's completion.
1. Identify the coordination failure and propose a design that removes the race condition between the two processes.
Read the worked answer
The two processes coordinate only by timing, which is not coordination. Process B's 09:05 start assumes Process A finishes by then, and nothing enforces that. The shared workbook is a single mutable resource with three writers (A, B, and the analyst) and no locking, so B can read a partially written file. The absence of a row-count check means B cannot detect that it is reading an incomplete file.
The robust design is to replace implicit timing with an explicit handoff. Process A should write to a uniquely named file (for example with a timestamp or run ID), close it, and only then publish it by moving or renaming it to a pickup location. Renames within a file system are atomic, so B either sees the complete file or no file. Process B should poll the pickup location, process the file, and archive it with a processed marker.
Alternatively, use a work queue as the handoff: Process A adds one item per shipment batch only after the export is fully written, and Process B works those items. That gives retries, audit, and a natural dependency. Either way, the analyst's manual access must be separated from the automation's file, and B should validate expected row counts and raise an exception if they are outside tolerance.
2. The team wants to keep the shared workbook and just add a 10-minute delay to Process B. Assess this option and state the conditions under which it would be acceptable.
Read the worked answer
A fixed delay reduces the probability of collision but does not remove it. If Process A runs long - a slow network drive, a larger export, a retry - B still reads a partial file. The delay also does nothing about the analyst opening the file, and it adds latency to every run even when A finishes early.
It would be acceptable only under narrow conditions: the delay is combined with a completeness check (expected row count, a final 'END' row, or a checksum written last by A), B raises an exception and retries rather than proceeding when the check fails, and there is alerting. Even then it is a mitigation, not a fix, and should be documented as such.
For an exam answer, the judgement is that time-based coordination between independent processes is fragile by design. Prefer a signal: an atomic file publish, a queue item, or a status flag written by A and checked by B. The delay is a stopgap at best.