spring - 在 StreamingResponseBody 中加载实体时耗尽 JDBC 连接
问题描述
我目前正在开发一种流媒体服务，我想用它以更有效的方式在客户端播放音频文件。为了做到这一点，我有一个端点，StreamingRepsonseBody它只返回一个音频文件的请求字节。
但是，在流式传输音频文件之后，我遇到了获取对服务器发出CannotGetJdbcConnectionException的任何请求的问题（在下面找到完整的堆栈跟踪）。
似乎数据库连接永远不会关闭。在 10 个请求之后（通常）抛出异常（我认为这也是打开连接的限制）。
我能够通过不调用数据库来“解决”这个问题，但这意味着客户端必须向我发送我通常从数据库中获取的信息
工作端点
如果我得到bucketNameand bucketFilenamevia @RequestParam，我不需要TrackEntity从数据库中获取 并且流端点按预期工作：
@RequestMapping(
value = "/tracks/{trackId}/play",
method = RequestMethod.GET,
produces = MediaType.APPLICATION_OCTET_STREAM_VALUE
)
public StreamingResponseBody playTrack(
@PathVariable("trackId") Long trackId,
@RequestHeader("Range") String range,
@RequestParam("bucketName") String bucketName,
@RequestParam("bucketFilename") String bucketFilename
) {
String[] rangeValues = range.split("=")[1].split("-");
int rangeStart = Integer.parseInt(rangeValues[0]);
int rangeEnd = Integer.parseInt(rangeValues[1]);
int rangeSize = rangeEnd - rangeStart;
InputStreamResource inputStreamResource = this.trackService
.readChunk(bucketName, bucketFilename, rangeStart);
return outputStream -> {
try (InputStream inputStream = inputStreamResource.getInputStream()) {
StreamUtils.copyRange(inputStream, outputStream, 0, rangeSize);
}
};
}
破碎的端点
这是应该的。使用trackService我想TrackEntity从数据库中加载。但是，这就是异常的来源。
@RequestMapping(
value = "/tracks/{trackId}/play",
method = RequestMethod.GET,
produces = MediaType.APPLICATION_OCTET_STREAM_VALUE
)
public StreamingResponseBody playTrack(
@PathVariable("trackId") Long trackId,
@RequestHeader("Range") String range
) {
String[] rangeValues = range.split("=")[1].split("-");
// Fetch TrackEntity from the database
TrackEntity track = this.trackService.getTrack(trackId);
String bucketName = track.getBucketName();
String bucketFilename = track.getBucketFilename();
int rangeStart = Integer.parseInt(rangeValues[0]);
int rangeEnd = Integer.parseInt(rangeValues[1]);
int rangeSize = rangeEnd - rangeStart;
InputStreamResource inputStreamResource = this.trackService
.readChunk(bucketName, bucketFilename, rangeStart);
return outputStream -> {
try (InputStream inputStream = inputStreamResource.getInputStream()) {
StreamUtils.copyRange(inputStream, outputStream, 0, rangeSize);
}
System.out.println(String.format("Fetched %.4f MB", (rangeSize / Math.pow(1024.0, 2))));
};
}
这是getTrack(). 这里没有什么令人兴奋的。
public TrackEntity getTrack(Long trackId) {
return this.trackRepository.findById(trackId)
.orElseThrow(ResourceNotFoundException::new);
}
全栈跟踪
这是抛出异常的完整堆栈跟踪。
org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:612) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:790) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.security.oauth2.provider.token.store.JdbcTokenStore.readAccessToken(JdbcTokenStore.java:160) ~[spring-security-oauth2-2.3.2.RELEASE.jar:na]
at org.springframework.security.oauth2.provider.token.DefaultTokenServices.loadAuthentication(DefaultTokenServices.java:229) ~[spring-security-oauth2-2.3.2.RELEASE.jar:na]
at org.springframework.security.oauth2.provider.token.DefaultTokenServices$$FastClassBySpringCGLIB$$5a1f25c.invoke(<generated>) ~[spring-security-oauth2-2.3.2.RELEASE.jar:na]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:685) ~[spring-aop-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.security.oauth2.provider.token.DefaultTokenServices$$EnhancerBySpringCGLIB$$76cd39c0.loadAuthentication(<generated>) ~[spring-security-oauth2-2.3.2.RELEASE.jar:na]
at org.springframework.security.oauth2.provider.authentication.OAuth2AuthenticationManager.authenticate(OAuth2AuthenticationManager.java:83) ~[spring-security-oauth2-2.3.2.RELEASE.jar:na]
at org.springframework.security.oauth2.provider.authentication.OAuth2AuthenticationProcessingFilter.doFilter(OAuth2AuthenticationProcessingFilter.java:150) ~[spring-security-oauth2-2.3.2.RELEASE.jar:na]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:92) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:77) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.security.web.access.channel.ChannelProcessingFilter.doFilter(ChannelProcessingFilter.java:157) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE]
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358) ~[spring-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271) ~[spring-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at io.ear.spring.config.SimpleCORSFilter.doFilter(SimpleCORSFilter.java:36) ~[classes/:na]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:526) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:367) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:860) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1591) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) ~[na:na]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.29.jar:9.0.29]
at java.base/java.lang.Thread.run(Thread.java:832) ~[na:na]
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:697) ~[HikariCP-3.4.1.jar:na]
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:196) ~[HikariCP-3.4.1.jar:na]
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:161) ~[HikariCP-3.4.1.jar:na]
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-3.4.1.jar:na]
at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79) ~[spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
... 64 common frames omitted
堆栈跟踪到“检测到明显的连接泄漏”
感谢M. Deinum，我能够通过设置application.properties以下内容首先找到泄漏：
logging.level.org.springframework.security=DEBUG
spring.datasource.hikari.leak-detection-threshold=15000
这导致我遇到了例外：
java.lang.Exception: Apparent connection leak detected
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-3.4.1.jar:na]
at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:38) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:104) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:134) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
at org.hibernate.internal.SessionImpl.connection(SessionImpl.java:462) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:564) ~[na:na]
at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:282) ~[spring-core-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:266) ~[spring-core-5.2.2.RELEASE.jar:5.2.2.RELEASE]
at org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle.doGetConnection(HibernateJpaDialect.java:430) ~[spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE]
...
解决方案
首先不要使用
SimpleTaskExecutor这会导致以后出现问题（它会Thread为每个传入的请求创建对象，并且可能无法正确清理它们。您的代码可能由于不关闭
InputStream和/或进入连续循环而导致资源泄漏。您正在使用 JPA，并且默认情况下该
spring.jpa.open-in-view属性已启用（如果您没有明确设置它，您将在启动时收到警告）。您可能希望将其切换false为仅在需要时打开会话。
要修复 1，只需删除您的 bean 定义，Spring Boot 就会为您配置正确TaskExecutor的（开箱即用）。
对于数字 2，重写控制器中的代码以防止资源泄漏。
@GetMapping(
value = "/tracks/{trackId}/play",
produces = MediaType.APPLICATION_OCTET_STREAM_VALUE
)
public StreamingResponseBody playTrack(
@PathVariable("trackId") Long trackId,
@RequestHeader("Range") String range
) {
String[] rangeValues = range.split("=")[1].split("-");
int rangeStart = Integer.parseInt(rangeValues[0]);
int rangeEnd = Integer.parseInt(rangeValues[1]);
return outputStream -> {
InputStreamResource inputStreamResource = this.trackService.playTrack(trackId);
try (InputStream inputStream = inputStreamResource.getInputStream()) {
StreamUtils.copyRange(inputStream, outputStream, rangeStart, rangeEnd);
}
};
}
此代码将关闭InputStream并且StreamUtils.copyRange永远不会进入无限循环（并且它会清理您的代码）。
3 号很容易修复，将以下内容添加到您的application.properties.
spring.jpa.open-in-view=false
但是，这可能会对您的应用程序的其他部分不再正常工作产生影响。特别是如果您有一个视图并依靠视图模式中的打开会话来自动检索尚未检索到的数据（即您的实体中的惰性关系）。
推荐阅读
- vue.js - 如何使用 Vuex 获取和设置值？
- ios - 在 AppSync 中获取“软件导致连接中止”
- javascript - 从 html javascript 中删除反斜杠
- r - R如何逐步将向量的元素相加？
- google-cloud-platform - 在 GKE 上安装 coredns
- java - OkHttp3 在慢速互联网上从不超时
- scala - 如何动态替换 Spark jar 中的类型安全包含语句
- c++ - 构建静态qt时未解决的外部问题
- symfony - Symfony，集合，多个实体，得到arrayCollection，需要实体
- ios - 如何使用 SwiftLint 排除 Pod 警告