How to design a high TPS Inventory? Handle 10K requests per second

One of the IT ecosystem’s common design system problems is related to designing Inventory systems for high TPS investors. The definition of this problem is related to high TPS read data without a caching mechanism, and writing to the database regarding race condition problems.

What is an Inventory design system?

There are a lot of examples of inventory IT problems, such as online shops, cinema ticket-selling systems, accommodation-selling systems even selling NFT tokens on the blockchain. In these systems, businesses are trying to sell online unique or limited products. While a business profit is related to selling a high-value amount of products, it will face race condition problems.

Many modern businesses face similar challenges when selling unique or limited products online, whether it’s an e-commerce site offering exclusive merchandise, a cinema system managing ticket sales, a hotel reservation platform dealing with limited rooms, or a blockchain marketplace trading NFT tokens. These diverse systems share a common thread — they all struggle with race conditions, which occur when multiple customers attempt to purchase the same limited item simultaneously. While businesses aim to maximize profits by selling high volumes of unique products quickly, they inevitably encounter technical difficulties where multiple customers might accidentally purchase the same item due to overlapping purchase attempts, highlighting the critical need for sophisticated technical solutions to prevent these conflicts and ensure fair, reliable transactions.

Key Points of this article:

Understanding Inventory System Challenges: The article highlights that systems such as online shops, cinema ticket platforms, accommodation booking services, and NFT marketplaces often deal with unique or limited products. These systems face race conditions when multiple users attempt to purchase the same item simultaneously, leading to potential overselling.
Race Condition Illustration: A scenario is presented where two customers, Alice and Bob, simultaneously try to buy the last unit of a product. Without proper locking mechanisms, both transactions read the stock as available, proceed to purchase, and the system ends up overselling the product.
Database Structure: The article provides a basic database schema with ‘products’ and ‘orders’ tables to track inventory and manage orders.
Pessimistic Locking Solution: To prevent race conditions, the article suggests using pessimistic locking with transactions. By setting the transaction isolation level to SERIALIZABLE and using ‘SELECT … FOR UPDATE’, the system ensures that only one transaction can access and modify the product’s stock at a time, thus preventing concurrent modifications.

Cinema Ticket-Selling Systems

Imagine a popular movie premiere where tickets are selling out quickly. Here’s how race conditions can lead to problems:

Online Shopping Systems

Similar problems occur in online retail, especially with limited-edition products:

Race Condition Problem in concurrent requests

Suppose that we design a database without a lock mechanism. What will happen? Two different clients want to buy the same products, but only one instance exists, and only the first request can buy it.

Real-World Example: Race Condition in Product Purchase

Consider a scenario where two customers, Alice and Bob, simultaneously attempt to purchase the last available unit of a product. Without proper locking:

Both transactions read the product’s stock level as 1.
Each transaction proceeds to decrement the stock by 1, believing the product is available.
Both transactions write back a stock level of 0.
The system now reflects a stock of 0, but two purchases have been made, leading to overselling.

First, let’s create a basic database structure for tracking inventory:

sql

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    quantity INT NOT NULL DEFAULT 0,
    price DECIMAL(10, 2),
    description TEXT
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    status VARCHAR(20),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

How to design a high TPS Inventory? Handle 10K requests per second

Pessimistic Lock with transaction

To prevent a race condition situation and overselling the product, it is mandatory to use the pessimistic lock.

// ProcessOrder processes an order with pessimistic lockingfunc (i *InventoryManager)
ProcessOrder(productID int, requestedQuantity int) error {    // Begin transaction with SERIALIZABLE
isolation level    tx, err := i.db.BeginTx(nil, &sql.TxOptions{        Isolation:
sql.LevelSerializable,    })    if err != nil {        return
fmt.Errorf("failed to begin transaction: %w", err)    }    defer func() {        if err != nil {
if rbErr := tx.Rollback(); rbErr != nil {
fmt.Printf("Failed to rollback transaction: %v\n", rbErr)            }        } else {            if
err := tx.Commit(); err != nil {                fmt.Printf("Failed to commit transaction: %v\n",
err)            }        }    }()    // Step 1: Acquire exclusive lock with FOR UPDATE    var
product Product    row :=
tx.QueryRow(`        SELECT product_id, quantity         FROM products         WHERE product_id = ?         FOR UPDATE`, productID)    err = row.Scan(&product.ProductID, &product.Quantity)    if err != nil {        if errors.Is(err, sql.ErrNoRows) {            return fmt.Errorf("product %d not found", productID)        }        return fmt.Errorf("failed to select product: %w", err)    }    // Step 2: Validate quantity while holding lock    if product.Quantity < requestedQuantity {        return fmt.Errorf("insufficient stock: have %d, need %d",            product.Quantity, requestedQuantity)    }    // Step 3: Update quantity while still holding lock    result, err := tx.Exec(`        UPDATE products         SET quantity = quantity - ?         WHERE product_id = ?`,         requestedQuantity, productID)    if err != nil {        return fmt.Errorf("failed to update quantity: %w", err)    }    rowsAffected, err := result.RowsAffected()    if err != nil {        return fmt.Errorf("failed to check affected rows: %w", err)    }    if rowsAffected != 1 {        return fmt.Errorf("expected to update exactly one row")    }    err = nil    return nil}

Get the availability of products

Suppose we have a high transaction per second ( more than 10k per second), and all of these requests are related to checking the availability of products. In this scenario, using a cache can handle more requests without any problems.

sql

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    quantity INT NOT NULL DEFAULT 0,
    price DECIMAL(10, 2),
    description TEXT,
    status VARCHAR(20),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    version INT NOT NULL DEFAULT 0
);

