intermittent NJS-032: connection cannot be released

Delete irrelevant parts of this template.

For general questions:

I am trying to write generic middleware (to be used in express or restify) that gets an oracledb connection for each request and exposes it on the request object. It also takes care of releasing the connection at the end of the request. If I invoke the middleware at a very high rate, releasing the connection sometimes fails with NJS-032: connection cannot be released because a database call is in progress

I've gone over the code a couple of times, but can't figure out why the release would happen too soon or if the session is somehow shared between multiple requests and one request is closing it while the other is using it.

You can reproduce with the code below and opening http://localhost:8080/test in your browser and keeping Cmd-R pressed down for continuous refreshing. Im also logging the connectionsInUse and connectionsOpen every second. Once the NJS-032 occurred I can see both values increment by one and stay there. The connection is clearly not released. Once I hit the threshold of 4 connections things start to break down as I hit the max pool size.

Full source code is available at https://github.com/wvanderdeijl/oracledb-middleware/tree/d516ffb0c8c77310fccf3962f7626f2eceda10ad

Example of the logging:

0 0
0 0
0 0
0 0
have to close connection
closing connction
closed
0 1
0 1
0 1
0 1
have to close connection
closing connction
closed
0 1
have to close connection
closing connction
oracledb Error: NJS-032: connection cannot be released because a database call is in progress
    at Error (native)
have to close connection
closing connction
oracledb Error: NJS-032: connection cannot be released because a database call is in progress
    at Error (native)
have to close connection
closing connction
closed
have to close connection
closing connction
closed
2 3
2 3
2 3
2 3

Source code

const oracledb = require('oracledb');

// see pool parameters at https://github.com/oracle/node-oracledb/blob/master/doc/api.md#createpool
module.exports = function (poolConfig) {
    poolConfig = poolConfig || {};
    // take any ORACLEDB_* environment variable and add it as a key to poolConfig
    Object.keys(process.env)
        .filter(key => key.startsWith('ORACLEDB_'))
        .forEach(key => {
            let val = process.env[key];
            val = !isNaN(parseInt(val, 10)) ? parseInt(val, 10) : val;
            val = val === 'true' ? true : val === 'false' ? false : val;
            poolConfig[key.substring('ORACLEDB_'.length)] = val;
        });
    // create database connection pool (promise)
    const pool = oracledb.createPool(poolConfig);
    // return middleware function
    return function (req, res, next) {
        // get connection from pool and put promise on request (as request.connection)
        const conn = req.connection = pool.then(pool => pool.getConnection())
        // release connection at end of request
        const close = () => {
            console.log('have to close connection');
            conn
                .then(c => {
                    console.log('closing connction');
                    return c.close();
                })
                .then(() => console.log('closed'))
                .catch(err => console.error('oracledb', err))
        }
        res.once('finish', close);
        res.once('close', close);
        next();
        // next();
    }
}

and

const oracledb = require('oracledb');

// see pool parameters at https://github.com/oracle/node-oracledb/blob/master/doc/api.md#createpool
module.exports = function (poolConfig) {
    poolConfig = poolConfig || {};
    // take any ORACLEDB_* environment variable and add it as a key to poolConfig
    Object.keys(process.env)
        .filter(key => key.startsWith('ORACLEDB_'))
        .forEach(key => {
            let val = process.env[key];
            val = !isNaN(parseInt(val, 10)) ? parseInt(val, 10) : val;
            val = val === 'true' ? true : val === 'false' ? false : val;
            poolConfig[key.substring('ORACLEDB_'.length)] = val;
        });
    // create database connection pool (promise)
    const pool = oracledb.createPool(poolConfig);
    // return middleware function
    return function (req, res, next) {
        // get connection from pool and put promise on request (as request.connection)
        const conn = req.connection = pool.then(pool => pool.getConnection())
        // release connection at end of request
        const close = () => {
            console.log('have to close connection');
            conn
                .then(c => {
                    console.log('closing connction');
                    return c.close();
                })
                .then(() => console.log('closed'))
                .catch(err => console.error('oracledb', err))
        }
        res.once('finish', close);
        res.once('close', close);
        next();
    }
}

What is your version of Node.js? 6.10.0
What version of node-oracledb are you using? 1.13.0
What is the version of your Oracle client (e.g. Instant Client)? instantclient 12_2 for MacOSX zip files
What is the version of Oracle Database? XE 11g
What is your OS and version? MacOS 10.12.4
What environment variables did you set?
export ORACLEDB_user=system export ORACLEDB_password=oracle export ORACLEDB_connectString=localhost/xe export OCI_INC_DIR=/Users/wilfred/git/outputstop-api/instantclient/sdk/include export OCI_LIB_DIR=/Users/wilfred/git/outputstop-api/instantclient
What exact command caused the problem (e.g. what command did you try to install with)? node test-api.js

oracle / node-oracledb

intermittent NJS-032: connection cannot be released #670

For general questions: