WooCommerce powers 25% of all eCommerce sites on the internet, and is very battle-tested at all scales. But this wasn’t always the case.

I started working in the WooCommerce core team nearly six years ago as a relatively new developer and the first bug my manager assigned me was to fix this race condition. It was a fairly typical eCommerce bug where multiple people buying the same product can cause the stock level to go negative.

Why Redis Wasn’t an Option

Yes, the usual solution would be to add a lock. And it would work, usually. In most other companies, I would have added a Redis lock that checks and decrement the pending stock in an atomic operation.

But there’s a small issue in this approach – with 8 million sites hosted anywhere, a key value store, let alone Redis, is not guaranteed. In fact, WooCommerce didn’t mandate it. I think having very accessible minimum requirements is a major factor in its widespread adoption.

So great for product adoption, but rules out Redis counter locking. But hey, all hope is not yet lost. This constraint forced us to look for database-only solutions.

Alright, use a transaction

At that time ChatGPT was still in development, but it would have recommended transaction as the first solution. You would start a transaction before the query for checking stocks and keep that ongoing to till checkout.

Sounds cool in theory, but a couple of issues:

1. The payment step can take multiple minutes, especially with 3DS authentication taking customers to payment gateway site for payment. This means we needed some sort of intermediate locking.
   
2. Lots of third party extension things can happen between selecting stock and reducing it. We had hooks which plugins can use to add custom functionality, like reward points, custom discounts and so on.
   If any of those extensions fires a range query, there’s a risk of entire site being blocked on a single checkout.

This made transactions a non-starter, and we haven’t even considered cases like people running MyISAM instead of InnoDB as the table engine.

There’s also SELECT … FOR UPDATE

There’s also a query pattern – SELECT … FOR UPDATE, which I looked at. Ultimately, the dynamics and pro/cons are same as transactions, so that was a non starter too. It would still lock rows, with the risk of large gap locking.

So what was the solution

This means we needed a combination of :

• Locking that only uses DB. WooCommerce/WordPress only mandated PHP/MySQL as minimum requirement.
  
• Does not risk gap locking. We don’t know and can’t control what other code is running on the shop.
  
• Should expire after a reasonable amount of time. Customers can abandon orders, we need to get those stock back in circulation.
  

These requirements led to a two phase locking query, but one that didn’t have any gap locking and works for the internet, whether running multi-million AWS deployments or a humble micro VPS.

Locking queries

First we reserve the stock, by adding a new entry into a different table. We could have locked the stock entry instead of creating a new row, but that means locking in a fairly contentious table. Additionally, we can put in an order_id in the new table, limiting the gap lock that MySQL had to take.

REPLACE INTO {$wpdb->wc_reserved_stock} ( order_id, product_id, stock_quantity, expires )
SELECT {order_id}, {product_id}, {stock_quantity}, ( NOW() + INTERVAL %d MINUTE ) from DUAL
WHERE ( $query_for_stock FOR UPDATE ) - ( $query_for_reserved_stock FOR UPDATE ) >= %d

Let me break down this complex query:

1. $query_for_stock FOR UPDATE – we fetch how many stock units are remaining. This is a simple select, and FOR UPDATE locks the row on primary key index.
   
2. query_for_reserved_stock FOR UPDATE – fetches how many stocks are currently locked, and lock is not expired.
   
3. SELECT {order_id}, {product_id}, {stock_quantity}, ( NOW() + INTERVAL %d MINUTE ) from DUAL – This is what we are inserting, quantity details, and importantly an expiry timestamp.
   
4. REPLACE INTO wc_reserved_stock ... The outer query will execute all these clause in the scope of a single query to make it atomic.

If the stock needed is less than the difference of stock remaining and stock locked, the query will fail to insert, and we can reject the checkout.

Since we inserted with a timestamp embedded into it, we can start ignoring the locks that have expired, putting stocks from abandoned orders back into circulations. These expired orders would then needed to hold new locks if they are tried again.

Rollout

Developing the solution was just the beginning and rolling it out to millions of sites presented entirely different challenges. I will talk about how we proved the solution, the gaps we identified and how we rolled it out, in a different blog post.

So stay tuned, and if you run an eCommerce business, do checkout Urumi.AI, it’s the WooCommerce hosting that we have been building, which does use Redis lock here instead of the complex MySQL transaction.