I have made an POC application, where the requeirment is to insert the reocord if ther is no record present in the db. This logic is not working if we hit 100 request per second with same data. For new data, there is 5-10 records are being created in table, though according to logic only there is one record shoulb be created for new record. The details of this application is below:

Language: Node JS

Framework : Express JS

Database: Oracle DB.

Please find the code

app.post('/v1/test',async function(req, res) {
    const searchInitialStartTime = new Date();
    console.log(req.requestUID);
    try{
        let dbResult = await dao.getSerachDate(req.body);
        console.log(req.requestUID,dbResult.rows.length);
        if(dbResult.rows.length == 0){
            await dao.insertSerachDate(req.body);
        }
        await utils.logSummeryDetailsInDB(null, "test", "200");
        return res.json(dbResult.rows);
    } catch(e){
        console.log(e);
        res.status(400).send("Error");
    }
}
);

I have made an POC application, where the requeirment is to insert the reocord if ther is no record present in the db. This logic is not working if we hit 100 request per second with same data. For new data, there is 5-10 records are being created in table, though according to logic only there is one record shoulb be created for new record. The details of this application is below:

Language: Node JS

Framework : Express JS

Database: Oracle DB.

Please find the code

app.post('/v1/test',async function(req, res) {
    const searchInitialStartTime = new Date();
    console.log(req.requestUID);
    try{
        let dbResult = await dao.getSerachDate(req.body);
        console.log(req.requestUID,dbResult.rows.length);
        if(dbResult.rows.length == 0){
            await dao.insertSerachDate(req.body);
        }
        await utils.logSummeryDetailsInDB(null, "test", "200");
        return res.json(dbResult.rows);
    } catch(e){
        console.log(e);
        res.status(400).send("Error");
    }
}
);

• javascript
• node.js
• oracle-database
• express

Share Follow asked Mar 4 at 11:25 Chinmoy SamantaChinmoy Samanta 1,41899 silver badges1717 bronze badges 1

• Stack Overflow is a question and answer platform for specific questions. Do you have a specific question? The problem is probably a race condition. Multiple requests get an empty result in let dbResult = await dao.getSerachDate(req.body); before the first request writes in await dao.insertSerachDate(req.body); – jabaa Commented Mar 4 at 11:37

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

You appear to be using await dao.getSerachDate(req.body) to find out of there is an existing row in the table and, if there is not, then use await dao.insertSerachDate(req.body); to insert a new row.

This is leading to asynchronous issues where two requests to insert the row are handled near-simultaneously and both check if a row exists and then both try to insert a new row.

Don't use a two-stage process where you check first before inserting.

Either:

• Don't check and just directly insert the row and, if there is an existing row, let the database raise a constraint violation for a duplicate row and catch the error in Node; or

• Use a MERGE statement:

  MERGE INTO destination dst
  USING DUAL
  ON (dst.id = :your_id)
  WHEN NOT MATCHED THEN
    INSERT (id, column1) VALUES (:your_id, :your_column1)
  

  Which combines the check and insert into a single SQL statement.

---

Maybe something like (untested):

app.post(
  '/v1/test',
  async function(req, res)
  {
    const searchInitialStartTime = new Date();
    console.log(req.requestUID);
    try
    {
      let dbResult = await dao.insertSerachDate(req.body);
      await utils.logSummeryDetailsInDB(null, "test", "200");
      return res.json(dbResult.rows);
    }
    catch(e)
    {
      console.log(e);
      // Check if the error is from a unique constraint violation here.
      // If it is handle the error and return the appropriate status (or query the
      // database and return the existing data).
      // If not, use your previous error handler.
      res.status(400).send("Error");
    }
  }
);