Intermittent Lock Wait Timeout Laravel DB Transaction (with 5 retries)

Question

We've been experiencing intermittent lock timeout errors (roughly 1-2 a day out of ~250).

On checkout, we get all of the users details, save the order, process any payments, and then update the order. I think it may be the secondary update that's causing it.

Example of our code (not exactly the same but close enough):

DB::transaction(function () use ($paymentMethod, $singleUseTokenId, $requiresPayment, $chargeAccount) {
    // create order locally
    $order = Order::create([
        'blah' => $data['blah'],
    ]);

    // handle payment
    $this->handlePayment();

    // update order with new status (with a secondary transaction for safety)
    DB::transaction(function () use ($order) {
        $order->update([
            'status' => 'new status',
        ]);
    }, 5);

}, 5); // Retry transaction 5 times - this reduced the lock timeout errors a lot

And the intermittent error we get back is (actual values removed):

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction (SQL: insert into `orders` (`user_id`, `customer_uuid`, `type_uuid`, `status_uuid`, `po_number`, `order_details`, `cart_identifier`, `cart_content`, `cart_sub_total`, `cart_tax`, `cart_grand_total`, `payment_type_uuid`, `shipping_address`, `uuid`, `updated_at`, `created_at`) 

I've read lots up on it and some people say increase timeout (seems like a workaround), optimistic locking (I thought transactions already do that), and other things.

From what I can tell from database breadcrumbs, the order create sometimes takes a long time (eg saw one at 3s, another at 23s for some reason as it's usually 50ms insert), and then the other things happen and it tries to update the order but the row is still locked from the create().

Notes:

• We have 4 foreign keys on the orders table (customer uuid, user uuid, order type uuid, order status uuid) - I feel that these may be causing issues.
• Some eloquent creates take 3s, others 23s (only checked issue ones). On most orders, request is 500ms max, so these are outliers.

Any suggestions?

Solution: No primary key on orders uuid. Very silly mistake. Caused InnoDB to basically create a 6 byte key for index. And lock up from consecutive insert, then update..

Answer 1

If you see "lock wait timeout" errors look at other transactions. Particularly harmful are long running transactions. You can spot those in SHOW ENGINE INNODB STATUS\G. Climbing InnoDB history list indicates there are ones, too. Currently running long transactions will be listed in information_schema.INNODB_TRX.

Note if a transaction grabbed an exclusive lock it's not released until the end of the transaction, not the end of a query.

First, rule out long running queries. For example, slow UPDATE will hold a lock for its execution time.

After all queries are made reasonably fast, review your transactions. Make them as short as possible. Quite often clients open a transaction, execute a query or two then go to third-party API calls or do other heavy lifting and keep the transaction open. Other transactions meanwhile will be getting "Lock wait timeout".