Prevent race condition when concurrent API calls that write to database occurs (Or when the server is slow)

Question

Lets imagine a scenario where you would have an endpoint used to create a user. This would be within a restful application, so lets imagine that a rich client calls this API endpoint.

exports.createUser = function(req,res){
    if(req.body){
        //Check if email has already been used
        db.User.find({where:{email:req.body.email}}).success(function(user){
            if(user === null || user === undefined){
                //Create user
                res.send(201);
            } else {
                res.json(409,{error: 'User already exists'});
            }
        });
    } else {
        res.send(400);
    }
};

If I were to call this endpoint multiple time really fast, it would be possible to create multiple records with the same email in the database, even though you queryed the user table to make sure there would be no duplicate.

I'm sure this is a common problem, but how would one go about preventing this issue? I tough limiting the number of request to a certain endpoints, but that doesn't seem like a very good solution.

Any ideas? Thank you very much!

you can put a constraint on the db table to keep email unique. that won't stop the false approval, but it will keep the DB clean, and you can dbl-check the creation in a subsequent ajax call. — dandavis, Aug 13 '14 at 22:05
Many solutions, mostly depending on the database and technologies you use; like a constraint, an upsert followed by an update, an single request which insert the result of a select that returns no rows when already inserted, ... Could you be more specific about the kind of answer you are expecting ? — Volune, Aug 13 '14 at 22:19

score 3 · Accepted Answer · edited May 23 '17 at 10:24

The simplest option is to LOCK TABLE "users" IN EXCLUSIVE MODE at the beginning the transaction that does the find then the insert. This ensures that only one transaction can be writing to the table at a time.

For better concurrency, you can:

Define a UNIQUE constraint on email, then skip the find step. Attempt the insert and if it fails, trap the error and report a duplicate; or
Use one of the insert-if-not-exists techniques known to be concurrency-safe

If using a unique constraint, one thing to consider is that your app might mark users as disabled w/o deleting them, and probably doesn't want to force email addresses to be unique for disabled users. If so, you might want a partial unique index instead (see the docs).

Prevent race condition when concurrent API calls that write to database occurs (Or when the server is slow)

1 Answers1