How to use single transaction with multiple queries, business login in-between

[oddanderson]

I want to have each API call generate a single transaction, and if any portion of the API fails I want to rollback the transaction. This way I do not end up with partial data being created. The examples show you can do this if you immediately call .query(), but there are many cases where I want to encapsulate behavior into separate functions which prevents me from calling .query() immediately.

Here is an example

async func doALot() {
   let transaction = mysql.transaction();
   for (obj of someArray) {
       transaction.query("Do an insert", values)
       .query((r) => {
          if (condition) {
                return ["do an update", more values)
          }
       })
   }
   // for loop done, time for more queries
   let results = await transaction.query("SELECT combined data inserted/updated in prior functions").commit()
}

In this example, the for loop doing the insertions is successful, and multiple independent queries work. However, the results array returned in the final query() is the result of the very first insertion called in the for loop (rather than the final SELECT), and it seems like I am no longer able to read data from the DB using the transaction object.

[oddanderson]

I think part of the problem is that the queries do not execute until the transaction is committed. If you want to make an external API call in the middle of a transaction, this is problematic.

My particular example is that I am processing SQS messages, and generating push notifications. It's possible for sending a push notification to fail, in which case the SQS message fails and gets retried. AWS makes no certainties about how often SQS messages may get processed, which means you need to handle de-dupping. The easiest way to do this is to have a db row for every SQS message id, lock the row, send the push notification, update the row to no longer be allowed to be processed, then unlock the row.