Create then lock / general locking confusion & questions

Note

I added this under docs mainly because I have run into a few general cases where I have had confusion and had to run through source code, hit deadlock cases, etc when it comes to locking and think it'd be helpful to address that at some point within the docs if we can.

Issue Description

I am using Sequelize versions 3 and 4 throughout but this should all be fairly generalized to any version (i think).

This may just be me not understanding the general flow of everything properly, but wanted to make sure I am doing this right. I spent just over an hour trying to read through the source as well as searched github issues & stackoverflow. It doesn't seem like there's any way to create and lock instantly when creating a new row.

I am not 100% sure if maybe locking just isn't needed when creating a row within a transaction because maybe it doesn't actually add the updated row until the transaction completes? If this is the case then I think what I am doing now works fine.

I have a table Accounts with a column address is used to lock the row during a transaction being run. The column is TEXT type and it is not set as unique in the sql schema, but it is essentially unique in implementation (didn't setup the db schema myself, working with what was here).

This all works just fine and I implement it using a custom query which looks similar to below. We do not use findOrCreate because only a small subset of the addresses within the addresses array should not exist.

const accountModels = await db.query(
    'SELECT * FROM accounts WHERE address IN (:addresses) FOR UPDATE',
    {
      type: db.QueryTypes.SELECT,
      replacements: { addresses },
      transaction,
    },
  );

I am not clear why we didn't use Sequelize findAll with the lock property for the query above directly, also not my decision :-P. Happy to do so if there are concerns about the way it was done.

There is a small subset of those accounts which may not exist already so we then have to run through and check if this exist, creating if they don't.

DISCLAIMER I did not write the code here. The original programmer stated it was 100% necessary to do it the way he wrote it or else "nothing works", so not sure why he didn't want to use the findOrCreate when a transaction is present (if there is a valid reason, please do tell). He is no longer accessible so we've left as-is since it's been non-critical. He wrote this BEFORE we added locking to the row during this fn.

NOTE: I have manually modified the below a bit to try to make it cleaner for this issue, so it may have some errors or inconsistencies.

const getDefaultAccount = (address, transaction) => {
  if (transaction) {
    return (
      Account
        .findOne({
          where: { address },
          lock: transaction ? transaction.LOCK.UPDATE : undefined,
          transaction,
        })
        .then(model => model || Account.create(
              { 
                  address,
                 ...DEFAULT_ACCOUNT_PROPS
              },
              { transaction },
            ),
        )
    );
  }
  return Account
    .findOrCreate({
      where: { address },
      defaults: DEFAULT_ACCOUNT_PROPS,
    })
    .then(([account]) => account);
};

Basically once we run the initial locking on all the rows that already exist, we then iterate an array which has the addresses which may potentially not yet exist and run the getDefaultAccount function on any which were not returned in the initial SELECT.

I added a super slimmed down version of that below but probably not necessary for the issue at hand. It may have logical errors due to this.

  await Promise.all(
   tierOneAddresses.reduce((promises, address) => {
      if (!accountModels.find(model => model.address === address)) {
        // the account was not found while conducting the query
        // above, we will create it and add the result to the map.
        promises.push(
          getDefaultAccount(address, transaction).then(
            newAccountModel => {
              accountModels.push(newAccountModel);
            },
          ),
        );
      }
      return promises;
    }, []),
  );

I was just going to do a reload on the resulting model and add the lock to the reload options since i did see that the method should pass through to the select and implement the lock options.

Just wanted to make sure I wasn't off-base and check in since it's not clear! Thanks for the help!

What was unclear/insufficient/not covered in the documentation

There isn't really much or any information on locking, when it can be done or used throughout. It is fairly clear that it can be used for the find functions but nothing about how to lock a newly created table so that others dont instantly query and also start updating it while everything runs.

If possible: Provide some suggestion on how we can enhance the docs

I think it would be extremely helpful to have a nice section on locking, how and when it can be used , and the various cases like described above. I know a lot of that is sql and not sequelize but it's not overall that clear when it can be done, etc.

Issue Template Checklist

Is this issue dialect-specific?

• No. This issue is relevant to Sequelize as a whole.
• Yes. This issue only applies to the following dialect(s): XXX, YYY, ZZZ
• I don't know.

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I don't know how to start, I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• I wouldn't even know how to start.

Thanks again for all you guys do, y'all are always awesome and helpful and it's appreciated greatly!