Open robinknaapen opened 4 years ago
@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.
@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.
@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).
@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 https://github.com/denisenkom/go-mssqldb/pull/418#issuecomment-529827752.
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.
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.
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
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