Vertx - Database update query takes few minutes

Question

I have a very simple update database operation in my Java Vertx application but for some reason it takes araround 3 minutes. I have attached the console output showing the time. Can somebody please help me to debug why is it so.

2022-07-19 11:54:32.039+0430 [vert.x-eventloop-thread-1] DEBUG com.job.PrintJobHandler -  onSuccess for startPrintJob()   Job Completed Successfully!!!!
2022-07-19 11:54:32.039+0430 [vert.x-eventloop-thread-1] DEBUG com.dao.JobDao - Inside setJobFinishedDate() 14
2022-07-19 11:54:32.039+0430 [vert.x-eventloop-thread-1] DEBUG com.dao.JobDao - Tuple for Parent UID values: [2022-07-19 11:54:32,14]
2022-07-19 11:57:45.733+0430 [vert.x-eventloop-thread-1] DEBUG com.dao.JobDao - Updated job table's finished_on column!!!!!!!!!!!!!!!!!!!!!!!!! 
2022-07-19 11:57:45.733+0430 [vert.x-eventloop-thread-1] DEBUG com.job.PrintJobHandler - Job Completed Successfully!!!!
2022-07-19 11:57:45.734+0430 [vert.x-eventloop-thread-1] DEBUG com.job.PrintJobHandler -  onSuccess for startPrintJob()   Server sent msg --- Finished printing Job ID::::: 14

code:

startPrintJob(jobID, context)
            .onFailure(error -> {
                LOG.debug("startTest() Failed: ", error);
            })
            .onSuccess(res -> { 
                LOG.debug(" onSuccess for startPrintJob()   " + res);
                jobDao.setJobFinishedDate(jobID)
                .onSuccess(result -> {
                    LOG.debug(res);
                    String updatedStatus = "Server sent msg --- Finished printing Job ID::::: "+jobID;
                    
                    context.response()
                    .setStatusCode(200)
                    .putHeader("content-type", "application/json; charset=utf-8")
                    .end(Json.encodePrettily(updatedStatus));
                    
                    LOG.debug(" onSuccess for startPrintJob()   " + updatedStatus);
                });             
            });

public Future<String> setJobFinishedDate(int jobID) {
        
        Promise<String> promise = Promise.promise();
        
        LOG.debug("Inside setJobFinishedDate() "+jobID);
        LocalDateTime issuedAt = LocalDateTime.now();
        DateTimeFormatter format = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");      
        Tuple batch = Tuple.of(issuedAt.format(format), jobID);
        LOG.debug("Tuple for Parent UID values: " + batch.deepToString());

        // Execute the query
        db.preparedQuery("UPDATE job SET finished_on=? WHERE id=?;").execute(batch, res -> {
            if (res.succeeded()) {
                LOG.debug("Updated job table's finished_on column!!!!!!!!!!!!!!!!!!!!!!!!! ");
                promise.complete("Updated job table's finished_on column!!");
            } else {
                System.out.println("Batch failed for UPDATE job table's finished_on column!!" + res.cause());
            }
        });
        return promise.future();
    }

Answer 1

Are you implementing a job queue pattern in a DB?

I think your row is locked and that is why the update hangs for multiple minutes while the job that is currently processing that ID has the row lock

What does the startPrintJob do? Does it return after the job is finished or does it async launch the job and return immediately ?

Another lead could be that the DB pool is exhausted and it is hanging on getting a connection since all are already in use