MySQL transactions: use COMMIT and ROLLBACK in WordPress

Home » Coding » MySQL transactions: use COMMIT and ROLLBACK in WordPress
September 5, 2021 Coding, MySQL
MySQL transactions: use COMMIT and ROLLBACK in WordPress

A database transaction is a group of SQL statements that must all succeed or all fail together — ensuring your data never ends up in a partially updated, inconsistent state. Without transactions, if your script inserts an order record and then crashes before inserting the order items, you are left with a ghost order that has no items. With a transaction, MySQL rolls everything back automatically and neither record persists. MySQL uses the InnoDB storage engine for WordPress tables, which fully supports transactions. The three core statements are START TRANSACTION (begin the transaction), COMMIT (permanently save all changes), and ROLLBACK (undo all changes since START TRANSACTION). WordPress’s $wpdb object exposes these through the methods $wpdb->query( ‘START TRANSACTION’ ) and so on, which you call directly. Transactions are especially important when you perform a series of related inserts or updates — for example, creating a custom order record with multiple line items, or migrating data with a find-and-replace that touches hundreds of rows. Pair this knowledge with safe $wpdb queries and the find-and-replace guide for bulk updates. The example below wraps a multi-table insert in a transaction inside a WordPress context so you can drop it straight into a plugin.

Problem: You need to insert or update data in multiple database tables and want to ensure either all changes succeed together or none are saved if something goes wrong.

Solution: Add the following code to your functions.php or plugin file:

/**
 * Example: insert a custom order and its line items atomically.
 * Returns the new order ID on success, WP_Error on failure.
 *
 * @param int   $user_id
 * @param array $items  [ ['product_id' => 5, 'qty' => 2, 'price' => 19.99], ... ]
 * @return int|WP_Error
 */
function helloadmin_create_order( int $user_id, array $items ) {
    global $wpdb;

    $wpdb->query( 'START TRANSACTION' );

    // Step 1: insert the order header
    $inserted = $wpdb->insert(
        $wpdb->prefix . 'custom_orders',
        [
            'user_id'    => $user_id,
            'status'     => 'pending',
            'created_at' => current_time( 'mysql' ),
        ],
        [ '%d', '%s', '%s' ]
    );

    if ( ! $inserted ) {
        $wpdb->query( 'ROLLBACK' );
        return new WP_Error( 'db_error', 'Failed to create order: ' . $wpdb->last_error );
    }

    $order_id = $wpdb->insert_id;

    // Step 2: insert line items
    foreach ( $items as $item ) {
        $inserted_item = $wpdb->insert(
            $wpdb->prefix . 'custom_order_items',
            [
                'order_id'   => $order_id,
                'product_id' => absint( $item['product_id'] ),
                'qty'        => absint( $item['qty'] ),
                'price'      => floatval( $item['price'] ),
            ],
            [ '%d', '%d', '%d', '%f' ]
        );
        if ( ! $inserted_item ) {
            $wpdb->query( 'ROLLBACK' );
            return new WP_Error( 'db_error', 'Failed to insert order item: ' . $wpdb->last_error );
        }
    }

    $wpdb->query( 'COMMIT' );
    return $order_id;
}

// Raw SQL transaction (phpMyAdmin / MySQL CLI)
/*
START TRANSACTION;
  UPDATE ynYYAa7bFT_posts SET post_status = 'draft' WHERE post_author = 5;
  DELETE FROM ynYYAa7bFT_postmeta WHERE post_id IN (
      SELECT ID FROM ynYYAa7bFT_posts WHERE post_author = 5
  );
COMMIT;  -- or ROLLBACK; to undo
*/

NOTE: $wpdb->insert_id is only reliable immediately after the $wpdb->insert() call — it is reset on the next query. Within a transaction, ROLLBACK does not reset the auto-increment counter, so rolled-back inserts leave gaps in the ID sequence. This is normal MySQL behaviour and not a data integrity issue. Also, $wpdb->query() is used here for transaction control because there is no dedicated WordPress wrapper; always pair it with show_errors() or check $wpdb->last_error during development.

Related posts