Action Scheduler Queue Deadlocks & Table Bloat: Offloading WooCommerce Background Jobs to Isolated WP-CLI Workers

If your store processes subscription renewals, webhooks, and ERP syncs through Action Scheduler, the queue is part of your revenue path. This post covers the woocommerce action scheduler table bloat deadlock cli problem end to end: why it happens, how to measure it, and how to move queue execution off the web tier into an isolated WP-CLI worker with bounded table growth.

1. Introduction and Commercial Impact

Action Scheduler stores every unit of background work in four tables: wp_actionscheduler_actions, wp_actionscheduler_claims, wp_actionscheduler_groups, and wp_actionscheduler_logs. On a store with steady order volume, two failure modes compound each other.

Unbounded growth. Completed, canceled, and failed actions plus their log rows accumulate faster than the default cleaner removes them. Secondary indexes on wp_actionscheduler_actions and wp_actionscheduler_logs often outgrow the row data itself. Much like unchecked wp_options transient bloat and MySQL table locks, that overhead evicts hot pages from the InnoDB buffer pool, so checkout and admin queries compete with queue maintenance for memory and I/O.

Concurrent runners. With the default configuration, queue runners start from WP-Cron and from an HTTP loopback request to admin-ajax.php?action=as_async_request_queue_runner. Several PHP-FPM workers can claim work at once, and the claim query collides with itself. The result is SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction.

The business impact is concrete: stalled subscription renewals, delayed webhook dispatches, late ERP synchronizations, and checkout workers starved by loopback requests. The fix is architectural: one dedicated consumer, strict retention, and batched maintenance.

2. Architectural Reality: Why the Common Advice Fails

The most repeated advice for a stuck queue is to raise the action_scheduler_queue_runner_batch_size and action_scheduler_queue_runner_concurrent_batches filters, then run TRUNCATE TABLE wp_actionscheduler_actions or wp_actionscheduler_logs to reclaim space. Both steps cause outages on production stores.

How claiming works

When a runner starts, ActionScheduler_DBStore::claim_actions() does two things:

1. It inserts a new row into wp_actionscheduler_claims.
2. It stamps that claim onto a batch of pending actions:

UPDATE wp_actionscheduler_actions
SET claim_id = %d, last_attempt_gmt = %s, last_attempt_local = %s
WHERE claim_id = 0
  AND status = 'pending'
  AND scheduled_date_gmt <= %s
ORDER BY priority ASC, scheduled_date_gmt ASC, action_id ASC
LIMIT %d;

This is known as ActionScheduler_DBStore claim contention, where multiple queue workers attempt to reserve pending actions from overlapping database index ranges. When simultaneous claim operations encounter conflicting InnoDB locks, the resulting transaction waits can reduce background job throughput and, in some cases, trigger MySQL deadlock errors.

The WHERE clause and the ORDER BY force InnoDB to walk secondary index ranges on claim_id, status, and scheduled_date_gmt, locking every row it examines. Two workers executing this statement at the same moment scan overlapping ranges. Gap locks and next-key locks on those ranges lead to circular waits, and InnoDB resolves them by killing one transaction with error 1213. This behavior is associated with InnoDB next-key locking, where a database lock protects both an index record and the gap preceding it. Under the default REPEATABLE READ isolation level, overlapping range-based UPDATE operations can contribute to lock contention and transaction deadlocks, depending on the query execution plan and indexes involved.

Why raising concurrency makes it worse

Runners are spawned from two directions: WP-Cron (wp-cron.php) and an async loopback request dispatched from the shutdown hook, targeting admin-ajax.php?action=as_async_request_queue_runner. The WP-Cron HTTP loopback mechanism typically invokes wp-cron.php, while Action Scheduler's separate asynchronous queue runner uses admin-ajax.php?action=as_async_request_queue_runner. Distinguishing these request paths helps developers identify which background execution mechanism is consuming PHP workers and contributing to database contention. Each loopback occupies a PHP-FPM worker that checkout traffic needs. Increasing concurrent_batches multiplies the number of simultaneous claim statements against the same index ranges. You get more deadlocks, not more throughput, and fewer workers left for the frontend. This creates PHP-FPM worker starvation, where background Action Scheduler requests occupy PHP processes that would otherwise serve WooCommerce checkout traffic—similar to the worker exhaustion addressed when optimizing Redis session locks for high-traffic checkout concurrency. When the number of active workers reaches the pm.max_children limit, incoming checkout requests must wait for an available worker, increasing response times and potentially causing gateway timeout errors.

