How a Flash Sale Deadlocked MariaDB — Diagnosing WooCommerce Stock Reduction Lock Contention
· 10 min read
Sixty Seconds Into a Flash Sale, Orders Started Dying
A client runs a fashion accessories store on WooCommerce — around 150 orders on a normal day. They'd promoted a limited-edition drop on Instagram: 200 units of a single product, available from 7pm on a Thursday. I'd checked the server that afternoon. PHP-FPM was healthy, MariaDB had headroom, Redis object cache was connected. Everything looked ready.
At 7:01pm, the client messaged me: "Orders are failing. Customers getting errors at checkout."
I SSHed in. The site was responding — product pages loaded, the cart worked. But roughly one in three checkout attempts was throwing a WooCommerce "order could not be processed" error. The MariaDB error log told me exactly what was happening:
[Warning] InnoDB: Deadlock found when trying to get lock; try restarting transaction
Not one entry. Dozens per second.
What a Deadlock Actually Means Here
A deadlock is not the same as a slow query or a connection limit. It's two (or more) transactions each holding a lock that the other one needs — a circular dependency that MariaDB cannot resolve without killing one of them.
In this case, the deadlocked transactions were both trying to reduce stock for the same product. WooCommerce's wc_reduce_stock_levels() function updates the stock quantity in wp_postmeta and synchronises the value to wc_product_meta_lookup. Each UPDATE acquires an exclusive row lock (InnoDB X lock) on that product's meta row. When two transactions grab locks in a different order across these tables, deadlock.
Reading the Deadlock Log
First diagnostic step — get the actual deadlock details:
SHOW ENGINE INNODB STATUS\GThe LATEST DETECTED DEADLOCK section showed me exactly what was happening:
*** (1) TRANSACTION:
TRANSACTION 847291, ACTIVE 0.3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MariaDB thread id 1847, query id 29481
UPDATE wp_postmeta SET meta_value = '142'
WHERE post_id = 8834 AND meta_key = '_stock'
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 42 page no 18847 n bits 88
index PRIMARY of table `shop`.`wc_product_meta_lookup`
lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 9214 n bits 72
index PRIMARY of table `shop`.`wp_postmeta`
lock_mode X locks rec but not gap
*** (2) TRANSACTION:
TRANSACTION 847293, ACTIVE 0.2 sec starting index read
UPDATE wc_product_meta_lookup SET stock_quantity = 141
WHERE product_id = 8834
Transaction 1 held a lock on wc_product_meta_lookup and was waiting for wp_postmeta. Transaction 2 held a lock on wp_postmeta and was waiting for wc_product_meta_lookup. Classic deadlock — opposite lock acquisition order.
Why This Only Happens Under Load
On a normal day, stock reduction transactions complete in under 5 milliseconds. The window for two transactions to interleave is tiny. But during a flash sale with 50+ concurrent checkout attempts for the same SKU, the probability of two transactions hitting these tables in opposite order becomes near-certain within seconds.
The problem compounds: MariaDB detects the deadlock and kills one transaction (the "victim"). WooCommerce retries the order processing in some cases, but not reliably — the killed transaction often surfaces as a customer-facing error. Meanwhile, the surviving transaction now competes with 49 other new transactions that arrived in the meantime.
I checked the scale of the problem:
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits';
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Innodb_row_lock_waits | 2847 |
| Innodb_deadlocks | 312 |
+----------------------------+-------+
In the fifteen minutes since the sale started: 312 deadlocks and nearly 3,000 lock waits. Each lock wait that did not deadlock still held a PHP-FPM worker hostage for up to innodb_lock_wait_timeout seconds (default: 50 seconds). Workers were draining fast.
The Immediate Fix — Triage Under Fire
I needed to stop the bleeding without taking the site offline. Three changes, applied in sequence over about four minutes:
1. Reduce the lock wait timeout
The default innodb_lock_wait_timeout of 50 seconds is far too long for a web transaction. A customer waiting 50 seconds for checkout is already gone. I dropped it to 5 seconds so failed transactions released their workers quickly rather than piling up:
SET GLOBAL innodb_lock_wait_timeout = 5;This freed PHP-FPM workers faster. The site became responsive again, though some orders still failed.
2. Kill the deadlock backlog
A dozen transactions were already stuck in lock-wait queues. I identified and killed the longest-waiting ones:
SELECT trx_id, trx_state, trx_started, trx_wait_started, trx_mysql_thread_id
FROM information_schema.INNODB_TRX
WHERE trx_state = 'LOCK WAIT'
ORDER BY trx_wait_started ASC;KILL 1847;
KILL 1852;
KILL 1861;3. Serialise stock reduction temporarily
The nuclear option for the next hour: I added a one-line mu-plugin that forces WooCommerce to process stock reduction inside a table lock, eliminating the deadlock possibility at the cost of throughput:
<?php
add_action('woocommerce_reduce_order_stock', function($order) {
global $wpdb;
$wpdb->query('SELECT GET_LOCK("stock_reduction", 10)');
}, 1);
add_action('woocommerce_reduce_order_stock', function($order) {
global $wpdb;
$wpdb->query('SELECT RELEASE_LOCK("stock_reduction")');
}, 999);
This turned parallel stock updates into a serial queue. Throughput dropped — each checkout took an extra 50-100ms — but zero deadlocks. For a 200-unit flash sale that was going to sell out within the hour anyway, acceptable.
The Proper Fix — Applied the Next Morning
The mu-plugin was a sticking plaster. The real fix involved addressing why lock ordering was inconsistent in the first place.
Understanding the lock ordering problem
WooCommerce's stock reduction path does roughly this:
UPDATE wp_postmeta SET meta_value = $new_stock WHERE post_id = $id AND meta_key = '_stock'UPDATE wc_product_meta_lookup SET stock_quantity = $new_stock WHERE product_id = $id
But WooCommerce hooks also trigger during checkout that read from wc_product_meta_lookup first (shared locks during stock verification), then attempt the exclusive write to wp_postmeta. Depending on which hooks fire and in what order, the two tables get locked in opposite sequences.
The architectural solution
For this store, I implemented three changes:
1. Migrated stock management to HPOS tables
With WooCommerce 10.x and HPOS fully active, the store was already using wc_orders for order storage but still had legacy stock management touching wp_postmeta. I ensured the product data store was using the custom tables exclusively:
add_filter('woocommerce_product_data_store_cpt_get_products_query', function($query) {
return $query;
});More importantly, I verified the wc_product_meta_lookup table had the correct unique index:
SHOW INDEX FROM wc_product_meta_lookup WHERE Column_name = 'product_id';2. Added a Redis-based stock reservation layer
For future high-demand product launches, I implemented a lightweight reservation system using Redis atomic operations. When a customer adds a limited-stock item to their cart:
function reserve_stock_redis($product_id, $quantity = 1) {
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$key = "stock_reserve:{$product_id}";
$remaining = $redis->decrBy($key, $quantity);
if ($remaining < 0) {
$redis->incrBy($key, $quantity);
return false; // Sold out
}
return true;
}The Redis DECRBY operation is atomic — no locks, no deadlocks, no race conditions. The actual MariaDB stock update happens asynchronously via a WP-CLI worker after the order is confirmed:
wp wc stock-sync --batch=50 --interval=5This decouples the hot path (checkout) from the slow path (database writes).
3. Set innodb_lock_wait_timeout permanently to a sane value
In /etc/mysql/mariadb.conf.d/99-woocommerce.cnf:
[mysqld]
innodb_lock_wait_timeout = 10
innodb_print_all_deadlocks = ON
innodb_deadlock_detect = ONTen seconds is long enough for legitimate transactions under moderate contention but short enough that failed transactions release resources before PHP-FPM runs dry. And innodb_print_all_deadlocks = ON ensures every deadlock appears in the error log, not just the last one.
Monitoring to Catch This Early
I added two checks to the store's monitoring stack:
Prometheus/Grafana query for lock waits:
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_current_waits';Alert threshold: more than 5 concurrent lock waits for longer than 30 seconds.
WP-CLI check for failed orders during peak hours:
wp wc shop_order list --status=failed --after="$(date -d '1 hour ago' --iso-8601=seconds)" --format=countIf more than 10 orders fail within an hour, something is wrong at the database level.
The Lesson
WooCommerce was not designed for flash-sale-style concurrency on a single SKU. The stock reduction path assumes that checkout transactions will not collide on the same row often enough to matter. For most stores, that assumption holds. For a 200-person stampede hitting the same product within 60 seconds, it falls apart completely.
The fix is not bigger hardware or more PHP-FPM workers — those just delay the cliff by a few seconds. The fix is removing the contention point from the critical path entirely. Redis handles the atomic decrement. MariaDB gets the write eventually. Customers get their confirmation instantly.
If you run flash sales or limited-edition drops on WooCommerce, test this scenario before the sale goes live. Spin up k6 or Locust and hammer the checkout endpoint with 100 concurrent users buying the same product. If your error log fills with deadlock warnings, you know exactly what is coming on launch day.
I manage 70+ WordPress and WooCommerce sites, including several that run regular flash sales and limited-stock drops. If your store is approaching a high-traffic event and you want someone to stress-test the infrastructure before customers do, check the maintenance plans or get in touch.
