AWS Lambda Function Synchronization Issues

Question

I have an AWS lambda function whose job it is, is to listen for an incoming SNS event, retrieve a RDS secret from Secrets Manager, and perform an sql transaction with 3 sql queries using the mssql library on npm.

let response;

const mssql = require("mssql");
const AWS = require("aws-sdk");
const uuidv4 = require("uuid/v4");

exports.handler = async (event, context) => {

    //console.log(JSON.stringify(event));
    var secret;
    var snsMessage = JSON.parse(event.Records[0].Sns.Message);
    console.log("getting secret");
    try {
        var data = await new AWS.SecretsManager().getSecretValue({ SecretId: process.env.DATABASE }).promise();
            console.log("got data");
        if ('SecretString' in data) {
            secret = data.SecretString;
            secret = JSON.parse(secret);
        }
        else {
            let buff = new Buffer(data.SecretBinary, 'base64');
            secret = buff.toString('ascii');
        }

        const config = {
            user: secret.username,
            password: secret.password,
            server: secret.host,
            database: "xxxxx",
            options: {
                encrypt: true
            }
        }
            console.log("creating config");
        try {
            //let pool = await mssql.ConnectionPool(config);
            let pool = await new mssql.ConnectionPool(config).connect();
            console.log("create connection pool");
            const transaction = await new mssql.Transaction(pool);
            console.log("create transaction");
            transaction.begin(async err => {
                if (err) console.log("Error" + err);
                console.log("getting request object");
                var request = await new mssql.Request(transaction);
                console.log("have request object");
                try {
                    console.log("Starting request 1");
                    await request
                        .input('owner', mssql.UniqueIdentifier, snsMessage.sub)
                        .input('email', mssql.VarChar(256), snsMessage.email)
                        .input('phone', mssql.VarChar(256), snsMessage.phone_number)
                        .input('address', mssql.VarChar(256), snsMessage.address)
                        .query(`insert into Users 
                                (PK_UUID, EMAIL, PHONE, FIRST_NAME, LAST_NAME, STREET_ADDRESS, CITY, STATE, STATUS, DATE_CREATED)
                                    VALUES
                                (@owner, @email, @phone, 'Test', 'Test', @address, 'xxx','xx','BASIC', Current_Timestamp)`)
                    console.log("Request 1 finished");
                    const newCompanyID = uuidv4();

                    console.log("Starting request 2");
                    await request
                        .input('owner', mssql.UniqueIdentifier, snsMessage.sub)
                        .input('companyID', mssql.UniqueIdentifier, newCompanyID)
                        .query(`insert into Company
                                (PK_UUID, STATUS, DATE_CREATED, OWNER)
                                    VALUES
                                (@companyID, 'INCOMPLETE', CURRENT_TIMESTAMP, @owner)`)

                    console.log("request 2 finished");
                    console.log("starting request 3");
                    await request
                        .input('owner', mssql.UniqueIdentifier, snsMessage.sub)
                        .input('companyID', mssql.UniqueIdentifier, newCompanyID)
                        .query(`insert into User_Roles
                                (PK_UUID, USERS, ROLES, COMPANIES, DATE_CREATED)
                                    VALUES
                                (newid(), @owner,'Company_Owner',@companyID, CURRENT_TIMESTAMP)`)

                    console.log("Request 3 finished");
                    console.log("Committing transactions");
                    await transaction.commit(err => {
                        if (err) console.log(err);
                        console.log("transactions committed");
                        response = {
                            'statusCode': 200,
                            'body': JSON.stringify({
                                message: 'hello world',

                            })
                        }
                        //return err;


                        return response
                    })
                }
                catch (err) {
                    console.log("error caught 1");
                    console.log(err);
                    await transaction.rollback(err => {
                        if (err) console.log(err);

                    })
                }

            })
        }
        catch (err) {
            console.log("error caught 2");
            if (err) console.log(err);
        }
    }
    catch (err) {
        console.log("error caught 3");
        console.log(err);
    }
console.log("Down Here")
};

1 out of 10 times it correctly commits all 3 queries to the database. Every other time, the console.log messages are out of order, and sometimes the lambda function terminates while the transaction connection is open, leaving the connection blocked. It is definitely a synchronization issue but cannot tell if it is coming from AWS Lambda, the mssql library, or from my own code.

Answer 1

Ok so after hours of headbanging I figured it out. The answer that finally led me down the right path can be found here

In case anyone else is struggling with this situation using mssql transactions inside of async/await functions see below for a generic reference. Basically all your queries are wrapped in a try/catch block. If any one of those queries fail, you can then trigger your rollback.

const mssql = require('mssql')
const config = {
            user: "username",
            password: "password",
            server: "server",
            database: "database-name",
            options: {
                encrypt: true
            }

 let pool = await new mssql.ConnectionPool(config).connect();
 transaction = await new mssql.Transaction(pool);
 await new Promise(resolve => transaction.begin(resolve)); //This is the fix

 try{
   let request1 = new mssql.Request(transaction);
   let result1 = await request1.query('some query');

   let request2 = new mssql.Request(transaction);
   let result2 = await request2.query('some query');

   let request3 = new mssql.Request(transaction);
   let result3 = await request3.query('some query');

   await new Promise(resolve => transaction.commit(resolve));
 }
 catch(err)
 {
   await new Promise(resolve => transaction.rollback(resolve));
 }