使用springboot API在SQLite中插入大量数据：数据库文件被锁定&没有可用的缓冲区空间(是否达到最大连接数？)

j5fpnvbx  于 2022-12-12  发布在  Spring
关注(0)|答案(1)|浏览(187)

我必须开发一个REST API，它应该连接到其他可以处理数据库插入的Web服务和Java应用程序。我确实每秒钟都有很多插入。为了这个，提出的一个解决方案是将所有插入和查询集中在一个Web API中。为了伪造处理插入的最终应用程序，我创建了一个使用多个线程的客户端，它调用处理插入的后端。我的“假应用程序”有5个线程用于查询我的后端。下面是我使用的一个线程实现：

Thread t1 = new Thread(new Runnable() {
    @Override
    public void run() {
        List<ResponseBody> addedValues = new ArrayList<>();
        StopWatch watch = new StopWatch();
        watch.start();
        for(int i = 0; i < 10000; i++) {
            if(i % 1000 == 0 && i != 0) {
                System.out.println("T1 " + i);
            }
            OkHttpClient.Builder builder = new OkHttpClient.Builder();
            builder.connectTimeout(3600, TimeUnit.SECONDS);
            builder.readTimeout(3600, TimeUnit.SECONDS);
            builder.writeTimeout(3600, TimeUnit.SECONDS);
            OkHttpClient client = builder.build();
            String json = "{ " +
                    "\"id\": \"db-t1-" + i + "\", " +
                    "\"user\": \"test\", " +
                    "\"password\": \"test\", " +
                    "\"driver\": \"test\", " +
                    "\"connectionString\": \"test\", " +
                    "\"type\": \"test\"" +
                    "}";
            RequestBody body = RequestBody.create(json, MediaType.get("application/json; charset=utf-8"));
            Request request = new Request.Builder()
                    .url("http://localhost:8080/db")
                    .post(body)
                    .addHeader("Content-Type", "application/json")
                    .build();
            Call call = client.newCall(request);
            try {
                Response response = call.execute();
                addedValues.add(response.body());
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
        System.out.println(addedValues.size());
        watch.stop();
        System.out.println("----- t1 - Time Elapsed: " + watch.getTime()); 
    }
});

所有其他线程都与这个线程相同。在另一边，我有一个使用最新框架版本的Springboot应用程序。所有线程都调用一个控制器：

@PostMapping(
        consumes = MediaType.APPLICATION_JSON_VALUE,
        produces = MediaType.APPLICATION_JSON_VALUE
)
public ResponseEntity<DatabaseCreationResponse> save(@RequestBody DatabaseRequest request) {
    ModelMapper modelMapper = new ModelMapper();
    modelMapper.getConfiguration().setMatchingStrategy(MatchingStrategies.STRICT);
    DatabaseDto requestDto = modelMapper.map(request, DatabaseDto.class);
    DatabaseDto databaseDto = databaseService.save(requestDto);
    return new ResponseEntity<>(
            modelMapper.map(databaseDto, DatabaseCreationResponse.class),
            HttpStatus.CREATED);
}

此控制器正在调用服务方法-服务方法尚未完全实现...

public DatabaseDto save(DatabaseDto requestDto) {
    ModelMapper modelMapper = new ModelMapper();
    modelMapper.getConfiguration().setMatchingStrategy(MatchingStrategies.STRICT);
    DatabaseDto returnValue;
    try {
        DatabaseEntity databaseEntity = modelMapper.map(requestDto, DatabaseEntity.class);
        DatabaseEntity storedEntity = databaseRepository.save(databaseEntity);
        returnValue = DatabaseDto.builder()
                .operationId("generateUID")
                .status("OK (use enum)")
                .createdAt(new Date())
                .build();
    } catch (RuntimeException exception) {
        returnValue = DatabaseDto.builder()
                .operationId("generateUID")
                .status("Error (use enum)")
                .message(exception.getMessage())
                .createdAt(new Date())
                .build();
        exception.printStackTrace();
    }

    return returnValue;
}

我尝试实现JNDI并使用Hikari来处理SQLite和并发访问数据库的已知问题。
首先，我将存储库方法定义为@Transactional：

@Transactional
@Override
DatabaseEntity save(DatabaseEntity entity);

现在，为了完成配置，我向application.properties添加了配置。我首先配置了我的数据源：

spring.datasource.driverClassName=org.sqlite.JDBC
spring.datasource.url=jdbc:sqlite:C:/data/db.db
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.jndiName=jdbc/myDataSource
#disable schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=create

我还尝试配置Hikari：

#maximum number of milliseconds that a client will wait for a connection
spring.datasource.hikari.connection-timeout = 1800000
spring.datasource.hikari.connectionTimeout=1800000
#minimum number of idle connections maintained by HikariCP in a connection pool
spring.datasource.hikari.minimum-idle= 1
#maximum pool size
spring.datasource.hikari.maximum-pool-size= 1
spring.datasource.hikari.data-source-j-n-d-i=jdbc/myDataSource
#maximum idle time for connection
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.idleTimeout=600000
# maximum lifetime in milliseconds of a connection in the pool after it is closed.
spring.datasource.hikari.max-lifetime= 1800000
spring.datasource.hikari.maxLifetime=1800000
#default auto-commit behavior.
spring.datasource.hikari.auto-commit =true

最后，我试着设置嵌入式tomcat服务器：

# Maximum queue length for incoming connection requests when all possible request processing threads are in use.
server.tomcat.accept-count=10000
# Delay between the invocation of backgroundProcess methods. If a duration suffix is not specified, seconds will be used.
server.tomcat.background-processor-delay=10s
# Maximum number of connections that the server accepts and processes at any given time.
server.tomcat.max-connections=1000000
# Maximum amount of worker threads.
server.tomcat.max-threads=20000
# Minimum amount of worker threads.
server.tomcat.min-spare-threads=1000
# Character encoding to use to decode the URI.
server.tomcat.uri-encoding=UTF-8 
# server.tomcat.use-relative-redirects= # Whether HTTP 1.1 and later location headers generated by a call to sendRedirect will use relative or absolute redirects.

现在我的问题如下。我试着插入：50 000个条目使用5个线程，过了一段时间，我在后端应用程序中出现以下错误消息：

Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:61)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3392)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3991)
    at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:103)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:612)
    at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:483)
    at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:721)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:480)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:329)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
    at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1422)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:476)
    at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2233)
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:1929)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:439)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:562)
    ... 65 more
