Transactions hanging in AWS Lambda

[samin]

Environment

AWS Lambda NodeJS 5.6 with AWS RDS
Knex version: 0.14.2
Database + version: MySQL 5.7.19
OS: Linux

Bug

Currently any query being made inside knex.transaction does not get executed, waiting to fail in timeout. In the example below, logs show WITH KNEX TRANSACTION but not KNEX TRANSACTION BEGIN. If knex.transaction is removed, code works as expected, writing into the application table. Am I missing something?

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    ssl: 'Amazon RDS',
    charset: 'utf8mb4'
  },
  pool: false,
  debug: true
});

exports.handler = (event, context, callback) => {
  console.log('WITH KNEX TRANSACTION');

  knex.transaction((trx) => {
    console.log('KNEX TRANSACTION BEGIN');
    return knex('application').transacting(trx).insert({
      name: 'Hello World', user_id: 4, client_id: 'abcknex', client_secret: 'truknex', created: '2017-01-01'
    })
      .then(trx.commit)
      .catch(trx.rollback);
  })
    .then((resp) => {
      console.log('Transaction complete.');
      callback(null, 'Some success message');
    })
    .catch((err) => {
      console.error(err);
    });
};

[samin]

More details from CloudWatch, with DEBUG=knex:* turned on:

[elhigu]

at least you are using trasactions wrong way. If you use implicit commit/rollback (return promise from transaction handler) you must not call trx.commit/trx.rollback manually.

Anyways those problems would not cause the functionality you just described it would occur later on. Where did you find out pool: false parameter?

[samin]

Seems like an option to disable pool: https://github.com/tgriesser/knex/blob/45f5ffbad023e30eeb39162b4b743473f0188052/src/client.js#L51

Anyways, updated the code with your observations and using pool instead, still no luck, begins transaction but does not write or commit.

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    ssl: 'Amazon RDS',
    charset: 'utf8mb4'
  },
  pool: { min: 0, max: 10 },
  debug: true
});

exports.handler = (event, context, callback) => {
  console.log('WITH KNEX TRANSACTION');

  knex.transaction((trx) => {
    console.log('KNEX TRANSACTION BEGIN');
    return knex('application').transacting(trx).insert({
      name: 'Hello World', user_id: 4, client_id: 'abcknex', client_secret: 'truknex', created: '2017-01-01'
    });
  })
    .then((resp) => {
      console.log('Transaction complete.');
      callback(null, 'Some success message');
    })
    .catch((err) => {
      console.error(err);
    });
};

[elhigu]

Looks like this is not the first time having this problem #2245

Have you read through this issue? #1875 Hopefully someone there knows if this is a common issue.

[okada]

There are two ways of using transactions:

1. using trx as "query builder" (return trx - commit/rollback is handled by knex)
2. using trx as "transaction object" (use transactiong(trx) in queries -app code must commit/rollback)
   THIS IS INCORRECT: commit/rollback is handled automatically by knex in both cases as long as the function returns a promise. See wubzz comments bellow.

Both code snipets above use the second form, but in the last snipet commit is not called and therefore no write to the database occurs.

The first snippet is correct according to the documentation, but in reality the knex.transaction do not return the result of the operation. Looking at the code, this makes sense - there could be multiple operations using transacting(trx) and it is not clear what should be returned.

What worked for me was wrapping the knex.transaction inside a promise that was settled after the transaction block. To obtain the result of the operation, i define variables on the scope above the transaction block that are set after the operation executes.

In this case this does not make much difference, since you are not using the result and just calling the callback. And I should point out that I did not run this code on aws lambda.

exports.handler = (event, context, callback) => {
  console.log('WITH KNEX TRANSACTION');
 
  return new Promise((resolve, reject) => {
    let result;
    knex.transaction((trx) => {
      console.log('KNEX TRANSACTION BEGIN');
      return knex('application').transacting(trx).insert({
        name: 'Hello World', user_id: 4, client_id: 'abcknex', client_secret: 'truknex', created: '2017-01-01'
      })
      .then((resp) => {
         trx.commit();
         result = resp;
       })
      .catch((err) => {
        trx.rollback();
        throw err;
       });
    })
    .then(() => {
      resolve(result);
      console.log('Transaction complete.');
      callback(null, 'Some success message');
    })
    .catch((err) => {
      reject(err)
      console.error(err);
    });
  }
};

[wubzz]

@okada I believe you are mistaken. There is absolutely no need to explicitly call commit / rollback even if using .transacting() instead of directly going via trx as queryBuilder.

knex.transaction((trx) => {
	return knex('table')
		.select()
		.transacting(trx);
})
	.then((rows) => {
	})
	.catch((error) => {
	});

knex:tx trx1: Starting top level transaction +0ms
knex:client acquired connection from pool: __knexUid2 +0ms
knex:query BEGIN; +0ms
knex:bindings undefined +0ms
trx started
knex:query select * from "table" +15ms
knex:bindings [] +15ms
knex:query COMMIT; +0ms
knex:bindings undefined +0ms
knex:tx trx1: releasing connection +62ms
knex:client releasing connection to pool: __knexUid2 +15ms

I'm willing to bet this is an error strictly in AWS Lambda, somehow.. What does DEBUG=knex:* log when running the above examples?

[okada]

@wubzz Yes, I you are right. I tested the example code from the docs and it works just fine. Thanks for correcting me. Will cleanup my code.

[JQuezada0]

@samin Did you ever figure out a way to make this work? I've encountered the same issue. Specifically in a Lambda environment the transaction emits BEGIN but never moves beyond that.

[samin]

@JQuezada0 not yet

[jussikari]

This is somewhat old conversation, but maybe this helps someone else. I too got stuck with lambda timing out on acquiring a connection, but only when using transactions. I had configured knex pool with only one connection (min=max=1) as recommended when used with Lambda. I looked up some knex transaction handling code, and started to suspect transactions might require a separate connection. I am not sure why it couldn't reuse the existing connection but all my troubles went away when I tried running my code with pool config min: 1, max: 2. Maybe give it a try. I think max: 1 is enough when you don't use transactions.