Sequence Tables in Magento 2: Collision-Free Entity IDs Explained

1. What sequence tables are and which problem they solve

A sequence table in Magento 2 is an extremely lean helper table with exactly one column: sequence_value, defined as INT UNSIGNED AUTO_INCREMENT PRIMARY KEY. The table name follows the fixed pattern sequence_<entity>_<store>, for example sequence_order_1 for orders of the store view with ID 1, or sequence_invoice_2 for invoices of the second store view. Every store view gets its own physical sequence table for every relevant entity type, completely separate from the actual data table such as sales_order or sales_invoice.

The purpose of sequence tables is simple yet fundamental: they deliver a guaranteed collision-free, monotonically increasing integer that is completely independent from the internal entity_id of the actual entity. When Magento creates a new order, the AUTO_INCREMENT value of sales_order is not used as the visible order number. Instead, a value is reserved via a dedicated sequence table from the Magento_SalesSequence module. In Magento 2.4.8 this separation affects all core sales entities: orders, invoices, shipments, and creditmemos get their visible number exclusively through sequence tables, never through the AUTO_INCREMENT column of the entity table itself.

For developers coming from classic relational data models, this pattern initially looks like unnecessary complexity. In reality it solves a very concrete operational problem: sequence tables decouple the assignment of display IDs from the physical storage of the entity itself, which is essential in horizontally scaled, replicated, or multi-source Magento installations.

2. Why Magento moved from AUTO_INCREMENT to sequence tables

Before the current architecture, Magento used a pattern for order numbers that originated in the EAV system: the table eav_entity_store with the columns increment_prefix, increment_last_id, increment_pad_length, and increment_pad_char. The next value was determined by reading increment_last_id, incrementing it in PHP, and writing it back, a classic read-modify-write pattern with no atomic guarantee at the database level. Under parallel order processing with multiple PHP processes, this led to race conditions: two checkout processes could read the same increment_last_id value before either wrote it back, producing duplicate order numbers.

With multi-source inventory and the need to run Magento installations across multiple database masters or replicated nodes, this problem became more serious. AUTO_INCREMENT values of an entity table such as sales_order are tied to the physical table and its replication configuration. Under multi-master replication with auto_increment_increment and auto_increment_offset, assigned IDs jump in different intervals depending on the master, which is uncritical for internal primary keys but would be unacceptable for a customer-visible, sequential order number. Sequence tables solve this problem by outsourcing ID assignment into a dedicated, extremely small table that can be maintained on a single canonical write node, independent of the sharding strategy of the main data.

The second advantage concerns decoupling the internal primary key from the visible number. The entity_id of an order can remain a simple, database-wide AUTO_INCREMENT value, optimal for foreign key relationships and indexing, while the customer-facing order number stays sequential and store-specific via a separate sequence table per store view. This separation is a core principle of today's Magento architecture and was practically impossible to model cleanly with the old EAV increment pattern.

3. Architecture: SequenceBuilder, Sequence, and Magento_SalesSequence

The framework foundation lives in the Magento\Framework\DB\Sequence namespace. The interface SequenceInterface defines exactly one method: getNextValue(). The concrete implementation Magento\Framework\DB\Sequence\Sequence executes an empty INSERT INTO sequence_<table> () VALUES () on every call and then reads LAST_INSERT_ID() through the connection. These two database operations are atomic thanks to InnoDB's internal AUTO_INCREMENT mechanism, without any custom application logic for locking, race condition avoidance, or retry loops.

For physically creating the table, Magento\Framework\DB\Sequence\SequenceBuilder exists, a fluent builder class with methods such as setPrefix(), setSuffix(), setStartValue(), and create(). The builder generates the CREATE TABLE statement for the matching sequence table from this, including the correct start value via AUTO_INCREMENT = <startValue>. This building block is not used in checkout itself but exclusively at setup time, when a new store view or a new entity type is registered.

