r2dbc-mysql queueing execution of queries

[FutureGadget]

This is my suggested fix for issue #255 (comment).

Since in reactor environment, multiple threads can access to the same connection unintendedly, I added a queue per JasyncClientConnection.

[oshai]

Thanks for the PR!

Can you give some details on the suggested fix?
The test is a really good start.
Having a queue for queries (with scheduler) seems like not the optimal approach.
I am not sure how the driver expected to handle simultaneous queries on the same connection? multiplexing?
Jasync doesn't support multiplexing at the moment, but it seems transactions are not directly related to that.

[FutureGadget]

Thanks for the PR!

Can you give some details on the suggested fix? The test is a really good start. Having a queue for queries (with scheduler) seems like not the optimal approach. I am not sure how the driver expected to handle simultaneous queries on the same connection? multiplexing? Jasync doesn't support multiplexing at the moment, but it seems transactions are not directly related to that.

@oshai, thank you for taking a look on this PR.
What I tried to fix with this PR was an "unintended" conflict of query on transaction rollback.

What I mean by "unintended" is when you are in a transaction, cancelling the subscription of ongoing query publisher can cause an asynchronous ROLLBACK query to be executed using the same connection.

This behavior is from the following code. The transactional method uses Mono.usingWhen(...,asyncCancel), and the asyncCancel callback function parameter will make ROLLBACK happen on subscription cancellation.

Mono.usingWhen(Mono.just(it), 
    ignore -> mono,
    this.transactionManager::commit, 
    (res, err) -> Mono.empty(), 
    this.transactionManager::rollback
)

(https://github.com/spring-projects/spring-framework/blob/main/spring-tx/src/main/java/org/springframework/transaction/reactive/TransactionalOperatorImpl.java#L81)

It is "unintended" because a user cannot control the ROLLBACK query to be executed after the ongoing query is finished. As the jasync driver validates the connection before sending any query to the database, the above behavior will make jasync driver throw an exception since the ROLLBACK query cannot be executed simultaneously while the other query being executed using the same connection.

This is critical because when this type of error occurs, resource cleanup fails, which means the connection is not returned to the connection pool and the number of idle connections in the pool are only decreased.

In this PR, I added QueueingExecutionJasyncConnectionAdapter, which adapts MySQLConnection to use a ArrayBlockingQueue. The number of simultaneous queries should be at most 2 (the original first query and the following rollback query), so the size of the queue, which is 256, must be enough.

I didn't intend to introduce a behavior which is similar to query multiplexing with this PR.
Therefore, simultaneous queries using a single connection should be best avoided as it was before this PR.

[oshai]

I have few ideas/options to make it simple:

• on rollback, just close the connection (which I think will implicitly rollback the current transaction)
• use queryPromiseReference and chain the cancellation to it (if exists)

What do you think?

[FutureGadget]

I have few ideas/options to make it simple:

• on rollback, just close the connection (which I think will implicitly rollback the current transaction)
• use queryPromiseReference and chain the cancellation to it (if exists)

What do you think?

Thank you for better suggestions.
I've just tried the second one (chaining query promise) and it worked like a charm.
Please review : #345
Thank you.