Why truncating actions is destructive

TRUNCATE TABLE wp_actionscheduler_actions removes pending work indiscriminately. That includes database migration callbacks queued through WC_Install::run_update_callback() and High-Performance Order Storage (HPOS) migration and synchronization batches. Dropping them leaves schema upgrades half-applied and the legacy and HPOS order tables out of sync, a failure that surfaces days later as missing orders or wrong totals. Safe cleanup deletes only terminal statuses, in small batches (Section 4).

Index-to-data overhead

Production benchmarks consistent with WooCommerce GitHub issues #41565, #45354, and #68552 highlight the index-to-data imbalance:

• wp_actionscheduler_actions: about 0.52 MB of index for 0.39 MB of data.
• wp_actionscheduler_logs: more than 8.02 MB of data and 5.03 MB of index even on moderate installs.

When the index is larger than the data, every insert and status transition pays for maintaining several B-trees, and the claim query has more entries to lock.

3. Diagnostic Workflow

Run these before changing anything. They tell you whether you have a flood, orphaned locks, or plain bloat.

Query 1: Action distribution by group and status

SELECT
    g.slug AS group_slug,
    a.status,
    COUNT(a.action_id) AS action_count,
    MIN(a.scheduled_date_gmt) AS oldest_scheduled_gmt,
    SUM(CASE WHEN a.claim_id <> 0 THEN 1 ELSE 0 END) AS claimed_count
FROM wp_actionscheduler_actions a
LEFT JOIN wp_actionscheduler_groups g ON a.group_id = g.group_id
GROUP BY g.slug, a.status
ORDER BY action_count DESC;

Look for one group dominating the counts, and for pending rows with an old oldest_scheduled_gmt. That indicates the queue is not draining.

Query 2: Orphaned claims older than 10 minutes

SELECT
    c.claim_id,
    c.date_created_gmt,
    a.hook,
    COUNT(a.action_id) AS locked_actions
FROM wp_actionscheduler_claims c
INNER JOIN wp_actionscheduler_actions a ON c.claim_id = a.claim_id
WHERE c.date_created_gmt < (UTC_TIMESTAMP() - INTERVAL 10 MINUTE)
  AND a.status = 'pending'
GROUP BY c.claim_id, c.date_created_gmt, a.hook
ORDER BY c.date_created_gmt ASC;

Rows here mean a PHP-FPM worker died (OOM kill, timeout, deploy restart) while holding a claim. Those actions stay invisible to other runners until the claim times out.

Query 3: Data vs. index size and fragmentation

