Solving Concurrent Update Issue

1. Use a Separate Read Count Table (Eventual Consistency)

Instead of updating the main content table directly for every read, store read events separately and aggregate them periodically.

Steps:

1. Create a ReadCounts table:

CREATE TABLE ReadCounts (
    ContentID INT PRIMARY KEY,
    ReadCount INT DEFAULT 0
);

• Insert or update asynchronously:

  • Insert a record if it doesn’t exist or update it with ON DUPLICATE KEY UPDATE (MySQL) or MERGE (SQL Server).
  • This avoids row-level locks in the main content table.

• Batch Processing for Syncing:

  • Use a background job (SQL Agent Job, Hangfire, or a cron job) to periodically aggregate ReadCounts into the main Content table.

    UPDATE Content
    SET ReadCount = Content.ReadCount + rc.ReadCount
    FROM Content
    INNER JOIN ReadCounts rc ON Content.ID = rc.ContentID;
    
    DELETE FROM ReadCounts; -- Clear after syncing
    

    2. Use a Message Queue (Async Processing)

    Instead of updating the database on every read, push an event to a message queue and process updates asynchronously.

    Tech Stack:

  • RabbitMQ, Kafka, or Redis Streams to queue read events.
  • A background worker that listens to the queue and performs batch updates.

Which One to Choose?

• For high traffic scenarios → Redis + Batch Processing
• For event-driven architecture → Message Queue (RabbitMQ/Kafka)
• For simple optimization → Separate ReadCounts Table with Aggregation

Would you like a detailed implementation based on your current stack (ASP.NET + MSSQL)?