Deadlocks when processing and claiming actions

[thenbrent]

While running 3 queues of scheduled actions (with a small batch size of 5 actions) via WP CLI, I enountered the following deadlock:

WordPress database error Deadlock found when trying to get lock; try restarting transaction for query
UPDATE wp_actionscheduler_actions SET attempts = attempts+1, status='in-progress', last_attempt_gmt = '2018-05-10 10:31:56', last_attempt_local = '2018-05-10 06:31:56' WHERE action_id = 803045 made by
include('phar:///usr/local/bin/wp/php/boot-phar.php'),
include('phar:///usr/local/bin/wp/php/wp-cli.php'),
WP_CLI\bootstrap,
WP_CLI\Bootstrap\LaunchRunner->process,
WP_CLI\Runner->start,
WP_CLI\Runner->_run_command_and_exit,
WP_CLI\Runner->run_command,
WP_CLI\Dispatcher\Subcommand->invoke,
call_user_func,
WP_CLI\Dispatcher\CommandFactory::WP_CLI\Dispatcher\{closure},
call_user_func,
ActionScheduler_WPCLI_Scheduler_command->run,
ActionScheduler_WPCLI_QueueRunner->run,
ActionScheduler_Abstract_QueueRunner->process_action,
Action_Scheduler\Custom_Tables\DB_Store->log_execution

Screenshot: https://cl.ly/262n1x3m1E3Z

[thenbrent]

I'm also seeing a few of the following deadlocks when claiming actions (on a site with 8 concurrent queues processing):

WordPress database error Deadlock found when trying to get lock; try restarting transaction for query
UPDATE `wp_qftw_actionscheduler_actions` SET `claim_id` = 0 WHERE `claim_id` = 29497
made by
    include('phar:///usr/local/bin/wp/php/boot-phar.php'),
    include('phar:///usr/local/bin/wp/php/wp-cli.php'),
WP_CLI\bootstrap,
WP_CLI\Bootstrap\LaunchRunner->process,
WP_CLI\Runner->start,
WP_CLI\Runner->_run_command_and_exit,
WP_CLI\Runner->run_command,
WP_CLI\Dispatcher\Subcommand->invoke,
call_user_func,
WP_CLI\Dispatcher\CommandFactory::WP_CLI\Dispatcher\{closure},
call_user_func,
ActionScheduler_WPCLI_Scheduler_command->run,
ActionScheduler_WPCLI_QueueRunner->run,
Action_Scheduler\Custom_Tables\DB_Store->release_claim

[rrennick]

@thenbrent Do you think a try/catch, usleep & retry would be a good way to mitigate this?

[thenbrent]

@rrennick I think that's probably the best we can do. Sleep + retry won't reduce the likelihood of it happening much, because it's equally likely there will be multiple queues happening later, so the deadlock may occur again on a 2nd attempt. We could mitigate that by retrying a number of times, like up to 10, but that all starts to feel very hacky...

I'm not sure how (if) we can optimize the query or schema to prevent it completely though either, so try/catch, usleep & retry might be the best we can do.