Node JS Express - Oracle Connection Pooling (ORA-24418: Cannot open further sessions)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29846188/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Node JS Express - Oracle Connection Pooling (ORA-24418: Cannot open further sessions)
提问by ash
I'm having issues with a the Oracle DB module: https://github.com/oracle/node-oracledb/blob/master/doc/api.md
I'm having issues with a the Oracle DB module: https://github.com/oracle/node-oracledb/blob/master/doc/api.md
I have an application which has between 300 and 900 hits per hour (normally from around 100 users). The application has many $.post requests in the background to retrieve information from a database and display to the user.
I have an application which has between 300 and 900 hits per hour (normally from around 100 users). The application has many $.post requests in the background to retrieve information from a database and display to the user.
I've recently switched to this module as it is Oracle's own (I was using https://github.com/joeferner/node-oraclepreviously).
I've recently switched to this module as it is Oracle's own (I was using https://github.com/joeferner/node-oraclepreviously).
Here's how I have it set out:
Here's how I have it set out:
/bin/www
/bin/www
oracledb.createPool(
{
user : 'USER'
password : 'PASS',
connectString : 'DB:1521/SID:POOLED',
connectionClass : 'ARBITRARY_NAME',
poolMin : 1,
poolMax : 50,
poolTimeout : 300
},
function(err, pool)
{
if (err) {
console.log(err);
}
require('../libs/db')(pool); // Export pool to separate file
}
)
/libs/db.js
/libs/db.js
module.exports = function(pool) {
// Require oracle module for formatting
var oracledb = require('oracledb');
// Export acquire and query function
module.exports.acquire_and_query = function(sql, params, callback){
// ACQUIRE connection from pool
pool.getConnection(function(err, connection){
// NUMBER OF CONNCETIONS OPEN
console.log("ORACLE: CONNX OPEN: " + pool.connectionsOpen);
// NUMBER OF CONNEXTIONS IN USE
console.log("ORACLE: CONNX IN USE: " + pool.connectionsInUse);
if (err) {
console.log(err.message);
return;
}
// Use connection to QUERY db and return JSON object
connection.execute(sql, params, {maxRows: 1000, isAutoCommit: true, outFormat: oracledb.OBJECT}, function(err, result){
// Error Handling
if (err) {
console.log(err.message); // Log the error
return false; // Return false for our error handling
}
// Release the connection back to the pool
connection.release(function(err) {
if (err) {
console.log(err.message);
return;
}
})
// Return callback with rowset first, out bind paramaters second
return callback(result.rows, result.outBinds, result.rowsAffected);
})
})
}
}
}
This module "acquire_and_query" is called from with in our application, and has SQL and it's params passed in to be executed.
This module "acquire_and_query" is called from with in our application, and has SQL and it's params passed in to be executed.
The Oracle DB has a maximum allowed of Pooled connections set to 80 (and we are not exceeding them) - and generally looks pretty happy.
The Oracle DB has a maximum allowed of Pooled connections set to 80 (and we are not exceeding them) - and generally looks pretty happy.
The node application however is constantly throwing out an ORA-24418: Cannot open further sessions, and I am unsure how to resolve this.
The node application however is constantly throwing out an ORA-24418: Cannot open further sessions, and I am unsure how to resolve this.
Thanks.
Thanks.
回答by ash
Resolved - Issue: My poor coding!
Resolved - Issue: My poor coding!
I had unwittingly set a Socket.IO event to update the view for EVERYONE connected multiple times (rather than querying the database once, then sending the view over the socket...) sigh
I had unwittingly set a Socket.IO event to update the view for EVERYONE connected multiple times (rather than querying the database once, then sending the view over the socket...) sigh
I was also even more stupidly using a for loop in a transaction based query (which was INSERTing multiple data on each run)... Once I had changed this to a recursive pattern - it went ran swimmingly!
I was also even more stupidly using a for loop in a transaction based query (which was INSERTing multiple data on each run)... Once I had changed this to a recursive pattern - it went ran swimmingly!
Good article here about for loops and recursive patterns: http://www.richardrodger.com/2011/04/21/node-js-how-to-write-a-for-loop-with-callbacks/#.VaQjJJNViko
Good article here about for loops and recursive patterns: http://www.richardrodger.com/2011/04/21/node-js-how-to-write-a-for-loop-with-callbacks/#.VaQjJJNViko
Anyway - here's what I use now (and it works rather well) Using node-oracledb v0.6 (https://github.com/oracle/node-oracledb) and Express 4 (http://expressjs.com/)
Anyway - here's what I use now (and it works rather well) Using node-oracledb v0.6 (https://github.com/oracle/node-oracledb) and Express 4 (http://expressjs.com/)
bin/www
bin/www
/**
* Database
*/
// AS PER DOCUMENTATION: https://github.com/oracle/node-oracledb/blob/master/examples/dbconfig.js
var dbconfig = require("../libs/dbconfig.js");
oracledb.connectionClass = dbconfig.connectionClass,
oracledb.createPool({
user: dbconfig.user,
password: dbconfig.password,
connectString: dbconfig.connectString,
poolMax: 44,
poolMin: 2,
poolIncrement: 5,
poolTimeout: 4
}, function(err, pool) {
if (err) {
console.log("ERROR: ", new Date(), ": createPool() callback: " + err.message);
return;
}
require('../libs/oracledb.js')(pool);
});
libs/oracledb.js
libs/oracledb.js
module.exports = function(pool) {
////////////////////////////
// INSTANTIATE THE DRIVER //
////////////////////////////
var oracledb = require("oracledb");
//////////////////////
// GET A CONNECTION //
//////////////////////
var doConnect = function(callback) {
console.log("INFO: Module getConnection() called - attempting to retrieve a connection using the node-oracledb driver");
pool.getConnection(function(err, connection) {
// UNABLE TO GET CONNECTION - CALLBACK WITH ERROR
if (err) {
console.log("ERROR: Cannot get a connection: ", err);
return callback(err);
}
// If pool is defined - show connectionsOpen and connectionsInUse
if (typeof pool !== "undefined") {
console.log("INFO: Connections open: " + pool.connectionsOpen);
console.log("INFO: Connections in use: " + pool.connectionsInUse);
}
// Else everything looks good
// Obtain the Oracle Session ID, then return the connection
doExecute(connection, "SELECT SYS_CONTEXT('userenv', 'sid') AS session_id FROM DUAL", {}, function(err, result) {
// Something went wrong, releae the connection and return the error
if (err) {
console.log("ERROR: Unable to determine Oracle SESSION ID for this transaction: ", err);
releaseConnection(connection);
return callback(err);
}
// Log the connection ID (we do this to ensure the conncetions are being pooled correctly)
console.log("INFO: Connection retrieved from the database, SESSION ID: ", result.rows[0]['SESSION_ID']);
// Return the connection for use in model
return callback(err, connection);
});
});
}
/////////////
// EXECUTE //
/////////////
var doExecute = function(connection, sql, params, callback) {
connection.execute(sql, params, { autoCommit: false, outFormat: oracledb.OBJECT, maxRows:1000 }, function(err, result) {
// Something went wrong - handle the data and release the connection
if (err) {
console.log("ERROR: Unable to execute the SQL: ", err);
//releaseConnection(connection);
return callback(err);
}
// Return the result to the request initiator
// console.log("INFO: Result from Database: ", result)
return callback(err, result);
});
}
////////////
// COMMIT //
////////////
var doCommit = function(connection, callback) {
connection.commit(function(err) {
if (err) {
console.log("ERROR: Unable to COMMIT transaction: ", err);
}
return callback(err, connection);
});
}
//////////////
// ROLLBACK //
//////////////
var doRollback = function(connection, callback) {
connection.rollback(function(err) {
if (err) {
console.log("ERROR: Unable to ROLLBACK transaction: ", err);
}
return callback(err, connection);
});
}
//////////////////////////
// RELEASE A CONNECTION //
//////////////////////////
var doRelease = function(connection) {
connection.release(function(err) {
if (err) {
console.log("ERROR: Unable to RELEASE the connection: ", err);
}
return;
});
}
//////////////////////////////
// EXPORT THE FUNCTIONALITY //
//////////////////////////////
module.exports.doConnect = doConnect;
module.exports.doExecute = doExecute;
module.exports.doCommit = doCommit;
module.exports.doRollback = doRollback;
module.exports.doRelease = doRelease;
}
Example Usage
Example Usage
//////////////////////////////
// REQUIRE RELEVANT MODULES //
//////////////////////////////
var db = require("../libs/oracledb.js");
var oracledb = require('oracledb');
var sql = "";
///////////////////////////
// RETRIEVE CURRENT DATE //
///////////////////////////
module.exports.getCurDate = function(callback) {
sql = "SELECT CURRENT_DATE FROM DUAL";
db.doConnect(function(err, connection){
console.log("INFO: Database - Retrieving CURRENT_DATE FROM DUAL");
if (err) {
console.log("ERROR: Unable to get a connection ");
return callback(err);
} else {
db.doExecute(
connection, sql
, {} // PASS BIND PARAMS IN HERE - SEE ORACLEDB DOCS
, function(err, result) {
if (err) {
db.doRelease(connection); // RELEASE CONNECTION
return callback(err); // ERROR
} else {
db.doRelease(connection); // RELEASE CONNECTION
return callback(err, result.rows); // ALL IS GOOD
}
}
);
}
});
}
回答by Daniel Vukasovich
This error message is raised when the sessMax parameter supplied in OCISessionPoolCreate has been reached.
This error message is raised when the sessMax parameter supplied in OCISessionPoolCreate has been reached.
So, my first move would be verify if database sessions are being closed correctly.
So, my first move would be verify if database sessions are being closed correctly.
When this error message arises execute the following three actions:
When this error message arises execute the following three actions:
1.- (using sqlplus) show parameter sess
1.- (using sqlplus) show parameter sess
2.- (using sqlplus) select username,machine,program,count(*) from v$session group by username,machine ,program order by 4 ;
2.- (using sqlplus) select username,machine,program,count(*) from v$session group by username,machine ,program order by 4 ;
3.- verify in alert.log if there are any other ORA- messages during this event.
3.- verify in alert.log if there are any other ORA- messages during this event.
Did you perform this steps ? (share your results)
Did you perform this steps ? (share your results)
