Assert that a database row is claimed only once
Tags: database, programming
Published: 2024-02-01 12:44:28


I recently encountered an issue with a background command running on [cron](https://www.npmjs.com/package/cron). While the command functioned as expected in my local environment, it exhibited unexpected behavior in our stage environment, where some records were processed multiple times.

```js
// The command structure is something like that
const records = await RecordsDAO.find({ status: 'pending' });

for (const record of records) {
    // process it
}
```

## Understanding the Issue

Upon investigation, I discovered that our deployment setup, which includes [horizontal auto-scaling](https://kubernetes.io/docs/tasks/run-application/horizontal-pod-autoscale/) via [CI](https://aws.amazon.com/devops/continuous-integration/#:~:text=Continuous%20integration%20refers%20to%20the,for%20a%20release%20to%20production.), had multiple instances of the command running concurrently. This led to contention over the same data, resulting in duplicated processing.


## Implementing a Solution

To address this challenge, I drew upon my recent studies of MySQL through an excellent course by [Aaron Francis](https://aaronfrancis.com/) on [PlanetScale](https://planetscale.com/). In the course's "Examples" section, a method was presented to ensure exclusive ownership of records during processing.

By introducing a new column called `owner_id`, populated with unique identifiers such as process IDs or random UUIDs, each instance of the command can claim ownership of distinct sets of records. Here's a simplified TypeScript snippet demonstrating the implementation:

```typescript
async updateBatchIdOnPendingRecords(): Promise<UUID> {
    const batch_id: UUID = randomUUID();
    await this.recordsDAO.updateMany(
        {
            status: 'pending',
            batch_id: null,
        },
        {
            $set: {
                batch_id,
            },
        },
    );

    return batch_id;
}
```

## Testing the Solution

To validate this solution without redeploying to the stage environment, I utilized the `--scale` flag in Docker Compose. I had never used this flag before so I learned some things.

- The `container_name` option cannot be used when scaling containers, as each instance must have a unique identifier.
- When a service exposes a port, a range must be allocated to ensure that each container receives a unique port assignment.

Here's a snippet from my updated `docker-compose.yml` file:

```yml
version: '3'

services:
  node:
#    container_name: records-node
    build: ./docker
    ports:
      - '3001-3009:3000'
#      - '3003:3000'
    volumes:
      - ./:/var/www/app/
    env_file:
      - .env.example
    working_dir: /var/www/app
    networks:
      - default
    extra_hosts:
      - "host.docker.internal:host-gateway"
```

Scaling the service with three containers is achieved using the following command:

```sh
docker compose up --scale node=3
```

## Conclusion

I really love learning new things and applying them. The `owner_id` column solved the problem, and it was an easy and fast implementation. While there are simpler ways to test solutions, exploring new methods enhances the learning process and deepens understanding. Of course, the test can be as simple as adding another entry of the command in the cron. However, as I mentioned earlier, I enjoy applying my new learnings, and I was eagerly waiting for an opportunity to use the `--scale` flag in a real need.

Thank you for reading! I hope this post has been insightful or enjoyable. 😊