Can we clear inventory_reservation table. Magento2.3.1

Question

I have recently upgraded to 2.3.1 and noticed that some of the salable quantities do not match the actual quantities. I make sure that there are not more pending or processing orders and that everything is complete. Is it ok to delete the reservations? Would it cause any problems?

Answer 1

In Magento 2, main quantity which you see in the qty field of the product might be different than salable quantity. Customers can buy buy products according to salable quantity only. Salable quantity is calculated using main quantity in product and entries available in inventory_reservation table for that product.

Logic of salable quantity for any sku is:

main quantity + (sum of quantity field for that sku in inventory_reservation table)

What is stored in inventory_reservation table?

inventory_reservation table tracks quantity of sku let's say ABC in any order. If an order is placed, a new entry is created in inventory_reservation for sku ABC and value of quantity field is -1. When shipment is created for this order, a new entry is created with quantity +1.

Please remember when a order is placed, main quantity of the product will not decrease instantly but salable quantity will decrease according to above logic.

For example, sku ABC has 10 quantity and order is placed for 1 quantity, main quantity remains 10 but a entry is created in inventory_reservation table, with quantity -1. So salable quantity becomes 10 + (-1) = 9. This is the max quantity customers can purchase for this product.

Once the order is complete for any sku, the sum of quantity field in inventory_reservation table becomes 0.

If you see any mismatch in the main quantity and salable quantity even after all orders are completely processed that means some entries are missing in the inventory_reservation table. Any of the order creation, shipment creation, order cancellation, etc entry might be missing in the table.

Can you truncate inventory_reservation table?

If you are sure main quantity and salable quantity is same for all of your sku's available then you can truncate inventory_reservation table.

Alternatively you are also insert the missing entries in inventory_reservation table and both main quantity and salable quantity will be same but this is a long process.

Answer 2

Disclaimer: I am no magento expert, just sharing my personal experience.

I truncated the inventory_reservation table several times and nothing crashed or misbehaved afterwards. All it did was removing the reservation amount for each sku/product - which is exactly what I wanted. So I would say you can go ahead and give it a try. Just make sure you are not actually deleting the table (just use truncate).

Answer 3

It will cause several problems, because all the placed orders that do not yet have a shipment will keep a quantity in your reservation table that will be eventually released at the shipment time.

Imagine you have a product with quantity 10 and one order with quantity 2 of such product. The mechanism works as follow:

• At order time: quantity is 10, reservation is 2, availability is $qty - SUM(reservation) = 10-2 = 8
• At shipment time: quantity is $qty - $shippedQty = 10-2 = 8, reservation is $reservation+$compensation = 2 + (-2) = 0, availability is $qty - SUM(reservation) = (10-2) + (2-2) = 8

So, now, if you drop your reservation table, at shipment time you will have a ghost compensation increasing your availability.

In few words: NO, it is not safe at all unless you know perfectly what you are doing.

Instead, you may want to use:

• bin/magento inventory:reservation:list-inconsistencies
• bin/magento inventory:reservation:create-compensations

Answer 4

Refer below module for Remove Inventory Reservation on Order Place.

https://github.com/AmpersandHQ/magento2-disable-stock-reservation