database/sql: update documentation to explain context cancellation in more detail

[pjebs]

The documentation states in various places:

Drivers that do not support context cancelation will not return until after the query is completed.

The provided context is used until the transaction is committed or rolled back. If the context is canceled, the sql package will roll back the transaction. Tx.Commit will return an error if the context provided to BeginTx is canceled.

etc etc

[pjebs]

My suggestion is the documentation makes clear that some drivers will cancel the execution of the query whilst others will only cancel waiting for the query results but the execution will still complete behind the scenes.

This is a very important distinction.

[pjebs]

See: go-sql-driver/mysql#863 for more details.
@methane is one of the key contributors to the popular mysql driver.

[pjebs]

I found out this issue is more serious than a mere "documentation issue" forewarning the pitfalls.

See: go-sql-driver/mysql#863 (comment)

I have created an "empty" proposal: #28074 for auditing purposes.

[kardianos]

I'll look into more detail in the documentation.

[methane]

My suggestion is the documentation makes clear that some drivers will cancel the execution of the query whilst others will only cancel waiting for the query results but the execution will still complete behind the scenes.

This is a very important distinction.

Why is it very important?

Even though driver support cancelling query on the server, it only saves some CPU usage.

Canceling remote query is hard and nondeterministic problem. You shouldn't rely on behaviour of canceling.
In case of go-sql-driver/mysql#863, you should use transaction.

[kardianos]

There is already really clear documentation. At the top of the page of https://golang.org/pkg/database/sql/

Drivers that do not support context cancelation will not return until after the query is completed.

I think that is clear enough. Closing.

[pjebs]

Some drivers such as MySQL driver do support cancellation.

I was trying to make clear that even if the driver does support cancellation, the documentation should warn that successfully cancelling from Go's point of view does not imply that the query is cancelled from databases point of view.

In the case of MySQL, cancelling does not mean that MySQL has cancelled the execution of the query.