🎯 Prevent Connection Pollution by Closing/Resetting Connection on Context Cancellation During `BeginTx`

[raimeecas]

📝 Description

A critical concurrency issue exists in the driver's transaction initialization flow. When database/sql invokes BeginTx() with a context, and that context is canceled or times out while the START TRANSACTION command is in-flight or being processed by the MySQL server, the connection can be returned to the database/sql connection pool in an ambiguous state.

Because the server may have successfully received and executed the START TRANSACTION command before the driver aborted the operation due to context cancellation, the physical TCP connection remains associated with an active, uncommitted transaction on the MySQL server. When the connection is returned to the pool, a subsequent query or transaction block borrowing this connection will unknowingly inherit this open transaction. This leads to severe side effects, including:

• Unexpected commits or rollbacks of unrelated queries.
• Persistent row/table locks that block other database operations.
• Data contamination across unrelated application requests.

This issue is highly intermittent and typically manifests under high network latency, packet loss, or database CPU spikes where the round-trip time for the START TRANSACTION command exceeds the context deadline.

🎯 Acceptance Criteria

• If BeginTx fails due to context cancellation (ctx.Err()), the driver must guarantee that the connection is not returned to the pool in a dirty state.
• If the transaction state of the connection is ambiguous after a cancellation, the driver must discard the connection by returning driver.ErrBadConn or explicitly closing the underlying network connection.
• The fix must not introduce connection leaks; closed connections must be cleaned up properly.
• The solution must handle edge cases where the context is canceled exactly as the server response arrives.

🛠️ Technical Specifications & Context

• Relevant Files: Look for the implementation of driver.ConnBeginTx (typically in connection.go, transaction.go, or connector.go depending on the repository structure).
• Mechanism:
  • When executing BeginTx, the driver sends the query START TRANSACTION (or its equivalent binary command).
  • If the context is canceled during this network roundtrip, the driver's context watcher will trigger.
  • If the driver simply returns the context error (e.g., context.Canceled), database/sql assumes the connection is still healthy and returns it to the free pool.
  • To prevent this, if a write/read operation for START TRANSACTION is interrupted or fails due to context cancellation, the driver should mark the connection as invalid. Returning driver.ErrBadConn is the standard Go database/sql mechanism to signal to the pool that the connection should be discarded and destroyed.
  • Alternatively, ensure that the connection's internal state tracker marks the connection as "dirty" or "closed" so subsequent Prepare, Query, or Exec calls fail or force a reset.

🧪 Verification & Testing

To verify the fix, implement an integration test that simulates network delay during transaction start:

1. Setup a Mock/Delayed Connection: Use a proxy or a mock MySQL server that introduces a delay (e.g., 100ms) when responding to START TRANSACTION.
2. Trigger Cancellation: Call db.BeginTx with a context timeout set to a value lower than the delay (e.g., 50ms).
3. Verify Error: Assert that BeginTx returns a context deadline exceeded error.
4. Verify Pool State:
   • Acquire a new connection from the pool.
   • Execute a query to check the transaction status (e.g., check @@in_transaction or attempt to execute a query and verify it is not inside an active transaction block).
   • Assert that the connection returned is clean and does not have an active transaction inherited from the previous failed attempt.

---

---

This repo is using Opire - what does it mean? 👇

💵 Everyone can add rewards for this issue commenting /reward 100 (replace 100 with the amount).
🕵️‍♂️ If someone starts working on this issue to earn the rewards, they can comment /try to let everyone know!
🙌 And when they open the PR, they can comment /claim #1 either in the PR description or in a PR's comment.

🪙 Also, everyone can tip any user commenting /tip 20 @raimeecas (replace 20 with the amount, and @raimeecas with the user to tip).

📖 If you want to learn more, check out our documentation.

[raimeecas]

/reward 10

[opirebot]

😅 Ups... you cannot create a reward of $10.00. It needs to be at least $20.00

[freeEasyLoope]

Suggested fix — 2026-07-27 22:10

func (mc *mysqlConn) BeginTx(ctx context.Context, opts driver.TxOptions) (driver.Tx, error) {
    err := mc.exec(ctx, "START TRANSACTION")
    if err != nil {
        if errors.Is(err, context.Canceled) || errors.Is(err, context.DeadlineExceeded) {
            mc.Close() // 强制关闭连接，驱动池会移除它

_{Auto-generated, review before merge}

[wasim-builds]

/opire try

[MercornKing]

Proposed Fix: Immediate Connection Closure on Context Cancellation

Hi! I analyzed this connection pollution issue.

Fix Implementation

When a context is canceled during BeginTx, returning the connection to the pool leaves an active transaction hanging. The safest fix is to immediately close the connection:

select {
case <-ctx.Done():
    // Canceled during the roundtrip, connection is now in an undefined state.
    // We MUST close it to prevent pollution.
    mc.Close()
    return nil, ctx.Err()
default:
}

I can open a PR to properly patch this in the BeginTx lifecycle!