Node.js, transaction coflicts in PostgreSQL, optimistic concurrency control and transaction retries

Question

I want to use PostgreSQL transaction isolation to ensure data correctness with optimistic concurrency control pattern where conflicting transactions are automatically retried, instead of my application doing upfront locking of database rows and tables.

One usual way to implement this is that the web application retries the transaction a specific number of times within a code block or replays the HTTP request by a middleware layer, also known as HTTP request replay. Here is an example of such a middleware for Pyramid and Python web applications web.

I did not find any good information on how Node.js and its PostgreSQL driver handle situations where there are two concurrent transactions in progress and one cannot go through because of reading and write conflicts. PostgreSQL will rollback one of the transactions, but how this is signalled to the application? In Python, PSQL driver would raise psycopg2.extensions.TransactionRollbackError under this condition. For other SQL database drivers here are some exceptions they will raise.

This behaviour is more usual when you have set your SQL transaction isolation level to SERIALIZABLE, as you tend to get more conflicts under load, so I would like to handle it gracefully instead of giving HTTP 500 to users.

My question is:

• How to detect dirty read rollbacks with PostgreSQL and some of the common ORM frameworks like TypeORM - if special handling is needed and the retry library cannot be independent?

• Is there a middleware (NestJS/Express.js/others) to handle this and automatically try to replay the HTTP request N number of times whenever there is a transaction rollback from the database driver?

Answer 1

Here is how you can handle concurrency when you are working with libraries that use the pg library such as TypeORM:

/**
 * Check error code to determine if we should retry a transaction.
 * 
 * See https://www.postgresql.org/docs/10/errcodes-appendix.html and
 * https://stackoverflow.com/a/16409293/749644
 */
function shouldRetryTransaction(err: unknown) {
  const code = typeof err === 'object' ? String((err as any).code) : null
  return code === '40001' || code === '40P01';
}

/**
 * Using a repeatable read transaction throws an error with the code 40001 
 * "serialization failure due to concurrent update" if the user was 
 * updated by another concurrent transaction.
 */
async function updateUser(data: unknown) {
  try {
    return await this.userRepo.manager.transaction(
      'REPEATABLE READ',
      async manager => {
        const user = manager.findOne(User, id);
    
        // Modify user
        // ...
    
        // Save the user
        await manager.save(user);
      }
    );
  } catch (err) {
    if (shouldRetryTransaction(err)) {
      // retry logic     
    } else {
      throw err;
    }
  }
}

For retrying transactions, I recommend using a library such as async-retry which abstracts the retry logic.

You'll notice that this pattern is great for simple stuff but if you want to pass the manager around (eg. so transactions can be reused in other services) then this will become very cumbersome. I would recommend using the typeorm-transactional-cls-hooked library which leverages continuation local storage to propagate transactions.

Here is how you can replay transactions for an express app:

/**
 * Request replay middleware
 */

import retry from 'async-retry';

function replayOnTransactionError(fn: (req, res, next) => unknown) {
  return (req, res, next) => {
    retry(bail => {
      try {
        // Call the actual handler
        await fn(req, res, next);
      } catch (err) {
        if (!shouldRetryTransaction(err)) {
          // Bail out if we're not supposed to retry anymore
          return bail(err);
        }

        // Rethrow error to continue retrying
        throw err;
      }
    }, {
      factor: 2,
      retries: 3,
      minTimeout: 30,
    });
  }
}

app.put('/users/:id', replayOnTransactionError(async (req, res, next) => {
  // ...
}))