The Magento_SalesSequence module builds a convenient layer on top of this: Magento\SalesSequence\Model\Builder combines the SequenceBuilder with registration in the management tables sequence_meta and sequence_profile. Magento\SalesSequence\Model\EntityPool defines via di.xml which entity types actually need their own sequence tables: by default these are order, invoice, shipment, and creditmemo. Every entry in the entity pool automatically gets its own sequence table when a store view is created, without a developer needing to intervene manually.

4. Sequence tables per store view for orders, invoices, shipments, creditmemos

As soon as a new store view is created in the admin, Magento iterates over the EntityPool and creates a new sequence table for every registered entity type, specifically for this store view. For the store view with ID 3, this produces sequence_order_3, sequence_invoice_3, sequence_shipment_3, and sequence_creditmemo_3, each with its own counter starting at 1. This is why two shops in the same Magento installation can independently start at order number 100000001, even if they share the same sales_order table.

This store view granularity is a deliberate choice: many Magento operators configure different increment_prefix values per store view, such as DE- for the German shop and AT- for the Austrian one, while both store views use the same physical order table. Without separate sequence tables per store view, such per-market numbering with guaranteed collision freedom would not be possible without complex additional logic.

Important for daily operations: if a store view is deleted, its sequence table usually remains in the database, unless an explicit cleanup step runs. This is uncritical, but deserves attention during database audits, because orphaned sequence tables with a frozen counter value can appear as seemingly dead tables in the schema for years. A look at sequence_meta reliably shows which store ID belongs to which table, even if the store view itself was deleted long ago.

5. sequence_meta and sequence_profile: the registry behind it

Alongside the actual sequence_<entity>_<store> tables, Magento_SalesSequence creates two management tables. sequence_meta contains, per row, a combination of entity_type and store_id and thereby uniquely references which physical sequence table is responsible for which store and which entity type. This table is the central lookup point through which Magento resolves the appropriate sequence instance at runtime, without hardcoding table names in code.

The table sequence_profile references an entry in sequence_meta via meta_id and stores the actual configuration: prefix, suffix, start_value, step, max_value, warning_value, and an is_active flag. This profile concept is exactly what makes sequence tables more flexible than a plain AUTO_INCREMENT column: an operator can activate a new profile with a new prefix, for example at year-end, without changing the physical sequence table itself or losing its counter value. This simply creates a new profile record with is_active = 1, while the old profile is set to 0.

In practice, sequence_profile remains unchanged in most standard installations, with a single active profile per entity type and store. For custom development, for example when a client needs invoice numbers that restart annually with a year prefix, the profile table is the correct point of intervention, not direct manipulation of the physical sequence table.

6. Building custom sequence tables: db_schema.xml and a setup patch

For a custom module that needs collision-free, store-specific numbers, for example a service order number in an individual repair or subscription module, you follow the same pattern as the Magento core modules. The entity table itself is declared normally via db_schema.xml, with a classic AUTO_INCREMENT entity_id as the primary key and a separate increment_id column, which is later filled from the sequence table, not from its own AUTO_INCREMENT column.

The physical sequence table itself is not declared via db_schema.xml, but created programmatically at setup time via a data patch class that uses Magento\SalesSequence\Model\Builder. This class iterates over all existing store views, calling setEntityType(), setStoreId(), setPrefix(), and setStartValue() for each store view, and finishes with create(). The builder then creates both the physical table and the corresponding rows in sequence_meta and sequence_profile, exactly as when a new store view is created for the core modules.

The decisive advantage of this approach over a custom AUTO_INCREMENT column in the entity table: should the custom module later move to a sharded or replicated database, number assignment remains correct unchanged, because it already runs, from day one, through the same decoupled sequence table architecture that the Magento core modules also use.

