Why This Matters
Go’s database/sql reuses connections from a pool.
But MySQL connections are stateful. A session may accumulate:
- open transactions
- modified sql_mode
- changed isolation levels
- temporary tables
- user variables (@var)
- prepared statements
If a connection is returned to the pool in a “dirty” state and later reused, that state leaks into the next request.
This can cause:
- transaction scope leaks
- inconsistent behavior between requests
- subtle locking issues
- hard-to-reproduce production bugs
MySQL provides the correct primitive for this: COM_RESET_CONNECTION.
It resets session state efficiently without closing the TCP connection.
Exactly what a connection pool should use.
Step 1 — Solving It in Production
In our system, correctness and isolation were non-negotiable.
So I implemented automatic session resetting myself.
database/sql provides the correct lifecycle hook: driver.SessionResetter. If implemented, ResetSession() is called automatically before reusing a connection. That is precisely where a reset should happen.
So I:
- Wrapped the MySQL driver
- Implemented driver.SessionResetter
- Performed a true session reset inside ResetSession()
The implementation can be seen here:
👉 https://github.com/France-ioi/AlgoreaBackend/commit/371ce4b72fe9afef5feb46c3e79abf60b890f310
This guarantees:
- no transaction leaks
- no session variable leakage
- no temporary table persistence
- no cross-request contamination
The Difficult Part
Because the driver does not expose COM_RESET_CONNECTION, implementing this required going below normal abstraction layers:
- wrapping the driver
- accessing unexported fields
- calling unexported methods
- using unsafe
- using go:linkname
Yes — real runtime-level hacks.
But architecturally, the design remained clean:
- reset occurs exactly at the correct lifecycle boundary
- no changes to database/sql
- no permanent driver fork
The hacks were purely to reach the protocol layer.
Step 2 — Attempting Upstream Support
After the production solution was working, I took the first step toward upstream support:
I opened a pull request to go-sql-driver/mysql implementing protocol-level support for COM_RESET_CONNECTION, without enabling it automatically — just exposing the primitive:
👉 https://github.com/go-sql-driver/mysql/pull/1734
The goal was simple:
- expose the reset command so applications and frameworks could use it safely
The PR did not move forward and was left without action.
This was expected, given the earlier discussion in the driver repository (https://github.com/go-sql-driver/mysql/issues/1273), where maintainers explicitly declined to add reset support due to concerns about performance and scope.
Why Not Just Close Connections?
Because closing connections means:
- TCP teardown
- handshake
- authentication
- higher latency
- reduced throughput under load
COM_RESET_CONNECTION is significantly cheaper and designed exactly for this purpose.
If you care about both performance and correctness, reset is the right primitive.
The Broader Insight
This experience highlighted an interesting architectural gap:
- database/sql assumes safe reuse.
- MySQL sessions are stateful.
- The driver does not enforce cleanup.
- Most applications rely on discipline.
But discipline is not isolation.
If your system:
- modifies session settings
- uses transactions heavily
- changes isolation levels dynamically
- depends on strict correctness
Then connection state must be explicitly managed.
Final Thought
Connection pooling is not just about reuse.
It is about safe reuse.
If your driver does not reset session state, you don’t have isolation — you have hope.
And hope is not a production strategy.
No comments:
Post a Comment