Home > Back-end >  Mass data insertion in SQLite with springboot API: database file is locked & No buffer space availab
Mass data insertion in SQLite with springboot API: database file is locked & No buffer space availab

Time：12-10

I have to develop a REST API that should be connected to other webservices and java application that can handle database insertion. I do have a lot of insertions each seconds. FOr this one solution proposed was to centralize all insertions and queries in a single web api. In order to fake the final application that handles insertions, I created a client that uses several threads and which calls the backend that handles the insertions. My "fake application" has 5 threads that are used to query my backend. here is a thread implementation I used:

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()); 
    }
});

All the other threads are identical to this one. On the other side i have a Springboot application using the latest framework version. All the threads are calling a controller:

@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);
}

This controller is calling a service method - the service method is not yet fully implemented...

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;
}

I tried to implement JNDI and using Hikari to handle know issues with SQLite and concurrent access to database.

First I defined my repository method as @Transactional:

@Transactional
@Override
DatabaseEntity save(DatabaseEntity entity);

Now to complete the configuration i added configurations to application.properties. I fist configured my datasource:

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

I also tried to configure 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

Finally, I tried to set embedded tomcat server:

# 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.

Now my issue is the following. I try to insert: 50 000 entries using 5 threads and after a while i do have the following error message in the back end application:

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

In my front end application i do have another error message:

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

My questions are the following:

  • is there a way to increase maximum number of connections ?
  • is there a way to queue the queries or to make sure the connection is released between each HTTP queries ?

Actually, the application is running also as a jar, I will also have to deploy it into a Tomcat webserver as a War.

Thanks !

CodePudding user response：

I solved partially my file locked issue: i defined my service method as 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;
}

CodePudding user response：

I solved this issue by modifying my thread: the call.execute() method was not closing. I replaced my classic try catch with a try with resource in order to automatically close my execute call:

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

I also tried to use Dispatcher and connexion pool for each client i instanciated. Here is my implementation:

    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
        }
    });

Page link：https//www.codepudding.com/Backend/638223.html

Prev:In java in which thread is an object runned
Next:What happens when you read and write an object from multiple threads without protection?
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding