Sequelize: Avoid Race Condition When Updating Field with Unique Value?-CodePudding

I have a field in my postgres db called authorizedUserNumber. This field is set by default to 0, and does not auto-increment, because it is only asigned when a user has been fully onboarded.

Okay, so let's say a new user has been fully onboarded and I want to assign a unique number to the field authorizedUserNumber. In the event I have multiple servers running, I want to detect collisions of unique numbers in this field, so as to protect against race conditions.

I thought of defining authorizedUserNumber as a Sequelize unique field, and trying something like this:

// get current max authorizedUserNumber
let userWithMaxOnboardedauthorizedUserNumber = await connectors.usersClinical.findAll({
    attributes: [
        sequelize.fn('MAX', sequelize.col('authorizedUserNumber'))
    ],
});
let newLatestauthorizedUserNumber = userWithMaxOnboardedauthorizedUserNumber[0].authorizedUserNumber;
newLatestauthorizedUserNumber  = 1;

let numAttempts = 0
let done = false;
while ((!done) && (numAttempts <= 50)){
    try{
        user = await updateUser(user.userId, {authorizedUserNumber: newLatestauthorizedUserNumber});
        done = true;
    }catch(e){
        // a unique field will throw an error if you try to store a duplicate value to it
        console.log(`Collision in assigning unique authorizedUserNumber. UserId: ${user.userId}`);
        newLatestauthorizedUserNumber  = 1;
        numAttempts = 1;
    }
}

if (!done){
    console.error(`Could not assign unique authorizedUserNumber. UserId: ${user.userId}`);
}

The problem with this code, is that if the field authorizedUserNumber is defined as unique, then I can't put a default value in it. So there's no way to have it be empty prior to having the correct value placed in it.

What's the best practice for dealing with this sort of situation?

CodePudding user response：

Instead of creating a unique constraint, you can create a unique index like this:

CREATE UNIQUE INDEX ON tab (nullif(authorizedUserNumber, 0));

CodePudding user response：

Rather than defaulting to 0 just let the column be null when not set. Since the default is null there can be any number of them without violating a unique constraint. Then create a sequence for that column (do not set as column default). There is no requirement for a sequence to auto-increment, the nextval can be assigned when needed. Make the assignment from the sequence when the new user becomes fully on-boarded.

create table users ( id          integer generated always as identity
                   , name        text
                   , assignedid  integer
                   , constraint  assigned_uk unique (assignedid) 
                   ) ;                  

create sequence user_assigned_seq;

You can even make the assignment when user is created if desired. (see demo )