asked on

PHP Query in WordPress is returning duplicate returns in run at the same time with another user

I have an anomaly that happens from time to time in my WordPress / WooCommerce Store:

I have a unique UPC code for each product. The unique UPC is pulled from a pre-existing list in my Options Table. If I don't manually add the UPC to a product my code goes and grabs one from the options table and then deletes it. However, if more than one person is adding products to the store and they happen to hit save at approximately the same time, the code will issue the same UPC code. Is there a way to stop this from happening? Is there a way to speed up the query so if multiple people are listing products or a way to "reserve" a UPC at that moment so another query initialized at the same time does not duplicate the UPC? Delete the used UPC quicker?

      public function custom_product_meta_save($product) {
            if (isset($_POST['_upc'])) {
                if ($_POST['_upc'] != '') {
                    $product->update_meta_data('_upc', sanitize_text_field($_POST['_upc']));
                } else {
                    $available_upcs = get_option('tlg_upcs');
                    $upc_to_use = reset($available_upcs);
                    $product->update_meta_data('_upc', $upc_to_use);
                    $new_upc_list = array_diff($available_upcs, array($upc_to_use));
                    update_option('tlg_upcs', $new_upc_list);
                }
                $product->update_meta_data('_ebay_upc', $product->get_meta('_upc'));
                $product->update_meta_data('_amazon_product_id', $product->get_meta('_upc'));
                $product->update_meta_data('_amazon_id_type', 'UPC');
            } else {
                $product->delete_meta_data('_upc');
                $product->delete_meta_data('_ebay_upc');
                $product->delete_meta_data('_amazon_product_id');
                $product->delete_meta_data('_amazon_id_type');
            }
}

Open in new window

David Favor

Simple way, setup a queue for temporary UPC codes, where everyone calls a serialized function to allocate a temporary UPC code.

Doing this at the database level can turn into some gnarly (tech term) code.

Likely simple solution, use semaphore to serialize your function.

Allocate/Lock a semaphore at top of UPC generation function + unlock it at bottom of function.

ASKER CERTIFIED SOLUTION

gr8gonzo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Granlund

ASKER

@gr8gonzo
Is this correct? Line 7 and 13?

 public function custom_product_meta_save($product) {
            global $wpdb;
            if (isset($_POST['_upc'])) {
                if ($_POST['_upc'] != '') {
                    $product->update_meta_data('_upc', sanitize_text_field($_POST['_upc']));
                } else {
                    $wpdb->query( "LOCK TABLES 'wp_options' WRITE" );
                    $available_upcs = get_option('tlg_upcs');
                    $upc_to_use = reset($available_upcs);
                    $product->update_meta_data('_upc', $upc_to_use);
                    $new_upc_list = array_diff($available_upcs, array($upc_to_use));
                    update_option('tlg_upcs', $new_upc_list);
                    $wpdb->query( "UNLOCK TABLES" );
                }
                $product->update_meta_data('_ebay_upc', $product->get_meta('_upc'));
                $product->update_meta_data('_amazon_product_id', $product->get_meta('_upc'));
                $product->update_meta_data('_amazon_id_type', 'UPC');
            } else {
                $product->delete_meta_data('_upc');
                $product->delete_meta_data('_ebay_upc');
                $product->delete_meta_data('_amazon_product_id');
                $product->delete_meta_data('_amazon_id_type');
            }
}

Open in new window

David Favor

Your database version may also support locking one table too.

Check your docs to see if this is available.