Caused by: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)
    at org.sqlite.core.DB.newSQLException(DB.java:1135)
    at org.sqlite.core.DB.newSQLException(DB.java:1146)
    at org.sqlite.core.DB.execute(DB.java:941)
    at org.sqlite.core.DB.executeUpdate(DB.java:1010)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.lambda$executeLargeUpdate$2(JDBC3PreparedStatement.java:118)
    at org.sqlite.jdbc3.JDBC3Statement.withConnectionTimeout(JDBC3Statement.java:454)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.executeLargeUpdate(JDBC3PreparedStatement.java:117)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:99)
    at jdk.internal.reflect.GeneratedMethodAccessor67.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
    at jdk.proxy2/jdk.proxy2.$Proxy110.executeUpdate(Unknown Source)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
    ... 84 more

在我的前端应用程序中，我确实有另一个错误消息：

Exception in thread "Thread-0" Exception in thread "Thread-3" java.lang.RuntimeException: java.net.SocketException: No buffer space available (maximum connections reached?): connect
    at fr.dsidiff.client.Main$1.run(Main.java:55)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.net.SocketException: No buffer space available (maximum connections reached?): connect
    at java.base/java.net.PlainSocketImpl.connect0(Native Method)
    at java.base/java.net.PlainSocketImpl.socketConnect(PlainSocketImpl.java:105)
    at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:412)
    at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:255)
    at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:237)
    at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.base/java.net.Socket.connect(Socket.java:608)
    at okhttp3.internal.platform.Platform.connectSocket(Platform.kt:128)
    at okhttp3.internal.connection.RealConnection.connectSocket(RealConnection.kt:295)
    at okhttp3.internal.connection.RealConnection.connect(RealConnection.kt:207)
    at okhttp3.internal.connection.ExchangeFinder.findConnection(ExchangeFinder.kt:226)
    at okhttp3.internal.connection.ExchangeFinder.findHealthyConnection(ExchangeFinder.kt:106)
    at okhttp3.internal.connection.ExchangeFinder.find(ExchangeFinder.kt:74)
    at okhttp3.internal.connection.RealCall.initExchange$okhttp(RealCall.kt:255)
    at okhttp3.internal.connection.ConnectInterceptor.intercept(ConnectInterceptor.kt:32)
    at okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.kt:109)
    at okhttp3.internal.cache.CacheInterceptor.intercept(CacheInterceptor.kt:95)
    at okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.kt:109)
    at okhttp3.internal.http.BridgeInterceptor.intercept(BridgeInterceptor.kt:83)
    at okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.kt:109)
    at okhttp3.internal.http.RetryAndFollowUpInterceptor.intercept(RetryAndFollowUpInterceptor.kt:76)
    at okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.kt:109)
    at okhttp3.internal.connection.RealCall.getResponseWithInterceptorChain$okhttp(RealCall.kt:201)
    at okhttp3.internal.connection.RealCall.execute(RealCall.kt:154)
    at fr.dsidiff.client.Main$1.run(Main.java:52)
    ... 1 more
    Suppressed: java.net.SocketException: No buffer space available (maximum connections reached?): connect
        ... 26 more
        at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:566)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:654)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:407)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
        at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:163)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:218)
        at jdk.proxy2/jdk.proxy2.$Proxy102.save(Unknown Source)
        at fr.dsidiff.app.proxy.service.DatabaseService.save(DatabaseService.java:42)
        at fr.dsidiff.app.proxy.ui.controller.DatabaseController.save(DatabaseController.java:51)
        at jdk.internal.reflect.GeneratedMethodAccessor25.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:568)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:207)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:152)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:884)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1080)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:973)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1003)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:906)
        at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:731)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:880)
        at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:814)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:223)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
        at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
        at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:119)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:400)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1739)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
        at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
        at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.base/java.lang.Thread.run(Thread.java:833)
    Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:61)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3392)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3991)
        at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:103)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:612)
        at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:483)
        at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:721)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:480)
        at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:329)
        at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
        at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107)
        at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1422)
        at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:476)
        at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2233)
        at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:1929)
        at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:439)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
        at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
        at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:562)
        ... 65 more
    Caused by: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)
        at org.sqlite.core.DB.newSQLException(DB.java:1135)
        at org.sqlite.core.DB.newSQLException(DB.java:1146)
        at org.sqlite.core.DB.execute(DB.java:941)
        at org.sqlite.core.DB.executeUpdate(DB.java:1010)
        at org.sqlite.jdbc3.JDBC3PreparedStatement.lambda$executeLargeUpdate$2(JDBC3PreparedStatement.java:118)
        at org.sqlite.jdbc3.JDBC3Statement.withConnectionTimeout(JDBC3Statement.java:454)
        at org.sqlite.jdbc3.JDBC3PreparedStatement.executeLargeUpdate(JDBC3PreparedStatement.java:117)
        at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:99)
        at jdk.internal.reflect.GeneratedMethodAccessor67.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:568)
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
        at jdk.proxy2/jdk.proxy2.$Proxy110.executeUpdate(Unknown Source)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
        ... 84 more

