Skip to content
Advertisement

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

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:

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?

UPDATE:

Thanks to @Belayer for providing the solution.

Here are some notes on how I implemented it in Sequelize/Postgres.

Sequelize, AFAICT, does not yet support sequences. So I used a raw query in Sequelize to create the sequence:

Then when it’s time to authorize the new user and assign a unique user number, I again use a raw query, with the following sql:

Advertisement

Answer

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.

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

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement