Two Processes, One Shared Spreadsheet
A logistics company has two Blue Prism processes: one that updates a shared Excel tracking workbook with shipment status, and one that reads the same workbook to generate customer notifications. Both run on a schedule every 30 minutes, sometimes overlapping. The workbook sits on a file share. Recently the notification process has been reading half-written rows, and on two occasions the workbook was left locked, blocking both processes. Each process was built independently by a different developer, each opens and saves the file itself, and neither uses a work queue. The team wants to merge everything into one large process to remove the conflict, but the notification logic changes monthly while the status update is stable.
1. Evaluate the proposal to merge into a single process and recommend an architecture that resolves the conflict without that merge.
Read the worked answer
Merging solves the lock conflict by serialising access, but it couples a stable process to a volatile one: every monthly notification change would require regression testing the shipment update, and a defect in either half stops both. It also creates a single large process that is harder to assign, schedule, and govern. The conflict is really a shared-resource contention problem, and it should be solved at the resource, not by collapsing the processes.
A better architecture separates the data from the file. Introduce a work queue as the contract between the two processes: the status process writes shipment status events to a queue, and the notification process consumes from that queue. The shared workbook then becomes an output that only one process writes, or is replaced by a database or the queue itself as the system of record. If the workbook must remain, designate a single owner process for writes and have the other read a copy or an export produced on a schedule, so there is never concurrent open-and-save.
Keep the two processes separate, with clear interfaces and owners. The volatile notification logic stays isolated, and the stable update path is untouched by its monthly changes. Document the interface and the ownership in the solution design so future developers do not reintroduce direct file access.
2. If the workbook must stay as the system of record for now, what controls would you put in place and how would you test them?
Read the worked answer
Controls should make concurrent access impossible by design rather than by timing. First, assign a single writer: only the status process may open the workbook for write, and the notification process reads a read-only snapshot exported by the writer at the end of its run. Second, use a locking convention: the writer creates a lock file or acquires a named mutex before opening, and any other process that finds the lock waits or exits cleanly rather than opening the file. Third, make writes atomic where possible: write to a temporary copy and replace the original in one operation, so a reader never sees a half-written file. Fourth, stagger the schedules so the writer completes before the reader starts, and add a check at the start of each run that the other is not active.
Testing should include the failure modes, not just the happy path. Run both processes simultaneously in a test environment and confirm the reader either waits or reads the last complete snapshot, never a partial row. Kill the writer mid-write and confirm the lock is released or expires and the file is not corrupted. Simulate a locked file and confirm the process raises a clear System Exception rather than hanging. Check that a crash leaves no orphaned lock that blocks the next scheduled run. Finally, monitor for lock events in production and alert if they occur, because their presence means the controls are being bypassed.