我的问题如下：

  • 是否有办法增加最大连接数？
  • 是否有办法对查询进行排队或确保在每个HTTP查询之间释放连接？

实际上，这个应用程序也是作为一个jar运行的，我还必须将它作为一个War部署到Tomcat Web服务器中。
谢谢你！

spring-boot

1条答案

按热度按时间
tct7dpnv

tct7dpnv1#

我通过修改线程解决了客户端问题：call.execute（）方法没有关闭。2为了自动关闭execute调用，我用一个带资源的try替换了我的经典try catch：

try(Response response = call.execute()) {
                        // Response response = call.execute();
                        addedValues.add(response.body());

我还尝试为我示例化的每个客户机使用Dispatcher和connexion池。

Thread t1 = new Thread(new Runnable() {
        @Override
        public void run() {
            List<ResponseBody> addedValues = new ArrayList<>();
            StopWatch watch = new StopWatch();
            watch.start();

            Dispatcher dispatcher = new Dispatcher();
            dispatcher.setMaxRequests(160);
            dispatcher.setMaxRequestsPerHost(32);

            OkHttpClient client = new OkHttpClient.Builder()
                    .dispatcher(dispatcher)
                    .connectTimeout(3600, TimeUnit.SECONDS)
                    .readTimeout(3600, TimeUnit.SECONDS)
                    .writeTimeout(3600, TimeUnit.SECONDS)
                    .retryOnConnectionFailure(true)
                    .connectionPool(new ConnectionPool(1500,600,TimeUnit.SECONDS))
                    .build();

            for(int i = 0; i < 10000; i++) {
                if(i % 1000 == 0 && i != 0) {
                    System.out.println("T1 " + i);
                }

                String json = "{ " +
                        "\"name\": \"db-t1-" + i + "\", " +
                        "\"user\": \"test\", " +
                        "\"password\": \"test\", " +
                        "\"driver\": \"test\", " +
                        "\"connectionString\": \"test\", " +
                        "\"type\": \"test\"" +
                        "}";

                RequestBody body = RequestBody.create(json, MediaType.get("application/json; charset=utf-8"));

                Request request = new Request.Builder()
                        .url("http://localhost:8080/db")
                        .post(body)
                        .addHeader("Content-Type", "application/json")
                        .build();
                Call call = client.newCall(request);

                try(Response response = call.execute()) {
                    // Response response = call.execute();
                    addedValues.add(response.body());
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
            System.out.println(addedValues.size());
            watch.stop();
            System.out.println("----- t1 - Time Elapsed: " + watch.getTime()); // Prints: Time
        }
    });

我通过将服务方法定义为async解决了文件锁定问题

@Async
public synchronized DatabaseDto save(DatabaseDto requestDto) {
    ModelMapper modelMapper = new ModelMapper();
    modelMapper.getConfiguration().setMatchingStrategy(MatchingStrategies.STRICT);
    DatabaseDto returnValue;
    try {
        DatabaseEntity databaseEntity = modelMapper.map(requestDto, DatabaseEntity.class);
        DatabaseEntity storedEntity = databaseRepository.save(databaseEntity);
        returnValue = DatabaseDto.builder()
                .operationId("generateUID")
                .status("OK (use enum)")
                .createdAt(new Date())
                .build();
    } catch (RuntimeException exception) {
        returnValue = DatabaseDto.builder()
                .operationId("generateUID")
                .status("Error (use enum)")
                .message(exception.getMessage())
                .createdAt(new Date())
                .build();
        exception.printStackTrace();
    }

    return returnValue;
}
赞(0）分享  回复(0）举报  2022-12-12
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com