Timeout exception and cleaning up

[hoylen]

What is the best way to clean up after a TimeoutException is thrown when running an SQL query?

Problem

If you have code like this:

Transaction tx = ...;
try {
  await tx.prepared(sql1, params1);
  // Do something with the results that might raise an exception.
  await tx.prepared(sql2, params2); // what if this timeout?
  // Do something with the results that might raise an exception.
} finally {
  await tx.rollback();
}

The call to rollback throws a MySqlClientError saying: MySQL Client Error: Connection cannot process a request for QueryStreamHandler(rollback) while a request is already in progress for Instance of 'ExecuteQueryHandler'.

This is because the transaction/connection is still running the query when rollback is invoked. This can be demonstrated by adding a long delay (e.g. await new Future<void>.delayed(const Duration(seconds: 120));) before the rollback to allow the query to finish. But that is not a very practical solution.

Solution

I suspect sqljocky5 needs to deliberately terminate the running query, before it throws the timeout exception.

You don't want to just skip doing the rollback, because you want to rollback any changes made by query sql1 or if the application code had thrown an exception. You also don't want to simply discard or ignore the transaction/connection after getting a timeout: you want to reuse it when implementing a connection pool; or you want to cleanly close it so you don't get too many transactions/connections kept open. While calling exit to clean up is a solution for short running programs, for long running programs (e.g. Web servers) that is not a solution.

An interim workaround is to provide a larger timeout duration when connecting to the database, so query timeouts are avoided. But that is not always practical and has other consequences.

[tejainece]

I think this is a bug. I will fix it.

[BasedMusa]

@tejainece Mr.Publisher, I'm facing this issue right now, what do I do, my production server starts giving off this error random, but I think it's when we multiple devices try to access it.