Using rollbacks in mysql pool

Question

Following this tutorial I have promisified the mysql library which allows me to use .then instead of the callback functions.

Here is my setup for the mysql pool:

   var mysql = require('mysql')
    var pool = mysql.createPool({
        connectionLimit: 10,
        host: 'localhost',
        user: 'matt',
        password: 'password',
        database: 'my_database'
    })
    pool.getConnection((err, connection) => {
        if (err) {
            if (err.code === 'PROTOCOL_CONNECTION_LOST') {
                console.error('Database connection was closed.')
            }
            if (err.code === 'ER_CON_COUNT_ERROR') {
                console.error('Database has too many connections.')
            }
            if (err.code === 'ECONNREFUSED') {
                console.error('Database connection was refused.')
            }
        }
        if (connection) connection.release()
        return
    })
    pool.query = util.promisify(pool.query)
    module.exports = pool

This setup has made my life so much easier until a point now where I do not know how to handle rollback transaction.

I am running queries like this:

    pool.query('InsertQuery').then(rows=>{
                return pool.query(`Select LocationID from Locations where LocationName = '${location[i]}'`)
            })
            .then(rows=>{
                locationID =rows[0].LocationID

                return pool.query(`Select StageID from Stages where StageName = '${stage[i]}'`)
            })
.then('anotherInsert')....
        .catch(err=>{
            console.log(err)
        })

The catch block at the end is working perfectly as the execution breaks whenever there is an error at any of the stage. But I want to be able to rollback transactions and not have a single query run if there are any issues with any of the queries. Is there a way to achieve this?

"connections are auto-released after each query" - That sounds like bad news for transactions. — Paul Spiegel, Jul 25 '19 at 07:00
Not sure if this helps: https://evertpot.com/executing-a-mysql-query-in-nodejs/ — Evert, Jul 30 '19 at 13:39

kaliatech · Answer 1 · 2019-07-30T13:31:33.647

Transaction boundaries usually happen at higher level than the query itself. The reason is use of transactions usually indicates a need to run more than one query so that they all commit (or share same isolation rules), or they all rollback. If running single queries (typically using autocommit by default), then there is no need for explicit rollback.

Given that, your question about how to revise your query method to rollback itself without passing it any higher context (like a txn) doesn't entirely make sense. You would normally start the transaction at some higher level, then call one or more queries, and then finally commit. Or if any error occurs with any of the queries, then rollback. So one option for you might be to simply throw the error in your query method, and handle the txn at a higher level.

Example psuedo code:

conn = getConnection
.then
  beginTxn
  runQuery1
  runQuery2
  commitTxn
.catch
  rollbackTxn
.finally
  releaseConn

Fwiw, there are libraries for wrapping mysql library functions with promises that might be of interest. Example: node-promise-mysql

score 0 · Answer 2 · edited Jul 26 '19 at 16:03

0

You have to begin a transaction on your connection, then commit it after all your work is done, or rollback in your catch block. See this question/answer for more details: Node.js mysql transaction

edited Jul 26 '19 at 16:03

Kebab Programmer

1,213
2
21
36

answered Jul 26 '19 at 14:51

Todd Price

2,650
1
18
26

Using rollbacks in mysql pool

2 Answers2