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

LVL 8
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
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.
0
gr8gonzoConsultantCommented:
The quick-and-dirty method is table locking:

<?php
// Connect to the database
$db = new mysqli("servername","user","pw","dbname");

// Lock the table called "example"
$db->query("LOCK TABLES `example` WRITE");

// Simulate something that could take up to 10 seconds to perform
sleep(10);
$db->query("INSERT INTO `example` (upccode) VALUES ('".time()."')"); // Insert a row
$db->query("SELECT * FROM `example` (upccode) VALUES ('".time()."')"); // Or whatever

// Unlock the table when you're finished
$db->query("UNLOCK TABLES");

Open in new window


Once a query write-locks a table, nothing else can access that table (not even read from it) until the same script unlocks the table. So basically you can have exclusive access to do what you need to do on that table without worrying about someone else coming in at the very same millisecond and screwing up data.

If another script tries to run a query against that table while it's locked, the other script will simply wait until the table is unlocked, and then it'll keep running (almost as if the query simply took a little longer to execute), so there's nothing really special / additional you have to do beyond the locking/unlocking of tables.

Just make sure that you use it sparingly - you don't want to overuse locking and end up with performance problems because you have a backlog of queries that are all waiting in line to gain access to the table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rgranlundAuthor Commented:
@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

0
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Your database version may also support locking one table too.

Check your docs to see if this is available.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.