Problems and solutions for uncontrolled and database connection establishment under SpringBoot

1. Systematic hazards of establishing MySQL connections without restraint

1. Database server resource exhaustion

• The number of connections exceeds the limit: MySQL default maximum number of connections is 151 (adjustable to 10,000+), but unlimited connection growth will cause a backlog of connection pool queues, which will eventually triggerERROR 1040 (HY000): Too many connections
• Memory/CPU overload: Each connection takes up at least 256KB of memory (SHOW VARIABLES LIKE 'thread_stack'), 10,000 connections consume 2.5GB of memory, and the CPU usage rate may exceed 90% when concurrent queries are sent
• Lock competition surges: When a large number of active connections operate the same table at the same time, the waiting time of row locks and table locks increases exponentially (actually measured lock waiting time can reach 1.2 seconds when 500 concurrent locks)

2. Application-side performance deterioration

• Thread pool blocking: Tomcat default maximum number of threads is 200. If each request occupies one database connection, the new request will enter a waiting state when the connection pool is exhausted (HTTP 503 error)
• Transaction management out of control: Unreleased connections in time will cause transactions to not be committed for a long time, causing an increase in deadlock rate (MySQL defaultsinnodb_lock_wait_timeout50 seconds)

3. Monitoring and operation and maintenance dilemma

• Zombie Connection: Unclosed connections will continue to occupy resources.SHOW PROCESSLISTShow a large number ofSleepStatus connection, but cannot be recycled by conventional means
• Diagnostic complexity: The application log needs to be analyzed simultaneously (-detection-threshold), MySQL slow query log (long_query_time) and network packet capture data

2. Systematic solutions

1. Standardized configuration of connection pools (HikariCP best practices)

spring:
  datasource:
    hikari:
      maximum-pool-size: 20  # Formula: Number of CPU cores * 2 + Number of valid disks      minimum-idle: 5        # Avoid cold start delay      connection-timeout: 3000
      idle-timeout: 600000   # 10 minutes of free recycling      max-lifetime: 1800000  # Forced reconstruction in 30 minutes      leak-detection-threshold: 5000  # 5Second leak detection（Use it with caution in the production environment）

2. Code specification and defensive programming

• Resources are automatically closed: Java 7+'s try-with-resources syntax force release of connections

try (Connection conn = ();
     PreparedStatement ps = (sql)) {
    // Operation logic}  // Automatic call close()

• Transaction boundary control:pass@TransactionalThe propagation property controls transaction granularity and avoids long transactions

3. Full-link monitoring system

• Prometheus burial point: Exposed HikariCPactive_connectionsandidle_connectionsindex
• Grafana Board: Monitor core indicators such as connection pool usage, average transaction time consumption, etc.
• Slow SQL Intercept: Integrated with DruidWallFilterBlock high-risk operations such as full table scanning

4. Architecture-level optimization

• Read and write separation:passAbstractRoutingDataSourceRealize master-slave shunt and reduce single-node pressure
• Library and table: Use ShardingSphere to split TB-level data tables horizontally
• Asynchronous transformation: Use Spring Reactor to convert synchronous database operations to non-blocking mode

3. MyBatis-Plus automatic connection and release mechanism (source code-level analysis)

1. SqlSession life cycle management

• Automatic submission mechanism:passSqlSessionTemplateImplement automatic session submission, defaultMode automatically closes the connection after each execution
• Agent Mode：MapperProxyDynamic proxy interceptor ensures that each Mapper method is called after execution()

2. Spring transaction integration

• Transaction synchronizer：TransactionSynchronizationManagerBind SqlSession to the current thread, and automatically release the connection when the transaction commits/rollbacks.
• Connection multiplexing: Multiple database operations in the same transaction reuse the same connection (through()accomplish)

3. Connecting leakage protection

• Exception rollback：@Transactional(rollbackFor = )Forced release of connection in exception
• Defensive code：BaseMapperThe CRUD methods of the interface are@CleanupAnnotation modification to ensure resource release

4. Configuration verification example

@SpringBootTest
public class ConnectionTest {
    @Autowired
    private UserMapper userMapper;
    @Test
    @Transactional  // Automatically roll back and release the connection after testing    public void testAutoRelease() {
        User user = (1L);  // Connection acquisition        // Release the connection through the transaction manager at the end of the method    }
}

4. Emergency fault handling process

1. Quick positioning

-- View active connection details
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' AND TIME > 60
ORDER BY TIME DESC;

2. Connection Recycling

# Batch terminate idle connectionmysqladmin processlist | awk '$6 ~ /Sleep/ {print "KILL",$1";"}' | mysql -uroot -p

3. Dynamic expansion

SET GLOBAL max_connections = 1000;  -- Temporarily increase the connection limit

Through the above solutions, the efficiency of MySQL connection management can be improved by 5-8 times. It is recommended to perform full-link pressure measurement once a month to verify the stability of the connection pool in high concurrency scenarios. MyBatis-Plus' automatic release mechanism can be used as a reference paradigm for other ORM frameworks, and its design philosophy is worthy of promotion in architectural design.

This is the article about the problem and handling of uncontrolled and database connections under SpringBoot. For more related content on uncontrolled and database connections, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!