Realtime quiz with Next.js and PlanetScale (and Ably) - concurrency issues

Question

I'm trying to build a realtime quiz. A user guesses the answer, and does a post to my Next.js endpoint (I'm moving to Ably to handle the connections/order of message).

When a user posts an answer, they send the following data:

• Answer
• Game ID

The endpoint then does the following (PlanetScale DB transaction):

• Query (game table): get the game based on game ID
• Query (guess table): count the amount of guesses for the game ID
• Mutation (guess table): insert a new guess
• Query (userDeadline table): check if user is allowed (can only guess X minutes)
• Mutation (userDeadline table): update (date) or insert userDeadline
• Check if answer is correct
• Mutation (game table): close game by updating status
• ...

All these steps are in the transaction which probably is way too much. If I load test this with 2 requests per second, it errors instantaneous.

The reason for the transaction is the count of guesses in the guess table. The game table has a level field. It starts at 1 and if the count of guesses > level * X, the game row level should be updated to 2 and send a realtime event to all the connected browsers/users. The game can only update once to the next level.

I'm aware of the potential bottleneck and security/rate limiting issues, they are not described here in detail.

What's the best way to handle this scenario? Multiple DB queries in a transaction in a realtime pub/sub system? Potentially I would have X games with Y concurrent users and the system must handle quite a load. (+- 1000 concurrent users)

Answer 1

The first axe to optimize performance and handle concurrency better would be to simplify database transactions: try to minimize the amount of work done inside transactions, that is to only include critical operations: inserting a guess and updating user deadlines.
Non-critical operations, like checking and updating game levels, are performed outside the transaction.

For PlanetScale, Query Insights should help.

// Inside POST request handler
await db.transaction(async trx => {
  // Insert guess
  await trx('guessTable').insert({ gameId, answer });
  
  // Update user deadline
  // Assuming userDeadline function updates or inserts based on conditions
  await updateUserDeadline(trx, userId, newDeadline);
});

For operations like counting guesses or updating user deadlines, consider using batch processing or asynchronous updates. For example, you could periodically update the game level based on the number of guesses rather than doing it for every guess. For instance, the checkAndUpdateGameLevel function can be called asynchronously (returning a Promise) after the transaction, allowing the API to respond to the user without waiting for this operation to complete.

// Asynchronously check game level and update if necessary
checkAndUpdateGameLevel(gameId).then(() => {
  console.log('Game level checked and updated as needed.');
});

Cache frequently read but infrequently updated data, such as game details and user permissions. That reduces database load and speeds up response times. Make sure cache consistency with your database.

// Pseudocode for getting game details with caching
async function getGameDetails(gameId) {
  let gameDetails = cache.get(`gameDetails_${gameId}`);
  if (!gameDetails) {
    gameDetails = await db('gameTable').where({ gameId }).first();
    cache.set(`gameDetails_${gameId}`, gameDetails);
  }
  return gameDetails;
}

See also "Node.js query caching with PlanetScale Boost" for more specific solutions.

Implement also rate limiting to prevent abuse and queuing to manage load. Queuing user actions and processing them asynchronously can help manage spikes in traffic.

// Express.js rate limiting example
const rateLimit = require('express-rate-limit');

const apiLimiter = rateLimit({
  windowMs: 15 * 60 * 1000, // 15 minutes
  max: 100 // limit each IP to 100 requests per windowMs
});

//  apply to all requests
app.use(apiLimiter);

That will avoid hitting PlanetScale own rate limit (600 requests per minute, with 429 error response code if you hit the rate limit).

If feasible, split the large transaction into smaller ones focused on critical sections. For instance, updating the game level can be a separate transaction from inserting a guess.
The transaction in the POST handler is already focused on critical updates. The asynchronous function checkAndUpdateGameLevel shows how you can split logic to avoid long transactions.

// Logic to check if game level needs to be updated
async function checkAndUpdateGameLevel(gameId) {
  // This might include fetching the current guess count,
  // comparing it with the level threshold, and then updating the game level if necessary.
}

Finally, make sure your database schema is optimized for the queries you are performing. Proper indexing on game ID, user ID, and other frequently queried fields can significantly improve performance.

CREATE INDEX idx_guess_game_id ON guessTable(gameId);
CREATE INDEX idx_user_deadline ON userDeadline(userId);

To recap:

// Simplified Next.js API handler
export async function postAnswer(req, res) {
  const { answer, gameId } = req.body;
  
  // Begin transaction
  try {
    // Insert guess and update user deadline in transaction
    await db.transaction(async trx => {
      // Insert guess
      await trx('guessTable').insert({ gameId, answer });
      
      // Update user deadline
      // 
    });
    
    // Asynchronously check game level and update if necessary
    checkAndUpdateGameLevel(gameId);
    
    res.status(200).json({ message: 'Guess submitted successfully' });
  } catch (error) {
    res.status(500).json({ error: 'Internal server error' });
  }
}

async function checkAndUpdateGameLevel(gameId) {
  // Logic to check if game level needs to be updated
  // That is done outside of the initial transaction to reduce its duration
}

---

How would you handle updating the game?
As the calculation is based on guess count * guesses per level?
As in every request made, the logic is executed, and it should only happen once?

To handle updating the game level efficiently, making sure the calculation based on guess count and guesses per level only triggers the update once, you would have to combine atomic operations, caching, and possibly use a pub/sub system for real-time notifications.

You should perform an atomic operation that makes sure the game level update based on the guess count happens only once. That can be achieved by including a condition in your update logic that checks the current level and guess count before proceeding with the update. That condition acts as a safeguard against concurrent updates leading to the same action multiple times.

Assuming a function shouldUpdateGameLevel(gameId) exists and checks whether the game level should be updated based on the current guess count and the level's threshold:

async function updateGameLevelIfNeeded(gameId) {
  const shouldUpdate = await shouldUpdateGameLevel(gameId);
  if (!shouldUpdate) {
    return; // Early exit if no update is needed
  }

  // Proceed with atomic update with condition
  const updated = await db.transaction(async trx => {
    const game = await trx('gameTable').where({ gameId }).forUpdate().first();
    if (game.guessCount >= game.level * guessesPerLevel) {
      // Update game level here, ensuring it only happens if the condition is still met
      const newLevel = game.level + 1;
      await trx('gameTable').where({ gameId }).update({ level: newLevel });
      return true;
    }
    return false;
  });

  if (updated) {
    // Notify all users about the level update via Ably
    publishLevelUpdate(gameId, newLevel);
  }
}

async function publishLevelUpdate(gameId, newLevel) {
  // Implementation for publishing the update to all connected users
  // That could use Ably's Realtime Channels to broadcast the update
}

If your database supports row-level locking or optimistic concurrency control (versioning), you can use these features to make sure the check-and-update operation for the game level happens in a controlled manner. With optimistic concurrency control, you could add a version number to your game record, incrementing it with each update and checking it has not changed before applying any updates.

For high read and write frequency scenarios, cache the current guess count and game level. Update this cache with every guess and use it to check if the game level update criteria are met. Be cautious with cache consistency and invalidation to make sure the cache reflects the current state of the database.

The Pub/Sub for real-time notifications could use Ably or a similar real-time messaging service to notify all connected clients about the level update once it occurs. All users would receive the update immediately without the need for polling or repeated database checks.