Requesting documentation on how to run transactions with MySQL connection pools.

[deanhuynh]

Describe the issue

It seems like with this wrapper transactions are run by executing a string of queries to BEGIN, execute, and COMMIT the transaction. However, there is no concept of getting an individual connection from the client that I can find. If we are running multiple queries synchronously and use a pool, how can we run transactions and ensure the queries are run on the same connection so as to not overlap with another transaction?

Also, it would be nice to have an example of the preferred way to extract the output data from a query, instead of just printing the object.

Links

https://github.com/aws/aws-advanced-nodejs-wrapper/blob/main/examples/aws_driver_example/aws_internal_connection_pooling_mysql_example.ts

[karenc-bq]

Hi @deanhuynh, just wanted to make sure I understood your use case correctly:

1. What plugins do you have enabled in the wrapper? More specifically are you using the Read/Write Splitting plugin?
2. Are you creating multiple clients and calling query on different clients in your workflow or are you reusing the same client?
3. Could you please clarify what do you mean by getting an individual connection from the client?

The Read/Write Splitting plugin's internal connection pool only switches connections when the application explicitly calls setReadOnly, otherwise it reuses the same physical connection. You can learn more about internal connection pool here.

Internal connection pools are meant to be used for Read/Write Splitting, is that your intended use case?

Thank you for the suggestion regarding the result output, we will improve the sample code to better retrieve the result set.

[deanhuynh]

Thanks for the reply @karenc-bq. I haven't integrated the wrapper yet and am trying to understand it better before doing so. My main concern at the moment is transactions with a connection pool. My understanding is that the connection pool allows us to run simultaneous operations by running them on multiple connections at the same time. Unless I am mistaken, this is how it works in mysql2. Also, transactions need to run on the same connection.

What plugins do you have enabled in the wrapper? More specifically are you using the Read/Write Splitting plugin?

I am looking to direct traffic between reader and writer instances, either through the read/write plugin or just having two different clients, whichever makes more sense.

Are you creating multiple clients and calling query on different clients in your workflow or are you reusing the same client?

Ideally I would use just one client, since I am assuming connection pools allow concurrent operations like with mysql2, using different connections at once. I don't understand what you mentioned about the connection pool always using the same physical connection: what is the point of the pool if we can only use one/two of them?

Could you please clarify what do you mean by getting an individual connection from the client?

I was operating under the assumption that the connection pool allows concurrent operations, therefore it would need to use multiple connections at once. Under a multiple connections model, I was curious how we can hold onto a specific connection if we want to run a multi-operation transaction on it.

[deanhuynh]

And just to clarify further, here is some pseudocode that illustrates the way we expect to use the transaction:

tx = db.startTransaction()
createResource(tx)
createAnotherResource(tx)
tx.commit()

[karenc-bq]

Hi, the main goal of a connection pool is to reduce the overhead of creating new connections by reusing established connections: https://sidorares.github.io/node-mysql2/docs#using-connection-pools. Using multiple connections to execute queries at once can be done without connection pools.

You can explicitly execute queries BEGIN or START TRANSACTION to start a transaction, but you don't have to. Autocommit is enabled by default, each query is an individual transaction. You can disable this this by client.setAutoCommit(false) if you'd like.

For your use case, we recommend starting with the following setup:

• create a single client with Read/Write Splitting enabled,
• have autocommit enabled,
• use setReadOnly to direct the traffic between the read and write transactions, and execute the queries synchronously

Does this address your concerns?

[deanhuynh]

@karenc-bq It's still unclear to me how to achieve what I explained above: executing multiple queries within the same transaction while using connection pools. We want to execute multiple queries as part of a transaction on a single connection so we can commit the specific operations we executed on that connection when done.

Hi, the main goal of a connection pool is to reduce the overhead of creating new connections by reusing established connections: https://sidorares.github.io/node-mysql2/docs#using-connection-pools. Using multiple connections to execute queries at once can be done without connection pools.

This reduction of overhead is exactly why we use connection pools in general. The pool is used to manage the multiple connections that we want to keep alive.

For your use case, we recommend starting with the following setup:
create a single client with Read/Write Splitting enabled,
have autocommit enabled,
use setReadOnly to direct the traffic between the read and write transactions, and execute the queries synchronously

I don't think this addresses our use case. This is basically the status quo without transactions. Each query will be executed in its own transaction like you mentioned above. Instead we are looking to execute a string of queries transactionally. For example, if we are creating a new resource and want an "all-or-nothing" execution in case of intermediate failure. Also, queries will be executed sequentially and not synchronously on a single connection as far as I understand. To clarify again, what we want is to execute multiple queries as part of a single transaction while many other connections in the pool are still executing queries in parallel. We are using this in a server to handle control plane operations and thus need to support concurrency.

[karenc-bq]

Hi @deanhuynh, thank you for providing further information on your use case. To further understand your setup, are you using any external connection pools or ORMs?

To clarify, as of right now, the wrapper offers two ways to connect.

The first, the wrapper clients (AwsMySQLClient or AwsPgClient) are themselves individual connections. They return a connection, not a connection pool.

The second, is an internal connection pool, as described earlier:

The Read/Write Splitting plugin's internal connection pool only switches connections when the application explicitly calls setReadOnly, otherwise it reuses the same physical connection. You can learn more about internal connection pool here.
This pool is only compatible with the Read/Write splitting plugin but does not create multiple writer connections.

If you are looking for a more general connection pool, the wrapper doesn't support it out of the box yet but it is in our backlog.