Cleaning Up Inventory Reservations: When Orders and Reservations Drift Apart

1. How the reservation system works

MSI deliberately separates physical stock, stored per source in the inventory_source_item table, from salable quantity, which is derived as stock minus the sum of all reservations for a SKU and stock. Reservations themselves live in the inventory_reservation table as simple, additive entries: a negative reservation reduces salable quantity at checkout, a positive reservation with the same amount compensates it again once the stock is actually deducted from the source item.

This additive, never-deleted model is deliberate because it is race-condition safe: several concurrent orders can create parallel reservations without needing a lock on the source item row. The tradeoff is that the table theoretically grows without bound, and any reservation that is never correctly compensated stays in the salable_quantity calculation permanently until it is cleaned up manually.

-- Calculating salable quantity for a SKU in a stock
SELECT
    si.sku,
    si.quantity AS physical_quantity,
    COALESCE(SUM(r.quantity), 0) AS reservation_delta,
    si.quantity + COALESCE(SUM(r.quantity), 0) AS salable_quantity
FROM inventory_source_item si
LEFT JOIN inventory_reservation r
    ON r.sku = si.sku AND r.stock_id = 1
WHERE si.sku = 'WEBSHOP-SKU-001'
GROUP BY si.sku, si.quantity;

2. Common causes of orphaned reservations

The most common cause is an aborted payment: checkout creates a reservation at the start of the order to hold the quantity for the duration of the payment process. If the customer abandons or the payment fails without the order being canceled, the reservation stays in place even though no valid order ever came into existence. Depending on the payment method and timeout configuration, this happens more often than one might expect, especially with payment providers that have unreliable webhook callbacks.

A second cause is failed cron jobs, in particular the job that automatically cancels expired, incomplete orders. If that job gets stuck because of an error in another consumer queue or a timeout, open reservations pile up for orders that should long since be treated as abandoned. Manual interventions in the backend, such as deleting an order directly at the database level instead of going through the proper cancel process, also reliably leave orphaned reservations behind.

3. Existing CLI tools for checking inventory

Magento does not ship a ready-made command that automatically finds and cleans up orphaned reservations, but there are building blocks to build a custom solution on top of. The command bin/magento indexer:reindex cataloginventory_stock makes sure the legacy stock status table matches the current MSI state, but it does not fix faulty reservations themselves, only their downstream effect on the storefront display.

For the actual diagnosis, you have to run SQL against inventory_reservation and the order tables yourself. A sensible first step is comparing all open reservations with a negative amount against the status of their linked order: reservations whose order is canceled, closed, or no longer findable at all are strong candidates for manual compensation.

4. A custom cleanup script as a consistency check

A dedicated CLI command registered in Console/Command can automate this comparison. The command reads all reservations along with a metadata object referencing the linked order id, checks the current order status through OrderRepositoryInterface, and creates a compensating positive reservation for every reservation whose order is canceled or no longer exists. It is important to never modify or delete existing rows, but always compensate additively, so the race-condition-safe core principle of MSI stays intact.

The command should first run in dry-run mode and only print a list of affected reservations with SKU, quantity, and referenced order, before it actually compensates in apply mode. That allows a manual review before the first production run and builds confidence in the logic before it runs unattended on a cron schedule.

<?php
declare(strict_types=1);

namespace Mironsoft\InventoryReservationCleanup\Console\Command;

use Magento\InventoryReservationsApi\Model\ReservationBuilderInterface;
use Magento\InventoryReservationsApi\Model\AppendReservationsInterface;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

/**
 * Finds and compensates orphaned inventory reservations whose linked order
 * is canceled or no longer exists.
 */
class CleanupOrphanedReservationsCommand extends Command
{
    /**
     * @param OrphanedReservationFinder $finder
     * @param ReservationBuilderInterface $reservationBuilder
     * @param AppendReservationsInterface $appendReservations
     */
    public function __construct(
        private readonly OrphanedReservationFinder $finder,
        private readonly ReservationBuilderInterface $reservationBuilder,
        private readonly AppendReservationsInterface $appendReservations
    ) {
        parent::__construct();
    }

