[DOCS]: Parallel executes not supported within a single transaction

[Izhaki]

I assume the statement in the title is correct. If so, might be worth adding to the Transactions section of the README the following:

Note: execute cannot be called in parallel (Promise.all) within a transaction.

The transaction example provided in the README:

const results = await conn.transaction(async (tx) => {
  const whenBranch = await tx.execute(query1)
  const whenCounter = await tx.execute(query2)
  return [whenBranch, whenCounter]
})

However, if you try to fire execute in parallel:

const results = await conn.transaction(async (tx) => {
  await Promise.all(
    tx.execute(query1),
    tx.execute(query2)
  )
})

It breaks with:

target: dbname.-.primary: vttablet: rpc error: code = Aborted desc = transaction 1678232354685529562: in use: for query (CallerID: r05ouwpwzfqr7yd16bf2)

[mattrobenolt]

I don't know enough about JavaScript, but would we able to explicitly prevent this behavior and error? Or maybe execute() within a transaction isn't a Promise? I'm not sure if we have the ability to prevent concurrency here rather than relying on documentation.

[Izhaki]

Error seems to come from the DB, JavaScript might have little to do with it?

Do transactions support concurrency of queries? Does it work with other languages?

[mattrobenolt]

Correct, this fundamentally just isn't a thing MySQL can do and defeats a lot of properties of transactions. Transactions are meant to be linear.

This definitely errors on the database, and we shouldn't even attempt to allow it from the driver, is my point. No other language could do this, and this is super unique because this is only an issue with the HTTP interface. You wouldn't even be able to do this over a traditional mysql client that spoke the mysql protocol since the mysql protcol simply wouldn't allow it.

To be clear, parallelizing queries is entirely fine, just not within a transaction. A transaction implies order and other guarantees you break.

[mattrobenolt]

See also my comments in this thread: #58 (comment)

What you more want is a pipeline to send multiple queries in sequence, but in one HTTP request. We just don't support this yet on the API.

[Izhaki]

Cool cool. To be honest, the ideal resolution for people like me here is that this will be added to the docs. When I did load testing on PlanetScale, it could easily handle hundrands of concurrent requests per second. So I assumed this can be done with transactions as well.

Let me know if you want me to submit a PR updating the README for that.

Otherwise, this issue can be closed.

[mattrobenolt]

You can do concurrent transactions just fine, but the whole transaction, not individual queries within a transaction. This just breaks the model of what transactions guarantee. It's not really a PlanetScale thing, it's a mysql thing.