transaction rollback problems

There is an unwritten rule that you can not run several queries simultaneously within a transaction. If this rule is violated, then you either get an error like:

vttablet: rpc error: code = Aborted desc = transaction 1762205153626519114: in use: for query

or the request simply runs on a new connection, happily executing outside of a transaction.

The problem

This rule can not be truly followed by applications if they happen to throw an exception from within the transaction block, while a query is running. Minimal example:

await db.transaction(async (tx) => {
    const query = tx.execute("update junk set i=i+1 limit 100000"); // begin a long query

    // do something else, that might fail
    await new Promise((resolve, reject) => setTimeout(reject,100));

    // wait for the query to complete
    await query;
})

If not all queries within a transaction are awaited, then the final (hidden) COMMIT/ROLLBACK will likely execute on an unrelated connection, leaving the transaction running until it times out. With the previous example, after it runs, I can see the following;

mysql> select trx_id, trx_state, trx_query from information_schema.innodb_trx ;
+---------+-----------+-----------+
| trx_id  | trx_state | trx_query |
+---------+-----------+-----------+
| 2182518 | RUNNING   | NULL      |
+---------+-----------+-----------+
1 row in set (0,04 sec)

..and attempting to modify the junk table gets stuck waiting for the transaction to expire.

Proposed solution

The Connection class should maintain a queue of queries (as mentioned in #139), only allowing a new query to run after the previous one had completed. Otherwise the caller is at risk of starting queries on a different connection, which will usually lead to problems, including data corruption. This change is possible to implement within the current js driver.

Ideally, the queue would be maintained on the server side, this would allow callers to submit several queries in sequence, without having to wait for a response each time. For example, each query could include a sequence number, and the server would delay its execution until previous queries are received and completed. I'm not sure if it's even feasible, consider this a feature request:)

Start with the Connection class and read the related discussion in issue #139. Determine how query execution currently handles concurrent requests and transaction cleanup. Done means implementing the proposed query queue so a new query waits for the previous one to complete, while preserving transaction behavior.