Node.js mysql connection and transactions

Question

Hi all,

I trying to change all my mysql inserts, update etc to transactions before going live with my app, I've had alot of trouble with connection already released before and to solve it I started using pool.query instead so that it handles the release itself.

    pool.query( 'INSERT INTO X SET ?', [ X ], ( error, results ) => {

            if(err){
                reject(err);
            }
            if ( error ) {
                reject( error );
            } else if ( results[ 0 ] !== 'undefined' ) {
                resolve( 'X added sucessfully' );
            } else {
                reject( error );
            }
                } );

I've tried to google it but cant find it anywhere, even in docs of Mysql, when you do a transaction, where in the code do I release the connection when done, or even better, can I use pool.query when doing a transaction?

the code below works but it sometimes give a connection already released error

      connection.beginTransaction( function ( err ) {
            if ( err ) {
                reject( err);
            }

        connection.query( 'INSERT INTO X SET ?', [ X ], ( error, results ) => {

            if(err){
                reject(err);
            }
            if ( error ) {
                reject( error );
            } else if ( results[ 0 ] !== 'undefined' ) {
                resolve( 'X added sucessfully' );
            } else {
                reject( error );
            }
            connection.commit( function ( commitErr ) {
                if ( commitErr ) {
                    connection.rollback( function () {
                        reject(commitErr);
                    } );
                }
          connection.release();
            } );
                } );
        } );

Siddharth Nayar · Accepted Answer · 2017-12-07 10:39:14Z

You need to acquire a connection from the connection pool before beginning the transaction and release it wherever you decide to rollback. You should also rollback and release the connection as soon as you face an error on your query/decide to roll back on any other condition. In your current code, you are rolling back only if you face an error while committing your transaction.

pool.getConnection(function(err, connection) {
    connection.beginTransaction(function(err) {
        if (err) { //Transaction Error (Rollback and release connection)
            connection.rollback(function() {
                connection.release();
                reject( err);
            });
        }
        connection.query('INSERT INTO X SET ?', [X], (error, results) {
            if (err) {       //Why is this here?
                reject(err);
            }
            if (error) { //Query Error (Rollback and release connection)
                connection.rollback(function() {
                    connection.release();
                    reject( err);
                });
            } else if (results[0] !== 'undefined') {
                resolve('X added sucessfully');
            } else {
                reject(error);
            }
            connection.commit(function(commitErr) {
                if (commitErr) {
                    connection.rollback(function() {
                        connection.release();
                        reject(commitErr);
                    });
                }
                connection.release();
            });
        });
    });
});

Collectives™ on Stack Overflow

Node.js mysql connection and transactions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related