proposal: database/sql: add SetMaxBadConnRetries

[jinzhu]

This method adds the ability to sets the number of maximum retries if the driver returns driver.ErrBadConn

Currently, database/sql has a hardcoded maxBadConnRetries, which is set to 2, and it will rerun the SQL if the driver returns a bad connection error, which is causing problems in many cases.

For example, if we have a long-running INSERT SQL, the first try might be killed due to TCP timeout or whatever reason, and the next retry will insert duplicated data, which is confusing to end-users.

With this option, we can set the max bad retry to 0, and write our retry logic to avoid the issue. and it also opens the opportunity to do some optimizations like https://cacm.acm.org/magazines/2013/2/160173-the-tail-at-scale/fulltext

Related issues:

#48309
#15608

Implementation:

I have tried to submit a possible implementation: https://go-review.googlesource.com/c/go/+/404935

[gopherbot]

Change https://go.dev/cl/404935 mentions this issue: database/sql: add SetMaxBadConnRetries

[ianlancetaylor]

CC @kardianos

[kardianos]

To prevent a lock on max retries, I would prefer this to be set in OpenDB from the Connector. Maybe the connector could be have a configuration optional interface. Unsure. However, the associated CL is not correct, it doesn't lock correctly. It also changes the BadConn error I think.

Should we do this? Probably. I had ideally wanted to create a v2 sql package, but realistically my time is very limited. So if this was set when the DB is created so no lock is required, I'd be alright.

Another option would be 2 retries or no retires.

I'm okay with accepting this, conditionally that a good design can be found.

[rsc]

We can avoid the lock by setting this value atomically, I think.
So the API should be OK either way.

[rsc]

This proposal has been added to the active column of the proposals project
and will now be reviewed at the weekly proposal review meetings.
— rsc for the proposal review group

[jinzhu]

Thank you for your review, fixed the lock issue with atomic.

[MatthewJamesBoyle]

This seems a good proposal to me.

A couple of thoughts:

• Does it make sense to have an upper bound on the amount of retries we allow a user to configure?
• As far as I can see, the retry strategies today do not perform any exponential back off/jitter. This was likely fine for most use cases since the maximum was 2, but as users may put higher numbers here, we may want to do more to prevent a thundering herd problem.

[rsc]

The CL would be a lot clearer if the code was not being refactored at the same time. Can you instead add a db.maxBadConnRetries() method and replace all the i < maxBadConnRetries with i < db.maxBadConnRetries() ?

[jinzhu]

The CL would be a lot clearer if the code was not being refactored at the same time. Can you instead add a db.maxBadConnRetries() method and replace all the i < maxBadConnRetries with i < db.maxBadConnRetries() ?

Ok, will do that.

[jinzhu]

The CL would be a lot clearer if the code was not being refactored at the same time. Can you instead add a db.maxBadConnRetries() method and replace all the i < maxBadConnRetries with i < db.maxBadConnRetries() ?

but in the old style of code, we can't change the maxBadConnRetries to 0, or it will never reuse connection from pool, is it ok?

[jinzhu]

The CL would be a lot clearer if the code was not being refactored at the same time. Can you instead add a db.maxBadConnRetries() method and replace all the i < maxBadConnRetries with i < db.maxBadConnRetries() ?

Hello @rsc , which connReuseStrategy should we use if the maxBadConnRetries set to 0? alwaysNewConn or cachedOrNewConn?

[rsc]

@kardianos Do you have any suggestions or preferences here?