Nrthugu

So I have a table called 'Ready' that stores S3 object references that are ready for processing. In my application that consumes this data and mutates it, I want to be able to grab the most recent S3 object, if its relevant provider is not in use or the lock on that provider is expired.

My table structure looks like this:

key (PK) | provider | timestamp (createdAt) | locked | exp

I'm using TypeORM to create a TRANSACTION, get the most recent (non-locked) object reference, and updates all the other columns with that provider to be locked as well so we're not accidentally consuming two sets of data from the same provider.

My code looks like this:

return getManager().transaction(async (entityManger) => {

    const firstReady = await entityManger

      .createQueryBuilder(Ready, 'ready')

      .setLock('pessimistic_write')

      .select()

      .orderBy('timestamp')

      .where('locked = false or exp < :now', { now: Date.now() })

      .getOne();

    if (!firstReady) return;

    await entityManger

      .createQueryBuilder(Ready, 'ready')

      .update()

      .set({ locked: true, exp: Date.now() + vars.PG.LOCK_EXP })

      .where('provider = :provider', { provider: firstReady.provider })

      .execute();

    logger.debug(`Acquired lock on ${firstReady.provider} and S3 object key ${firstReady.key}`);

    return firstReady.key;

  });

I'm concerned that if, on the off chance, two consumers queried two separate rows with the same provider at nearly the same time, I'd have a race condition. I've considered locking the whole table when one consumer is making this query, but I'm wondering if there's a better way. Thanks!