Race Condition in Adonisjs with Cockroachdb

A race condition in AdonisJS with CockroachDB typically occurs when multiple concurrent requests read and write the same database rows without proper synchronization, leading to non-deterministic outcomes. Unlike some databases that offer stronger default isolation, CockroachDB provides serializable snapshot isolation (SSI). This means anomalies like lost updates can still manifest under high concurrency if application logic does not explicitly enforce consistency.

In AdonisJS, common patterns that expose race conditions include:

• Reading a value (e.g., available inventory or account balance) in one request, computing a new value, and writing it back without preventing other transactions from intervening.
• Creating related records based on a prior read (e.g., creating an order and an associated line item) where another concurrent transaction may violate uniqueness or referential expectations.
• Using non-atomic updates such as increment or manual arithmetic reads/writes instead of database-level atomic operations.

Example scenario: two API requests simultaneously fetch a row, each sees quantity = 1, each decrements locally to 0, and each writes 0 back. The final quantity should be -2 if oversell is allowed, or remain 1 if the operation should be rejected, but the race causes an incorrect final state. CockroachDB’s serializable isolation will retry one transaction, but if the application does not handle retries or does not use explicit locking/conditional writes, the user experience suffers and data integrity can be compromised.

AdonisJS does not inherently serialize requests; without explicit handling, the framework will process concurrent actions in parallel. If the ORM usage is not careful (e.g., multiple queries across an await without proper transaction scoping), the effective isolation depends on CockroachDB’s ability to detect write skew and abort one transaction. Relying on this detection alone is not a robust mitigation; you should design operations to be deterministic and idempotent and express constraints in the database schema or via conditional writes.

To mitigate race conditions, prefer atomic updates and explicit transaction control in AdonisJS when working with CockroachDB. Below are concrete patterns and code examples.

1. Atomic updates with update and SQL expressions

Instead of reading, modifying, and writing, update directly in the database:

import BaseModel from '@ioc:Adonis/Lucid/Orm'

export default class Product extends BaseModel {
  public static async decrementStock(productId: number, quantity: number) {
    await this.query()
      .where('id', productId)
      .update({ stock: this.query().raw('stock - ?', [quantity]) })
  }
}

For conditional updates that should only apply if a constraint holds (e.g., stock >= requested), use a WHERE clause with the condition. If the update affects zero rows, the operation is effectively rejected, avoiding race-induced oversell.

import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import Product from 'App/Models/Product'

export default class ProductsController {
  public async purchase({ request, response }: HttpContextContract) {
    const productId = request.input('id')
    const requested = request.input('quantity')

    const updated = await Product.query()
      .where('id', productId)
      .where('stock', '>=', requested)
      .update({ stock: this.query().raw('stock - ?', [requested]) })

    if (updated === 0) {
      return response.badRequest({ message: 'Insufficient stock or concurrent modification' })
    }

    return response.ok({ message: 'Purchase successful' })
  }
}

2. Explicit transactions with retry on serialization failures

CockroachDB may abort a transaction under serializable isolation. Wrap operations in an explicit transaction and implement retry logic:

import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import Product from 'App/Models/Product'
import db from '@ioc:Adonis/Lucid/Database'

export default class OrdersController {
  public async create({ request, response }: HttpContextContract) {
    const maxAttempts = 3
    let attempt = 0

    while (attempt < maxAttempts) {
      try {
        const trx = await db.transaction()
        try {
          const product = await Product.findOrFail(request.input('productId'), trx)
          if (product.stock < request.input('quantity')) {
            await trx.rollback()
            return response.badRequest({ message: 'Insufficient stock' })
          }
          product.stock -= request.input('quantity')
          await product.save(trx)
          // create order and related rows within the same transaction
          await trx.commit()
          return response.created({ message: 'Order created' })
        } catch (error) {
          await trx.rollback()
          // retry only on serialization failures (CockroachDB error code 40001)
          if (error?.code === '40001') {
            attempt++
            continue
          }
          throw error
        }
      } catch (err) {
        return response.serverError({ message: 'Transaction failed after retries', error: err.message })
      }
    }
    return response.serverError({ message: 'Max retries exceeded' })
  }
}

3. Unique constraints and upserts to prevent duplicates

Define uniqueness at the schema level and use upserts to handle concurrent creation attempts safely:

import { DateTime } from 'luxon'
import BaseModel from '@ioc:Adonis/Lucid/Orm'
import type { HasOneThroughRelation } from '@ioc:Adonis/Lucid/Relations'

export default class Order extends BaseModel {
  public static async createUnique(userId: number, productId: number) {
    return this.updateOrCreate(
      { user_id: userId, product_id: productId },
      {
        quantity: 1,
        createdAt: DateTime.local(),
        updatedAt: DateTime.local(),
      }
    )
  }
}

Ensure your table has a CockroachDB UNIQUE constraint on (user_id, product_id). The upsert will either insert a new row or return the existing row atomically, preventing race-induced duplicates.

4. Optimistic concurrency via version column

Add a version column to your model and check it on updates:

// In your migration:
// await db.schema.alterTable('products', (table) => {
//   table.integer('version').defaultTo(0)
// })

export default class Product extends BaseModel {
  public static async safeDecrement(productId: number, expectedVersion: number) {
    const updated = await this.query()
      .where('id', productId)
      .where('version', expectedVersion)
      .update({
        stock: this.query().raw('stock - 1'),
        version: this.query().raw('version + 1'),
      })
    return updated > 0
  }
}

If the version does not match, the update affects zero rows, signaling a concurrent modification; the client should reload and retry.