Scheduled Bot sometimes processes/creates two rows instead of one – unable to prevent concurrent execution
Hello AppSheet Community,
I have been struggling with an issue involving a Scheduled Bot, and I have already tried several different approaches to prevent duplicate/concurrent processing, but I still cannot get it to work reliably.
I would appreciate any advice, especially from anyone who has experienced similar behavior with Scheduled Bots.
What I am trying to achieve
I have a queue table called:
請求書一括 (Invoice Batch)
Each row represents one invoice-generation job.
The important columns include:
- JobID
- Created Date/Time
- Status
- File
- Error Memo
- ControlKey
- other invoice-related fields
The intended workflow is very simple:
queued → processing → done
I want AppSheet to process the jobs one at a time.
The reason for processing them sequentially is that each job performs several operations, including updating related rows, calculating invoice data, generating a PDF, and updating the job status.
I do NOT want two jobs to be processed simultaneously.
Original problem
Initially, I tried to create a chain where, after one job was completed, the automation would select the next queued job and change it to processing.
However, this sometimes resulted in two rows becoming processing almost simultaneously.
In other cases, the automation would process every other row and then stop.
If I removed the step that automatically started the next job, the jobs could be processed correctly one at a time, but of course the queue would no longer continue automatically.
So it appeared that self-triggering / chained automation was creating a race condition or overlapping execution.
Control table approach
To avoid the self-triggering problem, I created a separate single-row control table.
The Control table contains columns such as:
- ControlKey
- Lock
- KickAt
- LastJobID
- CurrentJobID
The idea was to use this single Control row as a coordinator/lock.
The expected logic was:
- Scheduled Bot runs.
- Check whether any queued jobs exist.
- Select only the oldest/first queued job.
- Store its JobID in CurrentJobID.
- Change that job from queued to processing.
- Process the invoice.
- Change the job to done.
- Release the lock / update the Control row.
- Process the next job on a later scheduled execution.
The intention was that only the Control row could decide which job was allowed to run, preventing multiple invoice jobs from starting at the same time.
However, I am still seeing cases where two rows are created/processed instead of one.
I also tried limiting the selected rows
I tried using expressions based on TOP() / ORDERBY() so that only one queued row should be selected.
Conceptually, the selection is equivalent to:
TOP(
ORDERBY(
SELECT(
請求書一括[JobID],
[ステータス] = “queued”
),
[作成日時],
TRUE
),
1
)
The expectation is obviously that this expression can return only one JobID.
However, the duplicate/concurrent behavior has not been completely eliminated.
This makes me wonder whether the issue is not the SELECT expression itself, but rather two Scheduled Bot executions evaluating the queue before either execution has finished updating the status.
For example:
Execution A:
sees Job A as queued
Execution B:
also sees Job A as queued
Execution A:
starts processing
Execution B:
also starts processing
Or possibly both executions select different queued jobs before the Control/Lock update is committed.
Other approaches I tried
I also experimented with triggering another Bot by updating/adding a row to a relay/control table.
The idea was:
Scheduled Bot
→ update Control/Relay
→ Data Change Bot
→ process one job
→ update Relay again
→ process next job
However, this introduced another problem.
Bot-to-Bot/self-triggering behavior did not continue reliably, and when I tried using ADD ROW as a pulse/relay mechanism, I eventually encountered recursive execution behavior.
Therefore, I abandoned the Relay approach.
Current approach
I returned to a Scheduled Bot / polling approach because it seemed safer than recursive Bots.
The Scheduled Bot checks whether queued jobs exist and should process only a limited number of rows.
The basic condition is similar to:
COUNT(
SELECT(
請求書一括[JobID],
AND(
[ステータス] = “queued”,
)
)
) > 0
I have also tried using “Run a data action on a set of rows” and selecting only the first row using TOP(ORDERBY(…), 1).
But I am still unable to guarantee that only one row is created/processed.
Important observation
This does not look like a normal duplicate caused by the user editing the same row twice.
The problem occurs in Scheduled Automation.
The automation is specifically designed to select only one queued record.
Also, changing the selected row immediately from:
queued
to:
processing
was intended to prevent another execution from selecting the same row.
Nevertheless, I still occasionally get two rows / two processing operations.
My questions
- Can two executions of the same Scheduled Bot overlap in AppSheet?
- If execution A has selected a row but has not yet committed the status change from queued to processing, can execution B select the same row?
- Is there any true atomic locking mechanism in AppSheet Automation?
- Is a Boolean Lock column in a Control table actually safe for this purpose, or can two Bot executions both read Lock = FALSE before either writes TRUE?
- Is TOP(ORDERBY(…), 1) guaranteed to return/process exactly one row in a Scheduled Bot?
- Is there a recommended architecture for implementing a queue where exactly one job must be processed at a time?
- Could this be related to the Scheduled Bot / TOP() duplicate execution issue that other users have reported?
My main requirement is not simply to delete duplicate rows afterward.
I need to prevent two jobs from starting in the first place, because each job performs additional updates and PDF generation, so deleting a duplicate afterward is too late.
Ideally, I need something equivalent to an atomic operation:
“Claim the oldest queued job only if no other process has already claimed it.”
If AppSheet cannot guarantee this with Scheduled Bots, I would also like to know the recommended AppSheet-native design for this type of queue.
Thank you for any advice or examples.