    /**
     * Configures the command name and the --apply option.
     *
     * @return void
     */
    protected function configure(): void
    {
        $this->setName('mironsoft:reservation:cleanup');
        $this->addOption('apply', null, InputOption::VALUE_NONE, 'Actually write compensating reservations');
    }

    /**
     * Executes the cleanup: dry run by default, writes compensations with --apply.
     *
     * @param InputInterface $input
     * @param OutputInterface $output
     * @return int
     */
    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        $orphaned = $this->finder->find();
        $reservations = [];
        foreach ($orphaned as $entry) {
            $output->writeln(sprintf(
                '%s: qty %.4f from order %s (status: %s)',
                $entry->getSku(),
                $entry->getQuantity(),
                $entry->getOrderIncrementId(),
                $entry->getOrderStatus()
            ));
            $reservations[] = $this->reservationBuilder
                ->setSku($entry->getSku())
                ->setStockId($entry->getStockId())
                ->setQuantity(abs($entry->getQuantity()))
                ->setMetadata((string) json_encode(['cleanup' => true]))
                ->build();
        }
        if ($input->getOption('apply') && $reservations !== []) {
            $this->appendReservations->execute($reservations);
            $output->writeln(sprintf('Compensated %d orphaned reservations.', count($reservations)));
        }
        return Command::SUCCESS;
    }
}

5. Distinguishing from active checkouts

Not every reservation without a linked final order is automatically faulty. A customer who has just started checkout holds a valid, temporary reservation for the duration of the session that is not yet linked to an order, because the order itself is only created at the end of checkout. A cleanup script must therefore build in a grace period, typically several hours, before a reservation without a final order counts as a candidate for compensation.

That grace period should be configurable and should follow the shop's own checkout and payment provider configuration. Payment methods with asynchronous confirmation, such as invoice purchase or certain instant transfer variants, sometimes need considerably longer than classic credit card payments before an order may finally be treated as failed.

6. Monitoring instead of a one-time cleanup

A single cleanup run only treats the symptom, not the cause. A regular, cron-driven monitoring check that tracks the number of open, uncompensated reservations per day and triggers a notification on an unusual spike is far more useful. A sudden jump almost always points to a technical problem, such as a broken payment webhook or a faulty consumer in the message queue, which deserves more urgent attention than simply mopping up the symptoms.

A simple metric works well for monitoring: the ratio between newly created negative reservations and compensating positive reservations in the same period. If that ratio drifts persistently away from one, something is structurally out of sync in the system that deserves investigation beyond a single cleanup script.

7. Performance with a heavily grown reservation table

Since inventory_reservation never deletes entries, the table grows continuously in high-traffic shops. That is usually not a problem for the salable_quantity calculation itself thanks to proper indexes, but a custom cleanup script that scans the entire history can itself become a load with several million rows. Limiting the time window, for example only checking reservations from the last thirty days, reduces runtime substantially without meaningfully hurting detection rate.

For very old, long-compensated reservations, a separate archival script is also worthwhile, moving completed reservation pairs, meaning matching negative and positive entries, into an archive table after a configurable retention period. That keeps the production table small without fully losing historical data for audits.

8. Configurability via system.xml

As with any other Mironsoft module, a dedicated system.xml section belongs here, letting the grace period for open reservations, the time window of the scan, and the recipient for monitoring notifications be configured. That lets the fulfillment team adapt thresholds to its own payment provider configuration without requiring a deployment.

A dedicated acl.xml restricts access to this sensitive configuration, since an overly aggressive cleanup could theoretically compensate valid, in-progress reservations by mistake and briefly overstate salable stock. A dedicated menu item under Stores Configuration makes the setting directly discoverable for the responsible team.

9. Common pitfalls with reservation cleanup

The most severe mistake is deleting rows from inventory_reservation directly instead of compensating additively. Deleted negative reservations increase salable_quantity instantly without ever checking whether a parallel, valid order still depends on exactly that reservation. The additive model exists precisely so existing rows never need to be touched.

A second common mistake is choosing too short a grace period, which misclassifies active checkouts as orphaned reservations. That causes customers mid-payment to suddenly see an error about unavailable stock even though their order was technically on a correct path. A generous grace period matched to the shop's own payment provider configuration reliably avoids that problem.

10. Summary

11. FAQ: Reservation Cleanup: Key Takeaways