Title: Deadlock db errors Last modified: June 8, 2017 --- # Deadlock db errors * [frnkoc](https://wordpress.org/support/users/frnkoc/) * (@frnkoc) * [8 years, 11 months ago](https://wordpress.org/support/topic/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 wp_options WHERE option_name = '_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 UPDATE`wp_woocommerce_downloadable_product_permissions` SET`user_id`= XXXX,`user_email`= 'xx@gmail.com' WHERE`order_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` = ‘xx@gmail.com’ 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 8 years, 11 months ago by [frnkoc](https://wordpress.org/support/users/frnkoc/). - This topic was modified 8 years, 11 months ago by [frnkoc](https://wordpress.org/support/users/frnkoc/). - This topic was modified 8 years, 11 months ago by [Steven Stern (sterndata)](https://wordpress.org/support/users/sterndata/). Viewing 2 replies - 1 through 2 (of 2 total) * [PerfectSolution](https://wordpress.org/support/users/perfectsolution/) * (@perfectsolution) * [8 years, 10 months ago](https://wordpress.org/support/topic/deadlock-db-errors/#post-9328803) * Did you find a proper solution to this issue? We are experiencing the very same issue with subscriptions missing from the wp_posts table after checkout. The meta data is present in the wp_postmeta though. * Thread Starter [frnkoc](https://wordpress.org/support/users/frnkoc/) * (@frnkoc) * [8 years, 10 months ago](https://wordpress.org/support/topic/deadlock-db-errors/#post-9329310) * Hi. Yes. This deadlocks were caused by Woocommerce Chain Products postmeta relevant to the products linked. It looks that when a product is chain linked to a parent product and later removed, the metadata is not properly deleted causing a fatal error due to the null value being called every time this product is purchased. * After we removed the plugin and removed the extra postmeta directly from the db for these products the deadlock errors stopped and not a single order has failed to link products or subscriptions in the orders. * Everytime this happen check the php log for errors at the time the order was placed. That’s your best bet to find what is causing the error. Viewing 2 replies - 1 through 2 (of 2 total) The topic ‘Deadlock db errors’ is closed to new replies. * ![](https://ps.w.org/woocommerce/assets/icon.svg?rev=3234504) * [WooCommerce](https://wordpress.org/plugins/woocommerce/) * [Frequently Asked Questions](https://wordpress.org/plugins/woocommerce/#faq) * [Support Threads](https://wordpress.org/support/plugin/woocommerce/) * [Active Topics](https://wordpress.org/support/plugin/woocommerce/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/woocommerce/unresolved/) * [Reviews](https://wordpress.org/support/plugin/woocommerce/reviews/) * 2 replies * 2 participants * Last reply from: [frnkoc](https://wordpress.org/support/users/frnkoc/) * Last activity: [8 years, 10 months ago](https://wordpress.org/support/topic/deadlock-db-errors/#post-9329310) * Status: not resolved