How to stop the same data into database when the network is so slow

Question

I use following code to accept the regsiter call from client

.post('/regsiter', async (ctx) => {
  requestfrom = JSON.parse(JSON.stringify(ctx.request.body))
  let regxemail = /^[a-z0-9]+([._\\-]*[a-z0-9])*@([a-z0-9]+[-a-z0-9]*[a-z0-9]+.){1,63}[a-z0-9]+$/;
  let email = requestfrom.email
  let password = md5(requestfrom.password)
  if (regxemail.test(email)) {
    await userModel.checkemailexist([email])
        .then(async(result) => {
            if (result.length === 0) {
                console.log("insert email to database")
                await userModel.insertUser([email,password])
            } else {
                console.log("email exist")
            }
        })
      }
})

If the visiter's network is well, this function will work well, but if the visiter's network is so slow, there's no enough response time, and it will insert some same datas into database, the result.length is always ===0, how can I stop this, any ideas?

Answer 1

You have a race condition. Specifically, if your node server receives a duplicate request while awaiting completion of your insertUser() method, that second rquest may find that the checkmailexist() method returns false. So both concurrent request processors will attempt to insert the same email value. This can happen whem your (impatient) user hits Refresh or clicks the Submit button again when the network is slow.

In other words, two concurrent requests for the same value of email may, sometimes, both yield false to your checkmailexist() function. Then they will both proceed to call insertUser() mentioning the same email. So you get a duplicate record.

Database systems provide serveral ways to handle this very common problem. One is to create a unique index on your table, so a second attempt to insert the same value will throw an error. Then your program can catch and ignore the duuplicate key error.

Database systems also provide transactions. You could begin the transaction when you query the database and commit it after you perform the insert, or roll it back if the email already exists. This will make the second invocation of checkemailexist() await the completion of the first check / insert sequence. It's difficult to tell you how to implement such database transactions without knowing what's in your methods.

MySQL offers INSERT ... IGNORE and INSERT ... ON DUPLICATE KEY UPDATE ... statements. These, combined with a unique key on your email column, let you handle the duplication logic in SQL.

Again, this is a common problem. All scalable database applications must deal with it.