Async parallel branches degrade to serial silently on non-MySQL Action Scheduler stores (SQLite)

[chubes4]

Async parallel branches degrade to serial silently on non-MySQL Action Scheduler stores

Summary

The Action Scheduler branch executor (#392) enqueues N branch actions correctly, but whether they actually run concurrently depends entirely on the Action Scheduler data store. On a store that does not honor stake_claim's LIMIT or lacks SKIP LOCKED (notably the WordPress SQLite integration), the branches degrade to serial execution with zero error or warning — the run still SUCCEEDS and produces correct output, it's just not parallel. This is a silent footgun for anyone deploying the async parallel primitive on a non-MySQL store.

Evidence (measured on a native-PHP + SQLite Studio runtime)

Deterministic A/B, 4 branches × sleep(3) + aggregator, cap raised to 6, 4 concurrent OS processes launched within 0.8ms of each other, --batch-size=1 --batches=1 --force:

run	total wall-clock	branch PIDs	branch starts
sync (selector→null)	12.71s	all 56442	staggered 3s apart
async-parallel (4 procs, cap=6)	12.67s	all 75810 (one PID)	staggered 3s apart

Speedup: 1.00×. Despite 4 processes racing to claim, exactly one runner claimed the whole due queue and ran all 4 branches serially; the other three reported "0 batches executed."

Root cause (two mechanisms, both tested directly)

The runtime's AS store is the WordPress SQLite integration (sqlite-database-integration 3.0.0-rc.4; db_server_info() → 3.45.2, though SELECT VERSION() spoofs 8.0.38).

1. stake_claim($limit) ignores its LIMIT. Direct probe: stake_claim(1) returned 29 actions; stake_claim(2) also returned 29. AS's claim uses an UPDATE ... JOIN (SELECT ... LIMIT %d FOR UPDATE) pattern that the SQLite translation layer does not honor, so the first claimant swallows the entire due queue — leaving concurrent workers nothing to run. This alone forces serial regardless of process count.
2. SKIP LOCKED is disabled. ActionScheduler_DBStore::db_supports_skip_locked() gates on MySQL ≥ 8.0.1 / MariaDB ≥ 10.6 via $wpdb->db_version(), which returns a truncated 8.0 here → version_compare('8.0','8.0.1','>=') is false. Without SKIP LOCKED, concurrent claims would contend on row locks anyway — and SQLite serializes writes on a single DB-level write lock regardless.

The agents-api branch executor itself is correct — it enqueues 4 independent async actions and relies on "AS's cross-process atomic claim" (per class-wp-agent-workflow-action-scheduler-branch-executor.php header) for concurrency. That guarantee holds on MySQL 8.0.1+/MariaDB 10.6+ and collapses to serial on stores that don't honor it (SQLite, older MySQL).

Why this matters

agents-api is headed for wpcom/WP core, where the store may vary. A consumer that adopts async parallel steps expecting concurrency will get correct-but-serial behavior on the wrong store with no signal that anything is degraded. The failure is invisible: no error, correct output, just no speedup.

Proposed fix (design-level, not prescribing implementation)

1. Detect + warn. At AS-executor selection time, probe the store's concurrency capability (e.g. db_supports_skip_locked() and/or a stake_claim LIMIT sanity check) and surface a clear diagnostic when the store cannot parallelize — a _doing_it_wrong, an admin notice, a run-metadata flag, or a filterable warning. Silent degradation is the actual bug.
2. Document the store requirement loudly in the branch-executor docblock and any consumer-facing docs: async parallel branches run concurrently only on MySQL 8.0.1+ / MariaDB 10.6+; on other stores (including the SQLite integration) they run correctly but serially.
3. (Optional) Expose the detected capability so a consumer can choose to warn its user ("install a MySQL-backed store for concurrent generation") or fall back deliberately.

Not in scope

This is not asking agents-api to make SQLite parallel (it can't — SQLite serializes writes). It's asking the substrate to not degrade silently: detect, warn, and document, so the concurrency guarantee is honest about its store dependency.

Repro

Native-PHP Studio site on the SQLite integration; AS 4.0.0; run any async parallel-roles workflow with the AS branch executor + raised concurrency cap + concurrent action-scheduler run processes; observe all branches execute in one PID (see table above). The same specs parallelize on a MySQL 8.0.1+ store.

AI assistance

• AI assistance: Yes
• Tool(s): Claude Code (Claude Opus 4.8)
• Used for: Diagnosing the store-dependent concurrency degradation with PID/timestamp evidence and drafting this report.

[chubes4]

Correction to root cause (verified directly)

I re-tested the two claimed mechanisms against the live SQLite runtime and the original diagnosis was partly wrong. Corrected findings:

1. stake_claim does NOT ignore its LIMIT. Tested the exact AS claim shape — UPDATE t1 JOIN (SELECT action_id ... WHERE claim_id=0 LIMIT 2) t2 SET claim_id=777 with 6 claimable rows → exactly 2 claimed. The SQLite integration honors LIMIT inside the UPDATE-JOIN subquery correctly. (The earlier stake_claim(1)→29 observation was a misread: stake_claim's default $max_actions is 10, and the probe wasn't passing 1 the way it appeared.)

2. FOR UPDATE / SKIP LOCKED parse without error under the integration — SELECT ... FOR UPDATE returns rows cleanly; the lock hints are simply no-ops on SQLite.

The real root cause: SQLite's single-writer model. SQLite serializes ALL writes on one database-level write lock — two PHP processes cannot hold concurrent write transactions. So even though each worker's claim query is correct and LIMIT-bounded, the workers cannot execute their claims concurrently; they serialize at the write lock. This is not a bug in sqlite-database-integration or Action Scheduler — it is SQLite's fundamental concurrency model. No plugin fix changes it.

The issue's substance still stands, with the corrected cause: agents-api async parallel branches run concurrently only on a store that supports genuine concurrent writes (MySQL/MariaDB). On SQLite they degrade to serial silently (correct output, no speedup, no warning). The recommendation is unchanged — detect the store's concurrency capability at AS-executor selection and warn/document rather than degrade silently — but the detection signal should be 'does the store support concurrent writes' (MySQL-family), not the stake_claim-LIMIT / SKIP_LOCKED specifics originally cited.

Evidence gathered on the native-PHP + SQLite Studio runtime via direct $wpdb probes.