[Resolved] Database deadlock bringing site down

I raised a support ticket recently which has been closed so I can no longer add to it (https://wpml.org/forums/topic/experiencing-database-deadlocks-intermittently/) I am experiencing the same problem again. When I enabled WPML it eventually will cease up and flood my error log with Deadlocks:

WordPress database error Deadlock found when trying to get lock; try restarting transaction for query INSERT IGNORE INTO wp_options ( `option_name`, `option_value`, `autoload` ) VALUES ('wpml.WPML\\\\TM\\\\Settings\\\\ProcessNewTranslatableFields.lock', '1706483353', 'no') /* LOCK */ made by require_once('wp-load.php'), require_once('/home/orthoracle/orthoracle.com/releases/20240125195731/wp-config.php'), require_once('wp-settings.php'), do_action('wp_loaded'), WP_Hook->do_action, WP_Hook->apply_filters, WPML\\LIB\\WP\\Hooks::WPML\\LIB\\WP\\{closure}, WPML\\FP\\Promise->resolve, WPML\\FP\\Right->chain, WPML\\FP\\Right->map, WPML\\BackgroundTask\\BackgroundTaskLoader->WPML\\BackgroundTask\\{closure}, WPML\\BackgroundTask\\BackgroundTaskLoader->getSerializedTasks, WPML\\FP\\Fns::__callStatic, call_user_func_array, WPML\\FP\\Fns::WPML\\FP\\{closure}, call_user_func_array, WPML\\FP\\Fns::WPML\\FP\\{closure}, array_map, WPML\\BackgroundTask\\BackgroundTaskLoader->WPML\\BackgroundTask\\{closure}, WPML\\BackgroundTask\\BackgroundTaskViewModel::get, WPML\\Utilities\\Lock->create, WPML\\Utilities\\Lock->create, WPML\\Utilities\\Lock->create, WPML\\Utilities\\Lock->create, WPML\\Utilities\\Lock->create, referer: hidden link

It seems I am not alone in experiencing this problem as I found the following support tickets from other users experiencing deadlocks:

- https://wpml.org/forums/topic/deadlock-errors-in-website-logs/
- https://wpml.org/forums/topic/got-error-php-message-wordpress-database-error-deadlock/
- https://wpml.org/forums/topic/wordpress-database-error-deadlock-3/
- https://wpml.org/forums/topic/wordpress-database-error-deadlock-found-when-trying-to-get-lock-try-restarting/

Most of the advice given on these tickets says to clear the WPML cache, which I have done and it seems to solve the problem briefly. I even set up a cronjob to clear the WPML cache once a week but this didn't help. Perhaps I should clear the cache once a day?

Since the solution provided is always to clear the cache, perhaps you could explain why clearing the cache can sometimes help? What is this wp_options query doing?

Another solution offered was to make sure the Advance Translator is enabled. Currently we have the Classic one enabled. Can you tell me why this should make a difference?

Thank you in advance.