数据库引起的后端服务Pool Exhausted

发表于 分类于 阅读次数：

ERROR 14678 — [http-nio-127.0.0.1-8071-exec-44] o.h.engine.jdbc.spi.SqlExceptionHelper : [http-nio-127.0.0.1-8071-exec-44] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:100; busy:100; idle:0; lastwait:30000]

数据库

1
2
3
4
5
docker run --name pool-exhausted -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0

docker exec -i pool-exhausted mysql -uroot -p123456  <<< "CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"

docker exec -i pool-exhausted mysql -uroot -p123456 <<< "CREATE TABLE test_db.news (id bigint NOT NULL AUTO_INCREMENT, title varchar(255), content mediumtext, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import base64
import pymysql


def image_to_base64(image_path):
    with open(image_path, "rb") as image_file:
        encoded_string = base64.b64encode(image_file.read())
        return encoded_string.decode('utf-8')

connection = pymysql.connect(host='127.0.0.1',
                             user='root',
                             password='123456',
                             database='test_db')
try:
    cursor = connection.cursor()
    base64_data = image_to_base64("./content.jpg") # content.jpg大小约为4MB
    for i in range(50):
        insert_query = "INSERT INTO test_db.news (title, content) VALUES (%s, %s)"
        data = ('title' + str(i + 1), base64_data)
        cursor.execute(insert_query, data)
        connection.commit()
    print("数据插入成功！")
except pymysql.Error as error:
    connection.rollback()
    print("数据插入失败：{}".format(error))
finally:
    cursor.close()
    connection.close()

服务

1
2
3
4
spring init -j=11 -b=3.1.0 -d=jpa,lombok,mysql,web \
    --build=gradle --type=gradle-project \
    -a=tutorial -n=tutorial -g=com.tutorial \
    pool-exhausted
  • src/main/resources/application.properties
1
2
3
4
5
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test_db?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123456
spring.jpa.show-sql=true
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
  • src/main/java/com/tutorial/tutorial/News.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.tutorial.tutorial;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.Data;

@Entity
@Table(name = "news")
@Data
public class News {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(length = 255)
    private String title;

    @Column(columnDefinition = "MEDIUMTEXT")
    private String content;
}
  • src/main/java/com/tutorial/tutorial/NewsRepo.java
1
2
3
4
5
6
package com.tutorial.tutorial;

import org.springframework.data.jpa.repository.JpaRepository;

public interface NewsRepo extends JpaRepository<News, Long> {
}
  • src/main/java/com/tutorial/tutorial/NewsController.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.tutorial.tutorial;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;


@RestController()
@RequestMapping("/news")
public class NewsController {
    @Autowired
    private NewsRepo newsRepo;

    @GetMapping("")
    public Page<News> getNews() {
        Pageable pageable = PageRequest.of(1, 10);
        Page<News> news = newsRepo.findAll(pageable);
        return news;
    }
}

问题复现

1
./gradlew bootrun
1
ab -n 100 -c 100 "localhost:8080/news"

ERROR 68394 — [io-8080-exec-98] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction] with root cause java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms

因不同线程池的实现和策略差异 导致后端服务的结果略有不同