[Solved] Woocommerce/WordPress and ACID

odjuret Asks: Woocommerce/WordPress and ACID
As I understand it WordPress does not use transactions when querying the database. After looking at the Woocommerce source code it looks like Woocommerce does not either. One of my questions is: how can an application built to handle monetary transactions (orders and payments) not rely on ACID? Running without transactions renders isolation levels useless, or does it not? Opening up for all kinds of misbehaviour in the application (dirty reads/writes, read/write skew, phantom reads).

I want to dissect a section of the create order function in Woocommerce and maybe someone out there can shed some light on how Woocommerce can handle several thousands of order transactions per minute reliably without ACID.

Sky is the limit. We’ve seen instances of shops with 100,000+ products listed, handling thousands of transactions per minute. In those cases, they had great hosting support and their own developer team focused on optimization.

source: WooCommerce Scaling FAQs – WooCommerce Docs

From now on I will paste code below to help illustrate my thought process but refer to line numbers from the actual sources (linked). Each source will be given a name. Thank you.

Let’s look at a simple example where Woocommerce updates several post_meta in a loop.

It all begins in the create() function.

Code:
/**
 * Method to create a new order in the database.
 *
 * @param WC_Order $order Order object.
 */
public function create( &$order ) {
    $order->set_version( Constants::get_constant( 'WC_VERSION' ) );
    $order->set_currency( $order->get_currency() ? $order->get_currency() : get_woocommerce_currency() );
    if ( ! $order->get_date_created( 'edit' ) ) {
        $order->set_date_created( time() );
    }

    $id = wp_insert_post(
        apply_filters(
            'woocommerce_new_order_data',
            array(
                'post_date'     => gmdate( 'Y-m-d H:i:s', $order->get_date_created( 'edit' )->getOffsetTimestamp() ),
                'post_date_gmt' => gmdate( 'Y-m-d H:i:s', $order->get_date_created( 'edit' )->getTimestamp() ),
                'post_type'     => $order->get_type( 'edit' ),
                'post_status'   => $this->get_post_status( $order ),
                'ping_status'   => 'closed',
                'post_author'   => 1,
                'post_title'    => $this->get_post_title(),
                'post_password' => $this->get_order_key( $order ),
                'post_parent'   => $order->get_parent_id( 'edit' ),
                'post_excerpt'  => $this->get_post_excerpt( $order ),
            )
        ),
        true
    );

    if ( $id && ! is_wp_error( $id ) ) {
        $order->set_id( $id );
        $this->update_post_meta( $order );
        $order->save_meta_data();
        $order->apply_changes();
        $this->clear_caches( $order );
    }
}

SOURCE1: WooCommerce Code Reference

On line 86 (SOURCE1) we call $this->update_post_meta( $order ); and I assum that it is called to update the post_meta table in the database. This is the funtion:

Code:
/**
 * Helper method that updates all the post meta for an order based on it's settings in the WC_Order class.
 *
 * @param WC_Order $order Order object.
 * @since 3.0.0
 */
protected function update_post_meta( &$order ) {
    $updated_props     = array();
    $meta_key_to_props = array(
        '_order_currency'     => 'currency',
        '_cart_discount'      => 'discount_total',
        '_cart_discount_tax'  => 'discount_tax',
        '_order_shipping'     => 'shipping_total',
        '_order_shipping_tax' => 'shipping_tax',
        '_order_tax'          => 'cart_tax',
        '_order_total'        => 'total',
        '_order_version'      => 'version',
        '_prices_include_tax' => 'prices_include_tax',
    );

    $props_to_update = $this->get_props_to_update( $order, $meta_key_to_props );

    foreach ( $props_to_update as $meta_key => $prop ) {
        $value = $order->{"get_$prop"}( 'edit' );
        $value = is_string( $value ) ? wp_slash( $value ) : $value;

        if ( 'prices_include_tax' === $prop ) {
            $value = $value ? 'yes' : 'no';
        }

        $updated = $this->update_or_delete_post_meta( $order, $meta_key, $value );

        if ( $updated ) {
            $updated_props[] = $prop;
        }
    }

    do_action( 'woocommerce_order_object_updated_props', $order, $updated_props );
}

SOURCE2: WooCommerce Code Reference

Ok, on line 334 (SOURCE2) we run a loop meaning that we will call function $this->update_or_delete_post_meta( $order, $meta_key, $value ); on line 342 (SOURCE2) for each meta value we want to update. Ok, that funtion looks like this:

Code:
/**
 * Update meta data in, or delete it from, the database.
 *
 * Avoids storing meta when it's either an empty string or empty array.
 * Other empty values such as numeric 0 and null should still be stored.
 * Data-stores can force meta to exist using `must_exist_meta_keys`.
 *
 * Note: WordPress `get_metadata` function returns an empty string when meta data does not exist.
 *
 * @param WC_Data $object The WP_Data object (WC_Coupon for coupons, etc).
 * @param string  $meta_key Meta key to update.
 * @param mixed   $meta_value Value to save.
 *
 * @since 3.6.0 Added to prevent empty meta being stored unless required.
 *
 * @return bool True if updated/deleted.
 */
protected function update_or_delete_post_meta( $object, $meta_key, $meta_value ) {
    if ( in_array( $meta_value, array( array(), '' ), true ) && ! in_array( $meta_key, $this->must_exist_meta_keys, true ) ) {
        $updated = delete_post_meta( $object->get_id(), $meta_key );
    } else {
        $updated = update_post_meta( $object->get_id(), $meta_key, $meta_value );
    }

    return (bool) $updated;
}

SOURCE3: WooCommerce Code Reference

We can see that the actual update happens on row 254 (SOURCE3), when calling what I presume to be WordPress´ native update_post_meta( $object->get_id(), $meta_key, $meta_value ); function.

So this is my concern. What happens if any update on row 254 (SOURCE3) fails and the entire order creation needs to be rolled back? We previously entered a row into the post table on row 65 (SOURCE1). Does that row not need to be deleted if one of the updates of post_meta fails, leaving us with a corrupt order in the database? I cant figure out if the function calls on rows 87 (SOURCE1) and 88 (SOURCE1) validates the outcome of the order creation in any way, and if they work as some kind of COMMIT/ROLLBACK? There is not even validation/error handling on the return of the function calls on rows 86 (SOURCE1), 87 (SOURCE1), 88 (SOURCE1). How do we know if anything has failed at all?

Further more, how does WordPress/Woocommerce handle reading/writing from the database when reads and writes can affect each other (dirty/skew/phantom reads/writes) when no locking can occur because there are no transactions?

Anyone?

Many thanks!!

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.