SELECT
    TABLE_NAME,
    ROUND(DATA_LENGTH  / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
    ROUND(DATA_FREE    / 1024 / 1024, 2) AS fragmented_mb,
    TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME LIKE '%actionscheduler%';

Large index sizes can indicate substantial B-tree index overhead, but they do not automatically prove B-tree index fragmentation. The DATA_FREE metric reports allocated but unused tablespace rather than a direct measurement of index fragmentation. Administrators should evaluate index growth, unused tablespace, and query performance together before deciding whether an index rebuild or OPTIMIZE TABLE operation is necessary.

Confirming deadlocks

wp action-scheduler status --allow-root
wp db query "SHOW ENGINE INNODB STATUS\G" --allow-root | grep -A 35 "LATEST DETECTED DEADLOCK"

If the deadlock block shows UPDATE wp_actionscheduler_actions SET claim_id ... on both sides, you are looking at the claim contention described above. Note that wp action-scheduler status requires the Action Scheduler WP-CLI commands, which ship with WooCommerce and the standalone library.

4. Production-Ready Remediation

The design has three parts:

1. Stop web requests from starting queue runners.
2. Run exactly one dedicated WP-CLI consumer under systemd.
3. Bound table growth with shorter retention and a batched purge.

4.1 The hardening plugin

Save as wp-content/mu-plugins/action-scheduler-optimizer.php. A must-use plugin loads before regular plugins and cannot be deactivated by accident.

<?php
/**
 * Plugin Name: Enterprise Action Scheduler Hardening & Web-Tier Offload
 * Description: Disables web-tier queue runners, enforces short retention, and shortens claim timeouts.
 * Version: 1.0.0
 */

declare(strict_types=1);

namespace EnterpriseWC\QueueHardening;

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

final class ActionScheduler_Optimizer {

    public static function init(): void {
        // Fires after Action Scheduler has registered its runner hooks.
        add_action( 'action_scheduler_init', [ self::class, 'disable_web_queue_runner' ], 20 );

        // Belt and braces: also refuse the async loopback request.
        add_filter( 'action_scheduler_allow_async_request_runner', '__return_false' );

        add_filter( 'action_scheduler_retention_period', [ self::class, 'set_retention_period' ] );
        add_filter( 'action_scheduler_default_cleaner_statuses', [ self::class, 'expand_cleaner_statuses' ] );
        add_filter( 'action_scheduler_cleanup_batch_size', [ self::class, 'set_cleanup_batch_size' ] );
        add_filter( 'action_scheduler_timeout_period', [ self::class, 'set_claim_timeout' ] );
    }

    /**
     * Remove WP-Cron and shutdown-hook runners on web requests.
     * Only WP-CLI processes may execute the queue.
     */
    public static function disable_web_queue_runner(): void {
        if ( defined( 'WP_CLI' ) && WP_CLI ) {
            return;
        }

        if ( ! class_exists( '\\ActionScheduler' ) ) {
            return;
        }

        $runner = \ActionScheduler::runner();

        // WP-Cron entry point.
        remove_action( 'action_scheduler_run_queue', [ $runner, 'run' ] );

        // Shutdown-hook dispatchers (async loopback to admin-ajax.php).
        global $wp_filter;
        if ( isset( $wp_filter['shutdown'] ) && is_object( $wp_filter['shutdown'] ) ) {
            foreach ( $wp_filter['shutdown']->callbacks as $priority => $callbacks ) {
                foreach ( $callbacks as $callback ) {
                    $fn = $callback['function'];
                    if ( is_array( $fn ) && isset( $fn[0] ) && is_object( $fn[0] ) ) {
                        $class = get_class( $fn[0] );
                        if (
                            false !== strpos( $class, 'ActionScheduler_AsyncRequest_QueueRunner' )
                            || false !== strpos( $class, 'ActionScheduler_QueueRunner' )
                        ) {
                            remove_action( 'shutdown', $fn, (int) $priority );
                        }
                    }
                }
            }
        }
    }

    /** Reduce retention for finished actions from 30 days to 3 days. */
    public static function set_retention_period(): int {
        return 3 * DAY_IN_SECONDS;
    }

    /**
     * Include failed actions in automatic cleanup.
     *
     * @param array<int,string> $statuses
     * @return array<int,string>
     */
    public static function expand_cleaner_statuses( array $statuses ): array {
        if ( ! in_array( 'failed', $statuses, true ) ) {
            $statuses[] = 'failed';
        }
        return $statuses;
    }

    /** Larger cleaner batches stop bloat from accumulating faster than it is removed. */
    public static function set_cleanup_batch_size(): int {
        return 500;
    }

    /** Release stalled claims after 180 seconds instead of 300. */
    public static function set_claim_timeout(): int {
        return 180;
    }
}

ActionScheduler_Optimizer::init();

Two operational notes. First, hook names and internal class layouts vary slightly between Action Scheduler versions, so after deploying, confirm with wp eval 'global $wp_filter; var_dump(isset($wp_filter["action_scheduler_run_queue"]));' on a web context and by watching your access log for as_async_request_queue_runner requests; they should stop. Second, consider setting define( 'DISABLE_WP_CRON', true ); and triggering wp cron event run --due-now from system cron so the rest of WP-Cron keeps working without HTTP triggers.

4.2 The isolated systemd worker

Create /etc/systemd/system/wc-action-scheduler.service:

[Unit]
Description=WooCommerce Action Scheduler WP-CLI Dedicated Worker
After=network.target mysql.service

[Service]
Type=simple
User=www-data
Group=www-data
WorkingDirectory=/var/www/html
ExecStart=/usr/bin/bash -c 'while true; do /usr/local/bin/wp action-scheduler run --batch-size=100 --hooks="" --exclude-groups="" --force --path=/var/www/html; sleep 2; done'
Restart=always
RestartSec=5
MemoryMax=512M
IOSchedulingClass=best-effort
IOSchedulingPriority=4
Nice=5

[Install]
WantedBy=multi-user.target

Enable it:

sudo systemctl daemon-reload
sudo systemctl enable --now wc-action-scheduler.service
journalctl -u wc-action-scheduler.service -f

Why this works: a single process claims one batch of 100, executes it, and sleeps two seconds. There is no second claimant, so the UPDATE ... claim_id = 0 statement stops colliding with itself. MemoryMax contains runaway callbacks, and the I/O class keeps queue work from competing with checkout reads. If one worker cannot keep up, scale by splitting on group (for example --group=woocommerce-webhooks in one unit and --exclude-groups=woocommerce-webhooks in another) rather than by running identical consumers. The empty --hooks="" and --exclude-groups="" values are no-ops here and act as explicit placeholders for those splits.

4.3 Batched purge procedure

Retention filters slow future growth, but an existing backlog needs a one-time purge. A single DELETE over millions of rows holds locks for the whole statement, so work in 5,000-row batches with a short sleep. MySQL does not allow LIMIT in multi-table DELETE or in IN (subquery), so the batches below select IDs through a derived table.

DROP PROCEDURE IF EXISTS prune_actionscheduler_bloat;

DELIMITER $$

CREATE PROCEDURE prune_actionscheduler_bloat()
BEGIN
    DECLARE rows_affected INT DEFAULT 1;

    -- 1. Delete terminal actions older than 3 days.
    WHILE rows_affected > 0 DO
        DELETE FROM wp_actionscheduler_actions
        WHERE action_id IN (
            SELECT action_id FROM (
                SELECT action_id
                FROM wp_actionscheduler_actions
                WHERE status IN ('complete', 'canceled', 'failed')
                  AND scheduled_date_gmt < (UTC_TIMESTAMP() - INTERVAL 3 DAY)
                LIMIT 5000
            ) AS batch
        );
        SET rows_affected = ROW_COUNT();
        DO SLEEP(0.05);
    END WHILE;

    -- 2. Delete orphaned log rows (their action no longer exists).
    SET rows_affected = 1;
    WHILE rows_affected > 0 DO
        DELETE FROM wp_actionscheduler_logs
        WHERE log_id IN (
            SELECT log_id FROM (
                SELECT l.log_id
                FROM wp_actionscheduler_logs l
                LEFT JOIN wp_actionscheduler_actions a ON l.action_id = a.action_id
                WHERE a.action_id IS NULL
                LIMIT 5000
            ) AS batch
        );
        SET rows_affected = ROW_COUNT();
        DO SLEEP(0.05);
    END WHILE;

    -- 3. Delete orphaned claims: older than 10 minutes with no actions attached.
    SET rows_affected = 1;
    WHILE rows_affected > 0 DO
        DELETE FROM wp_actionscheduler_claims
        WHERE claim_id IN (
            SELECT claim_id FROM (
                SELECT c.claim_id
                FROM wp_actionscheduler_claims c
                LEFT JOIN wp_actionscheduler_actions a ON a.claim_id = c.claim_id
                WHERE a.claim_id IS NULL
                  AND c.date_created_gmt < (UTC_TIMESTAMP() - INTERVAL 10 MINUTE)
                LIMIT 5000
            ) AS batch
        );
        SET rows_affected = ROW_COUNT();
        DO SLEEP(0.05);
    END WHILE;
END$$

DELIMITER ;

CALL prune_actionscheduler_bloat();

The procedure removes actions first so that their logs become orphans, then clears those logs in a second pass. Pending actions are never touched, which keeps migration callbacks and HPOS sync batches intact. Run it during a low-traffic window and keep an eye on replication lag if you have replicas, since each batch replicates as a separate transaction.

After a large purge, reclaim space and rebuild indexes:

OPTIMIZE TABLE wp_actionscheduler_actions, wp_actionscheduler_logs, wp_actionscheduler_claims;

On large tables, prefer pt-online-schema-change or your platform's online DDL equivalent to avoid a long metadata lock.

5. Rollout Checklist

1. Take a database backup and run the three diagnostic queries to record a baseline.
2. Deploy the must-use plugin to staging and confirm loopback requests to as_async_request_queue_runner stop.
3. Start the systemd worker and confirm wp action-scheduler status shows the queue draining.
4. Run the purge procedure off-peak, then OPTIMIZE TABLE.
5. Re-run the diagnostics and compare index_mb, fragmented_mb, and the deadlock section of SHOW ENGINE INNODB STATUS after 24 hours.
6. Alert on pending actions older than your SLA (for example, 15 minutes) so a dead worker is noticed before renewals stall.

Summary

The woocommerce action scheduler table bloat deadlock cli problem is not solved with larger batches or a TRUNCATE. Deadlocks come from overlapping claim statements issued by multiple web-tier runners, and bloat comes from retention and cleanup that cannot keep up with write volume. A single systemd-managed WP-CLI consumer, a web tier that never runs the queue, a 3-day retention window, and a batched purge give you predictable queue latency and stable InnoDB memory use.