<?xml version="1.0"?>
<!-- File: app/code/Mironsoft/ServiceOrder/etc/db_schema.xml -->
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="mironsoft_service_order" resource="default" engine="innodb"
           comment="Mironsoft Service Order Entity">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false"
                identity="true" comment="Entity ID, internal primary key, plain AUTO_INCREMENT"/>
        <column xsi:type="varchar" name="increment_id" nullable="false" length="32"
                comment="Display ID, filled from a dedicated sequence table, never AUTO_INCREMENT"/>
        <column xsi:type="smallint" name="store_id" padding="5" unsigned="true" nullable="false"
                comment="Store View ID, one sequence table exists per store view"/>
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false"
                default="CURRENT_TIMESTAMP" comment="Created At"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="entity_id"/>
        </constraint>
        <constraint xsi:type="unique" referenceId="MIRONSOFT_SERVICE_ORDER_INCREMENT_ID_STORE_ID">
            <column name="increment_id"/>
            <column name="store_id"/>
        </constraint>
        <index referenceId="MIRONSOFT_SERVICE_ORDER_STORE_ID" indexType="btree">
            <column name="store_id"/>
        </index>
    </table>
</schema>

<?php
// File: app/code/Mironsoft/ServiceOrder/Setup/Patch/Data/AddServiceOrderSequence.php

declare(strict_types=1);

namespace Mironsoft\ServiceOrder\Setup\Patch\Data;

use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\SalesSequence\Model\Builder as SequenceBuilder;
use Magento\Store\Model\StoreManagerInterface;

/**
 * Registers a dedicated sequence table for the mironsoft_service_order entity,
 * one physical sequence_serviceorder_<store_id> table per store view.
 */
class AddServiceOrderSequence implements DataPatchInterface
{
    /**
     * @param ModuleDataSetupInterface $moduleDataSetup Setup connection wrapper
     * @param SequenceBuilder $sequenceBuilder Builds and registers physical sequence tables
     * @param StoreManagerInterface $storeManager Provides the list of existing store views
     */
    public function __construct(
        private readonly ModuleDataSetupInterface $moduleDataSetup,
        private readonly SequenceBuilder $sequenceBuilder,
        private readonly StoreManagerInterface $storeManager,
    ) {
    }

    /**
     * Creates one sequence table per store view for the custom entity type.
     *
     * @return static
     */
    public function apply(): static
    {
        $this->moduleDataSetup->getConnection()->startSetup();

        foreach ($this->storeManager->getStores(true) as $store) {
            $this->sequenceBuilder
                ->setPrefix('serviceorder')
                ->setSuffix((string) $store->getId())
                ->setStartValue(1)
                ->setStoreId((int) $store->getId())
                ->setEntityType('serviceorder')
                ->create();
        }

        $this->moduleDataSetup->getConnection()->endSetup();

        return $this;
    }

    /**
     * Declares patches that must run before this one.
     *
     * @return string[]
     */
    public static function getDependencies(): array
    {
        return [];
    }

    /**
     * Declares aliases for this patch, used when a patch class was renamed.
     *
     * @return string[]
     */
    public function getAliases(): array
    {
        return [];
    }
}

7. increment_prefix and increment_pad_length: the formatting pattern

Even though the actual counting today runs through sequence tables, the old formatting pattern from eav_entity_store lives on conceptually. Back then, the columns increment_prefix, increment_pad_length, and increment_pad_char determined how a raw counter becomes a readable order number such as 100000001: an optional prefix, followed by the counter, padded to a fixed number of digits with a fill character, usually the digit 0. This pattern was not discarded, but moved into the profile configuration of sequence_profile, complemented by the actual formatting logic in the respective model layer.

Concretely, a sequence table only delivers a raw, unpadded integer value via getNextValue(), for example 247. Formatting to the final display ID with prefix and padding happens in a dedicated layer, typically with str_pad() to a fixed length, by default 8 or 9 digits for sales entities. Anyone wanting the same behavior for a custom entity combines a custom service that encapsulates the sequence instance with a formatting method following the same principle.

