Why ThreadLocal Cannot Isolate Database Connections in a Connection Pool

Practice Is the Sole Test of Truth

To ensure that a connection obtained from a pool is used by only one thread at a time, the author sets up a Druid pool with a maximum of one connection and runs multiple threads that share the same DataSource.

Each thread obtains a connection via datasource.getConnection() without calling connection.close() . The result shows that only the first thread can execute successfully; subsequent threads fail to acquire a connection, confirming that the pool enforces exclusive use per thread task.

Stepping Back

If a flawed pool allowed multiple threads to share the same connection, the author questions whether ThreadLocal could truly prevent interference.

// Validation idea from: https://blog.csdn.net/sunbo94/article/details/79409298
// Connection set autoCommit=false
private static final ThreadLocal<Connection> connectionThreadLocal = new ThreadLocal<>();

private static class InnerRunner implements Runnable {
    @Override
    public void run() {
        // ... omitted code ...
        String insertSql = "insert into user(id,name) value(" + RunnerIndex + "," + RunnerIndex + ")";
        statement = connectionThreadLocal.get().createStatement();
        statement.executeUpdate(insertSql);
        System.out.println(RunnerIndex + " is running");
        if (RunnerIndex == 3) {
            Thread.sleep(100);
            connectionThreadLocal.get().rollback();
            System.out.println("3 rollback");
        } else {
            connectionThreadLocal.get().commit();
            System.out.println(RunnerIndex + " commit");
        }
    }
}

The test shows that when thread 3 rolls back after its INSERT statement, a later COMMIT from another thread can cause the rolled‑back data to be persisted, demonstrating that ThreadLocal does not isolate the connection.

Why ThreadLocal Fails for the Same Connection

A Connection represents a session between the application server and the database; a Statement executes SQL within that session. Transactions guarantee atomicity, and when auto‑commit is on each statement forms its own transaction.

Storing a Connection in a ThreadLocal merely keeps a reference to the same object; it does not create a separate physical connection, so concurrent threads still contend for the same resource.

How to Achieve True Isolation with ThreadLocal

To use ThreadLocal for isolation, each thread must create its own Connection and store that distinct instance in the ThreadLocal, ensuring that the thread only ever uses its own connection.

How Druid and ZDAL Manage Connections

Druid maintains an array of connections, a counter poolingCount , and uses a lock to decrement the count when a thread acquires a connection. When the count reaches zero, acquisition fails; when a connection is closed it is returned to the array and the count is incremented.

Key internal fields include a volatile maxUsedConnections for visibility across threads and an AQS‑based Semaphore to limit concurrent access.

public class InternalManagedConnectionPool {
    private final int maxSize;
    private final ArrayList connectionListeners;
    private final InternalSemaphore permits;
    private volatile int maxUsedConnections = 0;
    // constructor and getConnection() simplified
}

The getConnection() method tries to acquire a permit, locks the pool list, removes a connection if available, updates maxUsedConnections , and returns the listener; otherwise it creates a new connection or throws an exception when the pool is exhausted.

ThreadLocal’s Role in ZDAL

In ZDAL, after parsing SQL to determine routing (database, table, etc.), the resulting RouteCondition is stored in a ThreadLocal map so that subsequent operations can reuse the routing information without re‑parsing.

public Object parse(...) {
    SimpleCondition simpleCondition = new SimpleCondition();
    simpleCondition.setVirtualTableName("user");
    simpleCondition.put("age", 10);
    ThreadLocalMap.put(ThreadLocalString.ROUTE_CONDITION, simpleCondition);
}

public void laterOperation() {
    RouteCondition rc = (RouteCondition) ThreadLocalMap.get(ThreadLocalString.ROUTE_CONDITION);
    if (rc != null) {
        metaData = sqlDispatcher.getDBAndTables(rc);
    } else {
        // fallback parsing
    }
}

The author concludes that while ThreadLocal is useful for passing context, it cannot magically isolate a shared Connection; proper isolation requires distinct connections per thread.