Concurrent Requests - Why's the database connection crashing the Node process

Question

Overview

I'm developing an MVC application with NodeJS. When the application loads for the first time, the database object (using a pool) is created.

 
var pool = mysql.createPool({connectionLimit: 150, host: __host,
               user: __user, password: __password,
               database: __database})
module.exports = pool
 

When a request is received, a Controller object is created, which creates a Model to perform actions. The model gets a connection from the pool, performs the action, and releases the connection back to the pool.

 
//router snippet
router.get('/post_data', function(req, res){
    router.setRequestAndResponse(req, res)
    var post_data = new Post_Data()
    post_data.processDataFromGet(router)
})

//controller code snippet
Post_Data_Controller.prototype.processDataFromGet = function(router){
    var controller_obj = this
    var data_array = {}
    var req = router.req, res = router.res
    //retrieving data from request and passing to the data_array
    controller_obj.model.create(data_array, function(result){
        var xml = xmlbuilder.create("response")
        if (result.code == "error"){
            xml.e("code", "error")
            xml.e("message", result.error_message)
        }else if (result.code == "success"){
            xml.e("code", "success")
        }
        controller_obj.sendResponse(router.res, xml, "xml")
    })
}

Post_Data_Controller.prototype.sendResponse = function(res, response, type){
    if (type == "json"){
        res.set({"Content-Type": "application/json", "Content-Length": JSON.stringify(response).length})
        res.send(response)
    }else{ /* Default type is XML */
        res.set({"Content-Type": "application/xml", "Content-Length": response.end({pretty: true}).length})
        res.send(response.end({pretty: true}))
    }
}

//Model snippet
Post_Data.prototype.create = function(data_array, callback){
    /* data validation */
    var fail = false, error_data = {}

    if (fail) {callback({code: "fail", cause: error_data}); return;}
//the next 2 lines do not throw an error when uncommented
    //callback({code: "fail", cause: "how's it going"});
    //return;
    __db_pool.getConnection(function(err, db_conn){
// the next two lines throw an error for two or more requests coming in at the same time
        callback({code: "fail", cause: "how's it going"});
        return;
        if (err) { callback({code: "error", error_message: err}); return;}
        callback({code: "fail", cause: "how's it going"});
        return;
          db_conn.query("sql command", [data_array],
            function(err, result){
                if (err){ callback({code: "error", error_message: err}); return;}
                if (result && result.length > 0){ //affiliate and listing exist
                    data_array.listing_id = result[0].listings_id
                    var data = [data_to_insert]
                    db_conn.query("sql command here", data,
                        function(err, result){
                            db_conn.release()
                            if (err){ callback({code: "error", error_message: err}); return;}
                            if (result && result.affectedRows > 0) {
                                callback({code: "success", data: {data_to_be_returned}})
                            }else {callback({code: "error", error_message:"Error inserting data"}); return}
                        })
                }else{
                    callback({code: "fail", cause: "error to send back"})}
            })
    })
}
 

Problem

These requests are web service requests. If I send one GET request, no error happens; however, when I send two or more concurrent requests, I receive this error:

 
/project_path/node_modules/mysql/lib/protocol/Parser.js:82
        throw err;
              ^
Error: Can't set headers after they are sent.
    at ServerResponse.OutgoingMessage.setHeader (http.js:689:11)
    at ServerResponse.res.set.res.header (/project_path/node_modules/express/lib/response.js:549:10)
 

I traced the culprit to the specific line in the Model code pasted above. It seems that for some reason, once the model obtains a connection from the pool for the second request, it somehow interferes with the first request. Both requests still insert the proper data into the database; however, the second and subsequent requests can't send a response without throwing an error anymore.

I have performed the requests with GET, POST, and PUT content-types; only GET throws the error. All the other content-types don't throw any error, even with over one thousand concurrent requests.

Here's the web service code for the GET requests; it's the same for the other content-types except for the content-type changes and the data being put in the body.

 
for(var i=0; i less than 5; i++){
    sendAsGet()
    i++
}

function sendAsGet(){
    try{
        var data = "?data_to_be_sent"
        var uri =url.parse("http://localhost:4000/post_data")
        var options = {hostname: uri.hostname, port: uri.port, method: "GET",
            path: uri.path + data, agent: false}
        request = (uri.protocol == "https")? https : http
        var req = request.request(options, function(res){
            var result = ""
            console.log("STATUS: " + res.statusCode)
            console.log("HEADERS: " + JSON.stringify(res.headers))
            res.setEncoding("utf8")
            res.setTimeout(50, null)
            res.on("data", function(chunk){
                result += chunk
            })
            res.on("end", function(){
                console.log(result)
            })
        })
        req.end()
    }catch(err){
        console.log(err.message)
    }
}
 

I would like to know 2 things:

• Why is getting the database connection causing this problem?
• Why does it happen only on GET requests and not on POST and PUT?

Google and previous SO questions haven't been able to help so far.
Thanks.

Answer 1

The reason you are seeing the error is because you're placing request/response instances on the router itself. Don't do that. The router object is a "static" object, it's not a per-request thing. So currently this is what's happening (in order):

1. Request #1 comes in and sets req/res on router and starts the asynchronous model.create().

2. Meanwhile, request #2 comes in and overwrites req/res on router and starts its own asynchronous model.create().

3. Request #1's model.create() callback is called, sending the response to request #2's socket instead.

4. Request #2's model.create() callbacks is called, where it attempts to send a response to the same res that was just responded to just a moment ago. Trying to write headers to a response that has already been sent then results in the error you are seeing.