The following example shows a custom ID generator in Service Contract style with PHP 8.4 and constructor property promotion, which encapsulates a sequence instance for a custom entity and formats the display ID with prefix and padding, exactly following the increment_prefix/increment_pad_length pattern.

<?php
// File: app/code/Mironsoft/ServiceOrder/Api/ServiceOrderNumberGeneratorInterface.php

declare(strict_types=1);

namespace Mironsoft\ServiceOrder\Api;

/**
 * Service Contract for generating collision free, store specific
 * service order display numbers backed by a Sequence-Table.
 */
interface ServiceOrderNumberGeneratorInterface
{
    /**
     * Generates the next display ID for the given store, formatted with
     * prefix and zero padding, backed by a dedicated sequence table.
     *
     * @param int $storeId Store View ID the number is generated for
     * @return string Formatted display ID, e.g. "SO-000000248"
     */
    public function generate(int $storeId): string;
}

<?php
// File: app/code/Mironsoft/ServiceOrder/Model/ServiceOrderNumberGenerator.php

declare(strict_types=1);

namespace Mironsoft\ServiceOrder\Model;

use Magento\Framework\App\ResourceConnection;
use Magento\Framework\DB\Sequence\Sequence;
use Mironsoft\ServiceOrder\Api\ServiceOrderNumberGeneratorInterface;

/**
 * Resolves the physical sequence table by naming convention and formats
 * the raw counter value into a padded, prefixed display ID, following
 * the classic increment_prefix / increment_pad_length pattern.
 */
class ServiceOrderNumberGenerator implements ServiceOrderNumberGeneratorInterface
{
    private const string PREFIX = 'SO-';
    private const int PAD_LENGTH = 9;
    private const string PAD_CHAR = '0';

    /**
     * @param ResourceConnection $resourceConnection Provides the DB adapter used by the sequence
     */
    public function __construct(
        private readonly ResourceConnection $resourceConnection,
    ) {
    }

    /**
     * @inheritDoc
     */
    public function generate(int $storeId): string
    {
        $sequenceTable = sprintf('sequence_serviceorder_%d', $storeId);
        $sequence = new Sequence($this->resourceConnection, $sequenceTable);

        /** @var int $rawValue */
        $rawValue = $sequence->getNextValue();

        return self::PREFIX . str_pad((string) $rawValue, self::PAD_LENGTH, self::PAD_CHAR, STR_PAD_LEFT);
    }
}

8. Gaps, collisions, and performance under high load

Gaps in the number sequence are not a malfunction with sequence tables, but expected behavior. Every call to getNextValue() executes a real INSERT and thereby irrevocably reserves an AUTO_INCREMENT value, even if the actual order subsequently fails, the checkout process is aborted, or the surrounding transaction is rolled back. InnoDB never releases an AUTO_INCREMENT value that has already been assigned, not even after a rollback. A support ticket reporting "order number 100000042 is missing from the list" is, in the vast majority of cases, not a bug in the sequence table, but an aborted checkout attempt that already reserved a number before actually saving the order.

With collisions, the cause is almost always outside the sequence logic itself: manual data interventions, restoring an older database backup without adjusting the counter value, or merging two previously separate shop databases with overlapping order numbers. The actual sequence table cannot, by itself, deliver duplicate values due to InnoDB's atomic AUTO_INCREMENT behavior, as long as nobody manually writes into it from outside or manipulates its counter value.

Under high concurrency, for example in flash sale scenarios with hundreds of simultaneous checkouts, the performance characteristics of sequence tables remain favorable: the table has only one column and no secondary index, so InnoDB's internal AUTO_INCREMENT lock is held only extremely briefly, independent of the size of the actual sales_order table. With innodb_autoinc_lock_mode = 2, the interleaved mode recommended in Magento 2.4, the table-level lock between multiple simultaneous INSERT statements is additionally eliminated, which noticeably improves throughput under parallel checkouts compared to the classic, table-level-locked auto_increment_lock_mode = 0.

