Handling Concurrent Transactions with Knex.js and PostgreSQL

Question

I'm working on a Node.js application using knex.js for database operations with PostgreSQL. My database has a users table with columns id and credits. The isolation level of my PostgreSQL database is set to READ COMMITTED.

I have two asynchronous functions, substractCredits and addCredits, intended to modify the credits column for a user. The user's current credits are 10. I'm concerned about the behavior when these functions are called simultaneously. Here's the simplified code for both functions:

const addCredits = async (knex: Knex, options: any, collection: any) => {
  const trx = await knex.transaction();
  try {
    const user = await db.getUser(trx, userId); // Assume that it does something like trx.select()
    user.credits += 20;
    await db.saveUser(user); // Assume that it does something like trx.where().patch()
    await trx.commit();
  } catch (error) {
    await trx.rollback();
  }
}

const substractCredits = async (knex: Knex, options: any, collection: any) => {
  const trx = await knex.transaction();
  try {
    const user = await db.getUser(userId);
    user.credits -= 20;
    await db.saveUser(user);
    await trx.commit();
  } catch (error) {
    await trx.rollback();
  }
}

Given the scenario where substractCredits and addCredits are called at the same time, I'm trying to understand the final state of the user's credits. Here's the sequence of events as I understand it:

1. substractCredits fetches the user (credits = 10).
2. addCredits fetches the user (credits = 10) before substractCredits commits any changes.
3. addCredits updates the credits to 30 (10 + 20) and commits.
4. substractCredits then attempts to update the credits based on its initial fetch (intending to set credits to -10), but due to the READ COMMITTED isolation level, it should read the latest state before committing.

Given this sequence, what would be the final credits of the user? My understanding is that it should end up being 10, but I'm looking for confirmation or correction of my understanding.

If the answer is -10 (which I don't want), what would be the recommended approach in such cases? Ideally, I want addCredits to wait for substractCredits transaction to finish before even reading the user.

What is the general approach to handle such kind of cases where we are dealing with balance, money, credits, etc. ?

Answer 1

Here's the correction, under READ COMMITTED isolation level , first transaction will read initial data even second transaction commits new change.

As return is PostgreSQL documentation "When a transaction runs on this isolation level, a SELECT query sees only data committed before the query began and never sees either uncommitted data or changes committed during query execution by concurrent transactions."

In your scenario , final credit will be -10 .