-- Cache table for quick lookups
CREATE TABLE product_cache (
    product_id INT PRIMARY KEY,
    quantity INT NOT NULL,
    status VARCHAR(20),
    version INT NOT NULL,
    last_updated TIMESTAMP NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Index for efficient cache invalidation
CREATE INDEX idx_expires_at ON product_cache (expires_at);

// CheckAvailability checks product availability using distributed caching
func (i *InventoryManager) CheckAvailability(ctx context.Context, productID int, requestedQuantity
int) (*ProductAvailability, error) {
    cacheKey := fmt.Sprintf("product:%d", productID) // Try cache first
    cached, err := i.getFromCache(ctx, cacheKey)
    if err == nil {
        return cached, nil
    }
    // Cache miss or expired, get from database
    return i.getFromDatabase(ctx, productID, requestedQuantity)
}
func (i *InventoryManager) getFromCache(ctx context.Context, cacheKey string) (*ProductAvailability,
error) {
    var availability ProductAvailability
    data, err := i.redis.Get(ctx, cacheKey).Result()
    if err != nil {
        return nil, err
    }
    err = json.Unmarshal([]byte(data), &availability)
    if err != nil {
        return nil, fmt.Errorf("failed to unmarshal cache data: %w", err)
    }
    return &availability, nil
}
func (i *InventoryManager) getFromDatabase(ctx context.Context, productID int, requestedQuantity
int) (*ProductAvailability, error) {
    tx, err := i.db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelSerializable,
    })
    if err != nil {
        return nil, fmt.Errorf("failed to begin transaction: %w", err)
    }
    defer func() {
        if err != nil {
            if rbErr := tx.Rollback();
            rbErr != nil {
                fmt.Printf("Failed to rollback transaction: %v\n", rbErr)
            }
        }
        else {
            if err = tx.Commit();
            err != nil {
                fmt.Printf("Failed to commit transaction: %v\n", err)
            }
        }
    }
    (
    )
    var product Product
    row := tx.QueryRowContext(ctx,
    `        SELECT product_id, quantity, status, version         FROM products         WHERE product_id = ?         FOR SHARE`, productID)
    err = row.Scan(&product.ProductID, &product.Quantity, &product.Status, &product.Version)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, fmt.Errorf("product %d not found", productID)
        }
        return nil, fmt.Errorf("failed to select product: %w", err)
    }
    availability := &ProductAvailability{
        ProductID: product.ProductID, Available: product.Quantity >= requestedQuantity, Quantity:
        product.Quantity, Status:
        product.Status, Version: product.Version,
    }
    // Cache the result
    cacheKey := fmt.Sprintf("product:%d", productID)
    i.mutex.Lock()
    i.cacheKeys[cacheKey] = true
    i.mutex.Unlock()
    err = i.redis.Set(ctx, cacheKey, availability, i.cacheTTL).Err()
    if err != nil {
        fmt.Printf("Failed to cache result: %v\n", err)
    }
    return availability, nil
}

When updating the database, it is mandatory to clear the cache value.

Optimize Database Schema and Indexing. Efficient database design is crucial

Based on your business needs and data, it could be indexing and partial data storage.

sql

-- Optimized product table with efficient indexing
CREATE TABLE products (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    region_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    price DECIMAL(10, 2),
    status VARCHAR(20),
    version INT NOT NULL DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category_quantity (category_id, quantity),
    INDEX idx_region_status (region_id, status),
    INDEX idx_version (version)
);

