Stock Decrement Locking – Latency Spikes During Concurrent Checkouts

Answer to the Core Questions

Is the SELECT … FOR UPDATE lock sufficient to serialize concurrent stock decrements without causing overselling? In a typical MySQL/MariaDB setup with WooCommerce’s default stock‑decrement transaction, the lock does serialize updates, but it can still allow overselling if the lock is released before the transaction commits (e.g., due to a deadlock, timeout, or improper transaction handling). The lock is a safeguard, not a guarantee.

What mechanisms does WooCommerce provide to detect and resolve deadlocks during checkout? WooCommerce relies on the underlying database engine. MySQL reports deadlocks via SHOW ENGINE INNODB STATUS and logs them in error.log. WooCommerce itself does not retry the transaction automatically; it simply aborts the checkout if a lock timeout or deadlock occurs.

Which configuration changes can reduce lock contention and latency for high‑volume stores? The most effective changes are:

1. Verify and, if possible, lower the transaction isolation level to READ COMMITTED for the stock‑decrement query.
2. Switch from pessimistic locking to an optimistic concurrency strategy (e.g., a stock_version column or a lightweight advisory lock).
3. Implement a short retry/back‑off loop around the decrement operation.
4. Use WooCommerce’s High‑Performance Order Storage (HPOS) to offload stock updates to a separate table with less contention.
5. Consider queueing heavy checkout flows via a background job or a lightweight API endpoint that batches updates.

Likely Explanation vs. Confirmed Facts

Likely Explanation: Pessimistic row‑level locking causes contention when many users try to buy the same SKU, leading to lock wait timeouts and latency spikes. The lock can be released before the transaction commits, allowing a second transaction to decrement the same row and causing overselling.

Confirmed Fact: Your server logs show repeated Lock wait timeout exceeded; try restarting transaction events during peak checkout periods, and response times jump from ~200 ms to over 2 s.

Practical Steps for Your Store

1. Check the current isolation level. Run:

   SELECT @@transaction_isolation;
   

   If it returns REPEATABLE-READ, consider switching to READ COMMITTED for the stock update. Add this to wp-config.php before the decrement query:

   add_action( 'init', function() {
       global $wpdb;
       $wpdb->query( 'SET SESSION transaction_isolation = "READ COMMITTED";' );
   } );
   

2. Implement optimistic locking. Add a stock_version integer column to wp_wc_product_meta_lookup (or the relevant stock table). Update the decrement query to check and increment this column atomically:

   UPDATE wp_wc_product_meta_lookup
   SET stock_quantity = stock_quantity - 1,
       stock_version = stock_version + 1
   WHERE product_id = %d AND stock_quantity >= 1 AND stock_version = %d;
   

   If the affected rows count is 0, retry the operation.
3. Use advisory locks for very high traffic. Wrap the decrement in a lightweight lock:

   SELECT GET_LOCK( CONCAT('stock_', %d), 5 );
   -- perform decrement
   SELECT RELEASE_LOCK( CONCAT('stock_', %d) );
   

   This keeps the critical section short and avoids long row locks.
4. Enable retry/back‑off. In the checkout handler, catch WPDB::get_results() failures that return a falsey value and wait 50–200 ms before retrying, up to 3 attempts.
5. Leverage HPOS. If you’re on WooCommerce 8.0+, enable HPOS to separate order data from product meta, reducing contention on the product table.

What We Still Need to Know

Could you confirm the transaction isolation level currently used for the stock‑decrement operation? Knowing this will determine whether the first step above is applicable.

Testing and Verification

• Run a controlled load test with 200 concurrent checkouts and capture SHOW ENGINE INNODB STATUS output.
• Monitor SELECT … FOR UPDATE wait times via performance_schema.events_waits_history_long.
• After applying changes, re‑run the test and compare latency and oversell counts.