Handle transient errors

[robinknaapen]

Is your feature request related to a problem? Please describe.
Loadbalancing causes the database connection to shift to a transient state when using Azure SQL Database.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues#transient-errors-transient-faults

An occasional cause of transient errors is when the Azure system quickly shifts hardware resources to better load-balance various workloads.

This causes unhanded/unexpected errors when executing a query

Describe the solution you'd like
Auto reconnecting when a transient state is detected

Describe alternatives you've considered
Writing my own wrapper around the sql.DB interface, but this means I have to wrap every function on the interface

[yukiwongky]

@robinknaapen instead of using APIs from the sql.DB interface, where each Exec or Query will get a connection from the connection pool, you can get a stable connection sql.Conn first and then run any queries from there. That way you only need to retry when you first get sql.Conn. Once the connection is established, you don't need to retry when running the queries.

[robinknaapen]

@v-kaywon thanks for your response. If I were to use sql.Conn, wouldn't this mean that I'm ditching the whole pool provided by mssqldb in the first place? Also, this wouldn't fix my issue either way. If I open a new sql.Conn and execute a query, the transient state can still occur.

[yukiwongky]

@robinknaapen I apologize I misunderstood the question. I thought transient error only occurs at connection and once you get a stable connection you can do anything with it.

As for this feature request, we'll have to figure out how easy/difficult it is to detect a transient state, and also if this feature can be supported (from the documentation you linked, seems like other drivers do not reconnect when a transient error is retrieved).

[robinknaapen]

@v-kaywon

As for this feature request, we'll have to figure out how easy/difficult it is to detect a transient state

I think the detection shouldn't be hard, as seen here #418 (comment).

if this feature can be supported

The docs point to examples for PHP and .NET

seems like other drivers do not reconnect when a transient error is retrieved)

Other drivers like lib/pq do reconnect on network errors.

[mikewilliamson]

Handling transient errors requires care. If you're doing a read-only query you can just try again, but if you have tried to do an insert or update you won't know whether it succeeded or not before your connection was interrupted by the error. Correct error handling means checking whether the "failed" transaction was successful or not before retrying. I don't see how this could be done in the driver.