-- Partition the products table by region
CREATE TABLE products_partitioned (
    product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    region_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    price DECIMAL(10, 2),
    status VARCHAR(20),
    version INT NOT NULL DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY LIST (region_id) (
    PARTITION p_north VALUES IN (1),
    PARTITION p_south VALUES IN (2),
    PARTITION p_east VALUES IN (3),
    PARTITION p_west VALUES IN (4)
);

-- Cache table with optimized indexing
CREATE TABLE product_cache (
    cache_key VARCHAR(255) PRIMARY KEY,
    product_id BIGINT,
    quantity INT,
    expires_at TIMESTAMP NOT NULL,
    INDEX idx_expires (expires_at),
    INDEX idx_product_id (product_id)
);

Leverage Read Replicas

One way to handle high TPS requests is to increase the number of replicas for the database and Redis.

Employ Connection Pooling

Connection pooling is a technique that manages a set of database connections that can be reused across multiple requests. Instead of creating a new connection for each database operation, the application borrows an existing connection from the pool, uses it, and returns it to the pool for future use.

// ConnectionPoolManager manages database connection poolstype ConnectionPoolManager struct {
primaryDB    *sql.DB    replicaDBs   []*sql.DB    redisClient  *redis.Client    metrics
*ConnectionMetrics}// NewConnectionPoolManager creates a new connection pool managerfunc
NewConnectionPoolManager(    primaryCfg DatabaseConfig,    replicaCfgs []DatabaseConfig,)
(*ConnectionPoolManager, error) {    // Initialize primary database connection    primaryDB, err :=
NewDatabaseConnection(primaryCfg)    if err != nil {        return nil,
fmt.Errorf("failed to initialize primary database: %w", err)    }    // Initialize replica database
connections    replicaDBs := make([]*sql.DB, 0, len(replicaCfgs))    for _, cfg := range replicaCfgs
{        db, err := NewDatabaseConnection(cfg)        if err != nil {            return nil,
fmt.Errorf("failed to initialize replica database: %w", err)        }        replicaDBs =
append(replicaDBs, db)    }    return &ConnectionPoolManager{        primaryDB:    primaryDB,
replicaDBs:   replicaDBs,        redisClient:  redis.NewClient(&redis.Options{}),        metrics:
newConnectionMetrics(),    }, nil}

Monitoring Inventory System

Ensure the system can handle load:SQLServerCentral

Monitoring Tools: Use monitoring solutions to track system performance and identify bottlenecks.
Auto-Scaling: Configure auto-scaling for your services to dynamically adjust resources based on traffic patterns.

Webhook‐based inventory

In high‐throughput e-commerce environments, a webhook‐based inventory update system enables real-time notifications while minimizing database load and ensuring consistency. By treating each stock change as an event, publishing it to a durable event bus, and asynchronously delivering HTTP callbacks to subscribed endpoints, you can scale to tens of thousands of notifications per second. Key components include an event producer (e.g., after a successful purchase), an event routing layer (message broker or stream processor), a retry‐capable delivery service with idempotency support, and secure subscription management. Reliability is achieved via durable queues, exponential backoff retries, and dead‐letter queues for failed deliveries. Security is enforced using HMAC signatures and HTTPS. Comprehensive logging, metrics, and dashboards allow monitoring success rates, latencies, and error patterns. Below is a detailed breakdown of designing such a system.

Core Components of the Webhook Architecture

Event Producer

– Instrument your order‐processing service to emit an “InventoryUpdated” event after decrementing stock in the database WebDataRocks.

Event Broker

– Use a durable message queue or streaming platform (e.g., Kafka, RabbitMQ) to buffer events, provide at‐least‐once delivery, and decouple producers from consumers Inngest.

Subscription Registry

– Maintain a database of webhook subscribers, including endpoint URLs, secret keys for signing, and backoff policies Coding Monkey.

Delivery Service

– A pool of workers pulls events from the broker, builds the HTTP payload, signs it, and attempts delivery to each subscriber Medium.

Conclusion

Designing an inventory system capable of handling high TPS requires careful consideration of concurrency issues, especially race conditions that can lead to overselling. Implementing pessimistic locking with appropriate transaction isolation levels ensures data consistency and reliability, even under heavy load. By adopting such strategies, businesses can maintain the integrity of their inventory systems and provide a seamless experience for their customers.

Thank you for being a part of the community

Before you go:

Be sure to clap and follow the writer ️👏️️
Follow us: X | LinkedIn | YouTube | Newsletter | Podcast | Differ | Twitch
Check out CoFeed, the smart way to stay up-to-date with the latest in tech 🧪
Start your own free AI-powered blog on Differ 🚀
Join our content creators community on Discord 🧑🏻‍💻
For more content, visit plainenglish.io + stackademic.com

How to design a high TPS Inventory? Handle 10K requests per second