Deadlock db errors

Hi.
Right after WC3 upgrade in our site we started getting a random issue with consecutive orders not being completed. Two consecutive orders, the first order is missing the products and the second order gets the first order product added by error. Or the first one is showing the subscription product but the actual subscription is not created but the order details show on the wp_postmeta table but the actual post is not created in wp_post.

After researching we found there were deadlock errors at the time this issue happened. Also the two orders involved were created at the same exact time even matching the seconds.

Most of the time the errors are related to transients but not always.Here are some samples:

[07-Jun-2017 17:00:06 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query DELETE FROM wp_options WHERE option_name LIKE '\\_transient\\_%1496854806' ORDER BY option_id LIMIT 1000; made by require('/wordpress-4.7.5/wp-blog-header.php'), require_once('/wordpress-4.7.5/wp-includes/template-loader.php'), do_action('template_redirect'), WP_Hook->do_action, WP_Hook->apply_filters, WC_AJAX::do_wc_ajax, do_action('wc_ajax_checkout'), WP_Hook->do_action, WP_Hook->apply_filters, WC_AJAX::checkout, WC_Checkout->process_checkout, WC_Checkout->create_order, do_action('woocommerce_checkout_update_order_meta'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Subscriptions_Switcher::add_order_meta, wcs_delete_objects_property, WC_Order->save, WC_Data_Store->update, WC_Order_Data_Store_CPT->update, Abstract_WC_Order_Data_Store_CPT->update, Abstract_WC_Order_Data_Store_CPT->clear_caches, wc_delete_shop_order_transients, WC_Cache_Helper::get_transient_version, WC_Cache_Helper::delete_version_transients

[07-Jun-2017 16:36:42 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query DELETE FROM <code>wp_options</code> WHERE <code>option_name</code> = '_transient_timeout_GFCache_3e139bb32df41dd1ef76b8108223e229' made by require_once('wp-load.php'), require_once('wp-config.php'), require_once('/wordpress-4.7.5/wp-settings.php'), do_action('init'), WP_Hook->do_action, WP_Hook->apply_filters, Vc_Manager->init, do_action('vc_after_init'), WP_Hook->do_action, WP_Hook->apply_filters, vc_vendor_gravityforms_load, GFFormsModel::get_forms, GFFormsModel::get_entry_count_per_form, GFCache::get, GFCache::get_transient, get_transient, delete_option

This one doesn’t have to do with transients but with woocommerce subscription download:

[07-Jun-2017 17:35:31 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query UPDATEwp_woocommerce_downloadable_product_permissionsSETuser_id= XXXX,user_email= '[email protected]' WHEREorder_id= 305XXX made by require('/wordpress-4.7.5/wp-blog-header.php'), require_once('/wordpress-4.7.5/wp-includes/template-loader.php'), do_action('template_redirect'), WP_Hook->do_action, WP_Hook->apply_filters, WC_AJAX::do_wc_ajax, do_action('wc_ajax_checkout'), WP_Hook->do_action, WP_Hook->apply_filters, WC_AJAX::checkout, WC_Checkout->process_checkout, do_action('woocommerce_checkout_order_processed'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Subscriptions_Checkout::process_checkout, WC_Subscriptions_Checkout::create_subscription, wcs_copy_order_meta, wcs_set_objects_property, WC_Order->save, WC_Data_Store->update, WCS_Subscription_Data_Store_CPT->update, WC_Order_Data_Store_CPT->update, Abstract_WC_Order_Data_Store_CPT->update, WCS_Subscription_Data_Store_CPT->update_post_meta, WC_Order_Data_Store_CPT->update_post_meta, WC_Data_Store->__call, WC_Customer_Download_Data_Store->update_user_by_order_id

This is a high volume of sales website were we get on a pick time up to 5 to 6 orders per minute. Normally after 12 at noon and thats when the deadlock errors happen.

This site is hosted with Pagely. This is what they say about this error:

Here’s our analysis of the deadlock issues you’re seeing:
? wp_woocommerce_downloadable_product_permissions
UPDATE wp_woocommerce_downloadable_product_permissions SET user_id = XXXX, user_email = ‘[email protected]’ WHERE order_id = 305XXX;
This can result in a deadlock because the plugin that created this table didn’t create an index on the order_id column. Since there’s now index, the update query has to do a full index scan to satisfy the WHERE clause. Observe the rows field in the output of the following query:
mysql> EXPLAIN SELECT COUNT(*) FROM wp_woocommerce_downloadable_product_permissions WHERE order_id = 305XXX;
+—-+————-+————————————————-+——-+—————+————————+———+——+——-+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————————————————-+——-+—————+————————+———+——+——-+————————–+
| 1 | SIMPLE | wp_woocommerce_downloadable_product_permissions | index | NULL | download_order_product | 146 | NULL | 46548 | Using where; Using index |
+—-+————-+————————————————-+——-+—————+————————+———+——+——-+————————–+
When you do an update, the storage engine locks all the index entries that it touches, so the entire table is essentially locked until the current transaction completes. This is easily remedied by adding an index on the field in question, which I have done:
mysql> ALTER TABLE wp_woocommerce_downloadable_product_permissions ADD INDEX idx_order_id (order_id);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT COUNT(*) FROM wp_woocommerce_downloadable_product_permissions WHERE order_id = 3058XX;
+—-+————-+————————————————-+——+—————+————–+———+——-+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————————————————-+——+—————+————–+———+——-+——+————————–+
| 1 | SIMPLE | wp_woocommerce_downloadable_product_permissions | ref | idx_order_id | idx_order_id | 8 | const | 1 | Using where; Using index |
+—-+————-+————————————————-+——+—————+————–+———+——-+——+————————–+
This index has high cardinality, so this improves the performance of any reads and writes that filter based on the order_id column.
? wp_options
DELETE FROM wp_options WHERE option_name LIKE ‘\\_transient\\_%1496877765’ ORDER BY option_id LIMIT 1000;
This query does a range scan on the option_name index because of its use of the LIKE comparison operator with a prefixed wildcard value. The storage engine will lock all entries on the index that it has scanned and hold on to them until after the transaction completes. If these are running often, deadlocks are almost inevitable. Storing transients in the wp_options table for a busy site is just not something that scales well. The best way forward here would be to diagnose and fix the issue with the part of the plugin code that causes issues when object cache is in use, because using object cache to store transients pretty much eliminates this class of error.
One tactic that we can try out immediately to see if it helps with the transients would be to adjust the transaction isolation level from the default (REPEATABLE-READ) to READ-COMMITTED. This adjusts the locking behavior slightly. In the above example with the options table, the storage engine (InnoDB) will release the locks for any entries that it didn’t modify as soon as the UPDATE statement completes instead of after the entire transaction is committed. This should definitely reduce the tendency for deadlocks, but I should warn you that it is an untested configuration. We can make this change for your RDS if you’d like–it just requires a maintenance period (reboot) to apply the changes.

Can you please give me a hint if this change in the db is recommended or if there is another way to figure exactly whats happening?
Thanks
FC

• This topic was modified 7 years, 9 months ago by frnkoc.
• This topic was modified 7 years, 9 months ago by frnkoc.
• This topic was modified 7 years, 9 months ago by Steven Stern (sterndata).