Have you ever seen a mob at a store fighting for the last 70% off Blu-ray player ? Have you wondered what is like if they were shopping in your e-commerce site instead? In today's post I discuss inventory contention.
In contrast with other tables where each shopper keeps its own data (Orders, Addresses, etc), inventory is shared. While a shopper is updating inventory for a SKU, other shoppers trying to get the same product will need to wait. If the product is popular enough, several shoppers will be waiting to update inventory. The shoppers for that product will notice a lag in response times and, as the number of shoppers trying to check-out increases, the whole site could be impacted.
Diagnosing inventory contention
The most common effect for inventory contention is slow checkout, but depending on the inventory model, add-to-cart could also be impacted. If the problem is severe enough, the WebContainer pool will be taken over by inventory threads rendering the site unusable (but that's a rather extreme scenario).
Javacores collected while the JVMs are experiencing problems will show the delay. This will vary depending on the inventory model. You could, for example, see threads waiting to update an inventory table, or waiting for the inventory back-end system to respond.
The following sample stack shows a thread trying to update the INVENTORY table (non-ATP inventory model) on the OrderProcess command. You can see the findByMultipleCatalogEntryAndFulfillmentCenterAndStoreForUpdate() SELECT FOR UPDATE was executed and the thread is waiting for the database (DB2 in this case) to respond:
at java/net/SocketInputStream.socketRead0(Native Method)
at java/net/SocketInputStream.read(SocketInputStream.java:140(Compiled Code))
at com/ibm/db2/jcc/am/ResultSet.next(ResultSet.java:309(Compiled Code))
Looking at the database side, you find multiple INVENTORY table selects in Lock-Wait state.
SELECT T1.QUANTITY, T1.FFMCENTER_ID, T1.INVENTORYFLAGS, T1.CATENTRY_ID, T1.QUANTITYMEASURE, T1.STORE_ID, T1.OPTCOUNTER
FROM INVENTORY T1
WHERE (T1.CATENTRY_ID = ? and T1.FFMCENTER_ID = ? and T1.STORE_ID = ?)
FOR UPDATE WITH RS
Notice that the SELECT statement is done with "FOR UPDATE", which means regular SELECT (read only) queries are allowed, but other SELECT FOR UPDATE or UPDATE statements are locked.
As with most performance issues, it's hard to predict how badly the site can be impacted and if it will be even a problem. It depends on variables such as:
How popular the product is
The inventory model ( ATP, non-ATP, OMS integration )
Site responsiveness (the slower the command, the higher the chances for contention)
The first thing is to discuss with the business team and add scenarios to the performance testing: Are they aware of hot SKUs? Do they plan aggressive promotions that are limited to a small number of products or categories? Are these limited time "flash" type promotions? Do they use site-wide, free-gift-with-purchase promotions?
Other situations that are harder to plan for, but can also lead to inventory contention are: a new hot product becoming in stock, and price errors.
When dealing with shared resources like inventory, some level of locking is always expected, so it is important to keep the tests as realistic as possible.
Fix Pack 7 non-ATP Inventory enhancements
V7 Fix Pack 7 includes two new important enhancements for inventory contention with the non-ATP model.
JR45151: AVOID LOCKING IN INVENTORY TABLE FOR RECORDS MARKED WITH NOUPDATE FLAGS
The INVENTORY table includes an INVENTORYFLAGS column that can be used to configure if inventory for that SKU should be checked or updated:
INVENTORYFLAGS: Bit flags, from low to high order, indicating how QUANTITY is used:
1 = noUpdate. The default UpdateInventory task command does not update QUANTITY.
2 = noCheck. The default CheckInventory and UpdateInventory task commands do not check QUANTITY.
Before JR45151, the INVENTORY table was always queried with "FOR UPDATE" leading to unnecessary contention for those SKUs configured to not update. With this APAR, the flags are cached, and if noUpdate is used, the row is not queried "FOR UPDATE", minimizing the locking.
For more info see: JR45151: CMVC 225626 - AVOID LOCKING IN INVENTORY TABLE FOR RECORDS MARKED WITH NOUPDATE FLAGS
JR48834: RESPONSE TIME DEGRADATION WHEN THE SAME ITEM IS PURCHASED CONCURRENTLY BY MANY SHOPPERS
In WebSphere Commerce, database transactions are committed at the end of the request. If the INVENTORY table is updated, then the row will remain locked until the end of the OrderProcess request, which can last several seconds. JR48834 provides an option for inventory to be updated in its own short transaction. This way the locks are held for a much shorter period of time reducing the contention.
To enable this option, you need to configure a flag in wc-server.xml: <com.ibm.commerce.fulfillment.commands.InventoryUpdateHelper updateImmediately="true" />
For more info see: JR48834: CMVC 225266 - RESPONSE TIME DEGRADATION WHEN THE SAME ITEM IS PURCHASED CONCURRENTLY BY MANY SHOPPERS