cockroach-go: transaction requires four round trips to manage txn state

[nvanbenschoten]

We currently suggest that all Go clients use our cockroach-go library when running transactional workloads. We also use this library ourself in tests and in benchmark load generators. This is a problem because the library has never been tuned for efficiency. The most obvious example of this is that the library performs four extra client<->gateway round trips just to manage transaction state. These roundtrips are to run the statements:

• BEGIN
• SAVEPOINT cockroach_restart
• RELEASE SAVEPOINT cockroach_restart
• COMMIT

There are two main problems with this:

1. it increases transactional latency
2. it increases transactional CPU footprint

We should explore methods to reduce the amount of work a transaction needs to perform in order to maintain state. For instance, we could send the first two statements together like BEGIN; SAVEPOINT cockroach_restart and the last two statements together like RELEASE SAVEPOINT cockroach_restart; COMMIT (either manually or through a driver batching API).

We could also take this a step further by taking hints from out client.Txn package. The first two statements could be lazily prepended to the first client-supplied statement. Further, we could provide an ExecAndCommit method that's analagous to our CommitInBatch method which would send that last client statement with the final two txn management statements. Combined, these two changes would even open up the opportunity for us to detect 1PC-able transactions that could have their BEGIN and COMMIT statements stripped and run in an implicit-transaction.

To accomplish all of these goals we'll need to be a little more aggresive about our interaction with the stdlib's database/sql package and may need to make the package more opinionated. I don't think this is a problem, especially if we offer it as an alternative to the existing API.

cc. @andreimatei @cockroachdb/sql-wiring

Jira issue: CRDB-4927