Wordpress - generate random unique number using atomic operation and update database

I have created a table called wp_tickets which contains a range of ticket numbers generated from a custom field set in my wordpress post. The columns in the table are 'id', 'ticket_id', 'lottery_id' and 'used'.

I'm trying to generate a random unique number when a customer purchases the products on my website.

For example, if a customer orders five of the same product. It needs to generate 5 random unique numbers based on the field set in the product meta field, 'maximum_entries' and then mark those numbers as 'used' in the wp_tickets table.

I need to this to be an atomic operation so customers can't have the same number if they order at the same time.

So far, i've created a function to be called on the woocommerce_order_status_processing hook which loops through each product in the cart and gets the product qty. I've then used the range function to turn this into an array of numbers before calling array_rand.

I then use the $ticketallocated in my $wpdb update function.

The problem i have is if the number being generated is already marked as 'used' in the table then it doesn't loop back to the top and try again. I need to somehow setup an if/else statement so if the random number being generated is 5 and there's already a 5 in the database as 'used' then it will try a different number.

add_action('acf/save_post', 'my_acf_save_post', 20);
function my_acf_save_post( $post_id ) {
    global $wpdb;
    $qty = get_field('maximum_entries');
    $array = range(1, $qty);

    foreach ($array as $ticket) {
     $wpdb->insert('wp_tickets', array(
         'ticket_number' => $ticket,
         'lottery_id' => $post_id,
     ));
    }
}


add_action( 'woocommerce_order_status_processing', 'ektgn_meta_to_line_item', 20, 4 );

function ektgn_meta_to_line_item( $order_id )
{

    $order = wc_get_order($order_id);

    foreach( $order->get_items() as $item_id => $item_product ) {
        global $wpdb;

        $product_id = $item_product->get_product_id();
        $qty = get_field('maximum_entries', $product_id, true);
        $ticketOptions = range(1, $qty);
        $ticketAllocated = array_rand($ticketOptions, 1);

        $wpdb->query(
            "
            UPDATE wp_tickets 
            SET used = 1
            WHERE ticket_number= ".$ticketAllocated." AND lottery_id = ".$product_id." AND used = 0
            "
);     

}             

}

I managed to get it working by creating two functions. One responsible for getting all the available numbers from within the wp_tickets table where used = 0 and lottery_id = $postID which returns a single random number from the available tickets numbers.

The second function is then responsible for updating the wp_tickets table with the ticket number generated from the get_available_numbers function.

Both these functions are then being executed on the woocommerce_order_status_processing hook when a customer orders and is being run for each item found in their cart.

So far when i've tested it, It seems to work. However, it's hard to say how this perform with multiple customers ordering at the same time.

function get_available_numbers($postID) {
            global $wpdb;
                $result = $wpdb->get_results(
                    "
                    SELECT ticket_number
                    FROM wp_tickets
                    WHERE lottery_id = ".$postID." AND used = 0
                    ORDER BY RAND()
                    LIMIT 1
                    "
                    );
            return $result[0]->ticket_number;
}


function add_ticket_number($postID, $order_id, $number, $user_id) {
        global $wpdb;

        $wpdb->query(
            "
            UPDATE wp_tickets
            SET used = 1, order_id = ".$order_id.", user_id = ".$user_id."
            WHERE ticket_number= ".$number." AND lottery_id = ".$postID." AND used = 0
            "
                    );
}


for ( $i = 0; $i < $item_meta['_qty'][0]; $i++ ) {
                        $number = $this->get_available_numbers($product_id);
                        $this->add_ticket_number($product_id, $order_id, $number, $order->get_user_id());
                    }

UPDATE

I've actually managed to shorten this to just one function by setting used=1 on the lottery_id column and ordering the update to be random and limited by one.

function add_ticket_number($postID, $order_id, $user_id) {
        global $wpdb;

        $wpdb->query(
            "
            UPDATE wp_tickets
            SET used = 1, order_id = ".$order_id.", user_id = ".$user_id."
            WHERE lottery_id = ".$postID." AND used = 0
                        ORDER BY RAND()
                        LIMIT 1
            "
                    );
}