When multiple users are concurrently accessing this API, the response is getting mixed up. User-1 is getting user-2’s response.
I believe this is happening due to something I am globally using in the below code.
Please help me sort this out.
const codeModel = async (req, res) => {
let connection;
try {
connection = await oracledb.getConnection("MS");
let lastCodeSQL = `perfectly running sql query with properly binding params)`;
let bind = [
req.header.USERNAME,
req.body.C_ID,
req.body.S_ID,
req.body.Q_ID,
];
const lastCodeSQLres = await connection.execute(lastCodeSQL, bind);
res.json(lastCodeSQLres);
connection.release();
} catch (err) {
logger.info(err);
} finally {
try {
if (connection) {
await connection.close(); // Ensure connection release
}
} catch (err) {
logger.error("Error closing connection:", err);
}
}
};
i have the below code in my application. Is there any scope of variables being global ?
let bind = {
userid: {
dir: oracledb.BIND_IN,
val: username.toLowerCase(),
type: oracledb.STRING,
},
c_id: {
dir: oracledb.BIND_IN,
val: cont,
type: oracledb.NUMBER,
},
q_id: {
dir: oracledb.BIND_IN,
val: quest,
type: oracledb.STRING,
},
a_q: {
dir: oracledb.BIND_IN,
val: sql,
type: oracledb.STRING,
},
q_output: {
dir: oracledb.BIND_IN,
val: JSON.stringify(something),
type: oracledb.STRING,
},
};
Here is my middleware.
let authentication=req.headers.authtoken;
const data=jsonwebtoken.verify(authentication,authString);
req.header.USERNAME = data.user.EMAIL;
2
Answers
Recommended Changes:
Here are a few things to ensure:
Connection Pooling: Ensure that the connection is correctly managed. If oracledb.getConnection("MS") is returning a connection from a pool, make sure the pool is correctly configured to handle concurrent requests. Each request should get its own connection instance.
Avoid Global Variables: Ensure that there are no global variables outside the code snippet you’ve shared that might be used to store the response or bind variables.
Proper Error Handling and Logging: You should add more robust error handling and logging to capture any anomalies during request processing.
Isolate the Request Context: Make sure each request is fully isolated in its own context. For example, every request handler should have its own bind array and lastCodeSQL string, which you already seem to be doing.
Concurrency Test: You can write unit tests or use tools like Apache JMeter or Postman to simulate concurrent users to identify if and where the responses are getting mixed up.
Here’s a slightly refactored version of your code with added comments for clarity:
You’re on the right track by using a connection pool, which is designed to manage database connections efficiently and support multiple concurrent requests. However, there are a few things to consider to ensure that each request gets its own connection instance and does not lead to mixed responses.
Understanding the Connection Pool:
Connection Pool: When you create a connection pool using oracledb.createPool, you’re essentially preparing a set of database connections that can be reused by multiple requests. This allows your application to handle multiple database operations without the overhead of establishing a new connection for each request.
Connection Reuse: When a request comes in, it borrows a connection from the pool. Once the operation is complete, the connection is released back into the pool for reuse. If not managed properly, issues can arise if connections or variables are inadvertently shared across requests.
Why Responses Might Get Mixed Up:
Shared Resources: If anything in your code (like variables or connections) is shared across different requests, and one request modifies this resource while another is using it, this can lead to mixed-up responses.
Improper Connection Management: If the connection isn’t properly isolated per request, or if the same connection is somehow shared across requests without proper locking or isolation, then the responses might get mixed.
Ensuring Each Request Gets Its Own Connection:
Get a New Connection for Each Request: Ensure that each API call fetches a new connection from the pool by using oracledb.getConnection(poolAlias). Since you are using poolAlias, your connection pool is correctly set up and should return a separate connection for each request.
Release Connection After Each Use: Always ensure that the connection is released back to the pool after each request using connection.close() or connection.release() in the finally block, which you are already doing.
No Shared Global Variables: Avoid using global or shared variables that can be accessed by multiple requests concurrently. All request-specific data should be kept within the request scope.
For Example: