Why a Misused Transaction Propagation Caused Connection Exhaustion and How to Fix It

In a test‑case execution framework, each test case is assembled into a multithreaded task, parameters are processed concurrently (including a distributed lock for user login status), and the results are written asynchronously to a database.

Scenario

The developer runs a suite of test cases that involve fetching user credentials, caching them in a ConcurrentHashMap<Integer, String>, and performing several database reads and writes within a Spring transaction.

Bug Code

@Override
@Transactional(isolation = Isolation.REPEATABLE_READ, propagation = Propagation.REQUIRES_NEW)
public String getCertificate(int id, ConcurrentHashMap<Integer, String> map) {
    if (map.contains(id)) return map.get(id);
    Object o = UserLock.get(id);
    synchronized (o) {
        if (map.contains(id)) return map.get(id);
        logger.warn("非缓存读取用户数据{}", id);
        TestUserCheckBean user = testUserMapper.findUser(id);
        if (user == null) UserStatusException.fail("用户不存在,ID:" + id);
        String create_time = user.getCreate_time();
        long create = Time.getTimestamp(create_time);
        long now = Time.getTimeStamp();
        if (now - create < OkayConstant.CERTIFICATE_TIMEOUT && user.getStatus() == UserState.OK.getCode()) {
            map.put(id, user.getCertificate());
            return user.getCertificate();
        }
        boolean b = UserUtil.checkUserLoginStatus(user);
        if (!b) {
            updateUserStatus(user);
            if (user.getStatus() != UserState.OK.getCode()) UserStatusException.fail("用户不可用,ID:" + id);
        } else {
            testUserMapper.updateUserStatus(user);
        }
        map.put(id, user.getCertificate());
        return user.getCertificate();
    }
}

Bug Analysis

Incorrect key‑checking method

The code uses map.contains(id) to test whether the cache already holds the user’s certificate. map.contains actually checks for a value , not a key, so the condition always returns false unless the exact certificate string happens to equal the integer id. The correct method is map.containsKey(id). This mistake forces the method to query the database on every call, generating unnecessary load.

/**
 * Legacy method testing if some key maps into the specified value
 * in this table.  This method is identical in functionality to
 * {@link #containsValue(Object)}, and exists solely to ensure
 * full compatibility with class {@link java.util.Hashtable},
 * which supported this method prior to introduction of the
 * Java Collections framework.
 */
public boolean contains(Object value) {
    return containsValue(value);
}

Transaction propagation misuse

The method is annotated with propagation = Propagation.REQUIRES_NEW. According to Spring documentation, REQUIRES_NEW creates a brand‑new transaction and suspends any existing one. Each invocation therefore opens a new JDBC connection that remains tied to the suspended transaction until the method completes. When many test cases run in parallel, dozens of connections are opened and never released, eventually exhausting the pool.

The stack trace shows the failure:

2020-07-29 10:27:50 ERROR com.okay.family.service.impl.CaseCollectionServiceImpl:287 [] [Thread-176] 处理用例参数发生错误!
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30006ms.
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:308)
    ... (stack trace truncated)

Solution

Adjust transaction propagation

Remove the REQUIRES_NEW setting so the method participates in the existing transaction (default REQUIRED). This prevents the creation of a new connection for each test case and allows the connection to be reused and properly closed.

Set connection timeout

Configure the HikariCP connection pool to fail fast when a connection cannot be obtained, e.g.:

spring.datasource.hikari.connection-timeout=3000

With the corrected key check ( map.containsKey(id)) and the adjusted transaction propagation, the service no longer leaks connections, and the test suite runs without hitting the CannotCreateTransactionException.