AWS Data api gives error when running queries concurrently in a transaction

[livingforjesus]

Describe the bug

When I'm running queries concurrently via Promise.all in a transaction, it fails. When I run the queries synchronously, however, it succeeds. This is super weird. The error given is: DatabaseErrorException: Transaction is still running a query. This error occurs randomly and on every other query(i.e if there are 5 queries in the promise.all, it could occur on queries 2 and 5)

Expected Behavior

I expect the queries to execute without errors reliably.

Current Behavior

It errors out with the error: DatabaseErrorException: Transaction is still running a query on random queries in the promise.all.

Reproduction Steps

  const params = {
    database: '**',
    resourceArn: '**',
    secretArn: '**'
  };
  const client = new RDSDataClient({
    region: 'us-east-1'
  });
  await client.send(
    new ExecuteStatementCommand({
      ...params,
      sql: `
CREATE TABLE IF NOT EXISTS "TheTable" (
    id text PRIMARY KEY NOT NULL,
    email text NOT NULL
);
    `
    })
  );

  const { transactionId } = await client.send(new BeginTransactionCommand(params));
  if (!transactionId) {
    throw new Error('No transactionId');
  }

  const queries = [];
  for (let i = 0; i < 50; i++) {
    queries.push(
      client.send(
        new ExecuteStatementCommand({
          ...params,
          sql: `
INSERT INTO "TheTable" ("id", "email")
VALUES ('${crypto.randomUUID()}', 'visiondaniels32+${Math.floor(Math.random() * 10000)}@gmail.com')
    `,
          transactionId
        })
      )
    );
  }
  await Promise.all(queries);

  await client.send(
    new CommitTransactionCommand({
      ...params,
      transactionId
    })
  );

Running this will give the error: Transaction is still running a query If it doesn't, run it a couple more times

Possible Solution

I think it might be possible that aws-data-api doesn't allow more than 1 query at the same time in a transaction. Maybe it has a lock somehow. I dont know any fix since the code is closed source

Additional Information/Context

No response

SDK version used

3.484.0

Environment details (OS name and version, etc.)

Mac OS M1 Max Sonoma 14.2.1, Node v20.11.1

[ryanleecode]

+1

[RanVaknin]

Hi @livingforjesus ,

Thanks for reaching out. The issue you are describing is likely a limitation with the RDS data API itself, and not the SDK. This seems to be a design choice with regards how the RDS data API processes transactions. I have reached out to RDS team internally to get some clarification and wording from them (ticket id #P127312449)

Will keep you posted.

Thanks, Ran~

[jakeleventhal]

This seems closer to a bug than a limitation in my opinion

[RanVaknin]

Hi there,

I have reached out to the RDS Data team internally and got this response:

As of now, we do not support running queries concurrently within a transaction.

The service team have created a feature request to support this in the future, but I don't have any ETA or insight of when it will get prioritized.

Since this is not actionable by the SDK team, I'm going to go ahead and close the issue. Thanks, Ran~

[SrushithR]

Hi! Is there any update on this issue? We have been using Aurora Serverless V1 with the Data API and are now migrating to V2, but we hit this issue.