-- Physical structure of a Sequence-Table, exactly as created by SequenceBuilder
CREATE TABLE `sequence_order_1` (
  `sequence_value` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sequence_value`)
) ENGINE=InnoDB AUTO_INCREMENT=1248 DEFAULT CHARSET=utf8mb4;

-- Check the current AUTO_INCREMENT counter of a sequence table
SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'sequence_order_1';

-- Compare the sequence counter with the highest increment_id actually
-- used in sales_order for the same store, to detect a desynced sequence
SELECT MAX(CAST(SUBSTRING_INDEX(increment_id, '-', -1) AS UNSIGNED)) AS highest_used
FROM sales_order
WHERE store_id = 1;

-- List all registered entity types and stores for a quick sanity check
SELECT m.entity_type, m.store_id, p.prefix, p.start_value, p.is_active
FROM sequence_meta m
INNER JOIN sequence_profile p ON p.meta_id = m.meta_id
ORDER BY m.entity_type, m.store_id;

-- Detect gaps between consecutive sequence values (expected after
-- rolled back orders, not a bug, but useful for support audits)
SELECT sequence_value,
       sequence_value - LAG(sequence_value) OVER (ORDER BY sequence_value) AS gap
FROM sequence_order_1
HAVING gap > 1;

9. Safely repairing a broken sequence table in a live shop

Before changing anything on a sequence table, take stock: which entity type and which store ID are affected, which physical table is it exactly, and what is the highest value already used in the production data. A look at sequence_meta and sequence_profile provides the mapping, and a comparison against the highest actually used increment_id in the entity table gives the reference value the counter must never fall below.

The safe repair path is exclusively ALTER TABLE sequence_order_1 AUTO_INCREMENT = <new_value>, where <new_value> must be at least one higher than the highest value already used. In InnoDB, this operation is a pure metadata operation, usually running in under a second and requiring no long exclusive lock on the table. Before the change, a targeted mysqldump of only the affected sequence table, not the entire schema, is recommended, to keep the maintenance window downtime short.

Absolutely avoid TRUNCATE TABLE on a production sequence table: a TRUNCATE irrevocably resets the AUTO_INCREMENT counter to the start value, meaning the very next checkout would generate an order number that has already been assigned and is long visible in the shop, with follow-on problems for accounting, invoice numbers, and ERP reconciliation. Equally taboo is directly deleting or modifying rows in sequence_meta or sequence_profile without exact knowledge of the relationship to the physical table, since an inconsistent registry causes Magento to resolve the wrong sequence instance, or none at all, for an entity type and store at runtime. After every repair, a test order in a staging environment followed by checking the generated number belongs to the mandatory checklist before the change is considered complete.

Many of the decisions mentioned are best understood side by side: which approach used to be standard, which one is standard today, and what concrete advantage the sequence table brings over the classic AUTO_INCREMENT column on the entity table itself.

10. Summary

Sequence tables are the foundation with which Magento 2 assigns collision-free, store-specific display IDs for orders, invoices, shipments, and creditmemos, completely decoupled from the internal AUTO_INCREMENT primary key of the respective entity table. Instead of an error-prone read-modify-write pattern like the old eav_entity_store approach, every sequence table uses InnoDB's own atomic AUTO_INCREMENT mechanism on a minimal single-column table, which structurally rules out race conditions under parallel checkouts while also laying the foundation for multi-master and multi-source capable installations.

The registry made up of sequence_meta and sequence_profile further makes the system configurable without touching the physical counter table: prefix, padding, and start value can be changed via new profiles, while the underlying counter value remains untouched. For custom entities, Magento\SalesSequence\Model\Builder, db_schema.xml, and a data patch class provide the same robust infrastructure that the Magento core modules use, instead of implementing a custom, potentially error-prone ID assignment.

11. FAQ: Sequence Tables in Magento 2