Managing threads accessing a database with Java

Question

I am working on an app that accesses an SQLite database. The problem is the DB gets locked when there is a query to it. Most of the time this is not a problem because the flow of the app is quite linear.

However I have a very long calculation process which is triggered by the user. This process involves multiple calls to the database in between calculations.

I wanted the user to get some visual feedback so I have been using Javafx progressIndicator and a Service from the Javafx.Concurrency framework. The problem is this leaves the user free to move around the app and potentially triggering other calls to the database.

This caused an exception that the database file is locked. I would like a way to stop that thread from running when this case happens however I have not been able to find any clear examples online. Most of them are oversimplified and I would like a way which is scalable. I've tried using the cancel() method but this does not guarantee that the thread will be cancelled in time.

Because I am not able to check in all parts of the code for isCancelled sometimes there is a delay between the time the thread is canceled and the time it effectively stops.

So I thought of the following solution but I would like to know if there is a better way in terms of efficiency and avoiding race conditions and hanging.

    // Start service
    final CalculatorService calculatorService = new CalculatorService();

    // Register service with thread manager
    threadManager.registerService(CalculatorService);

    // Show the progress indicator only when the service is running
    progressIndicator.visibleProperty().bind(calculatorService.runningProperty());

calculatorService.setOnSucceeded(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent workerStateEvent) {
            System.out.println("SUCCEEDED");
            calculatorService.setStopped(true);
        }
    });

    // If something goes wrong display message
    calculatorService.setOnFailed(new EventHandler<WorkerStateEvent>() {
        @Override
        public void handle(WorkerStateEvent workerStateEvent) {
            System.out.println("FAILED");
            calculatorService.setStopped(true);
        }
    });

    // Restart the service
    calculatorService.restart(); 

This is my service class which I have subclassed to include methods that can be used to set the state of the service (stopped or not stopped)

    public class CalculatorService extends Service implements CustomService {
    private AtomicBoolean stopped;
    private CalculatorService serviceInstance;

    public FindBundleService() {
        stopped = new AtomicBoolean(false);
        instance = this;
    }

    @Override
    protected Task<Results> createTask() {
        return new Task<Result>() {

            @Override
            protected Result call() throws Exception {
                try {
                    Result = calculationMethod(this, serviceInstance);
                    return Result;
                } catch (Exception ex) {
                    // If the thread is interrupted return
                    setStopped(true);
                    return null;
                }
            }
        };
    }

    @Override
    public boolean isStopped() {
        return stopped.get();
    }

    @Override
    public void setStopped(boolean stopped) {
        this.stopped.set(stopped);
    }
}

The service implements this interface which I defined

public interface CustomService {

    /**
     * Method to check if a service has been stopped
     * 
     * @return
     */
    public boolean isStopped();

    /**
     * Method to set a service as stopped
     * 
     * @param stopped
     */
    public void setStopped(boolean stopped);

}

All services must register themselves with the thread manager which is a singleton class.

public class ThreadManager {

    private ArrayList<CustomService> services;

    /**
     * Constructor
     */
    public ThreadManager() {
        services = new ArrayList<CustomService>();
    }

    /**
     * Method to cancel running services
     */
    public boolean cancelServices() {
        for(CustomService service : services) {
            if(service.isRunning()) {
                ((Service) service).cancel();
                while(!service.isStopped()) {
                    // Wait for it to stop
                }
            }
        }
        return true;
    }


    /**
     * Method to register a service
     */
    public void registerService(CustomService service) {
        services.add(service);
    }

    /**
     * Method to remove a service
     */
    public void removeService(CustomService service) {
        services.remove(service);
    }

}

In any place in the app if we want to stop the service we call cancelServices(). This will set the state to cancelled I'm checking for this in my calculationMethod() then setting the state to stopped just before returning (effectively ending the thread).

if(task.isCancelled()) {
        service.setStopped(true);
        return null;
}

Answer 1

(I will assume you are using JDBC for your database queries and that you have control over the code running the queries)

I would centralize all database accesses in a singleton class which would keep the last PreparedStatement running the current query in a single thread ExecutorService. You could then ask that singleton instance things like isQueryRunning(), runQuery(), cancelQuery() that would be synchronized so you can decide to show a message to the user whenever the computation should be canceled, cancel it and start a new one.

Something like (add null checks and catch (SQLException e) blocks):

public class DB {

    private Connection cnx;
    private PreparedStatement lastQuery = null;
    private ExecutorService exec = Executors.newSingleThreadExecutor(); // So you execute only one query at a time

    public synchronized boolean isQueryRunning() {
        return lastQuery != null;
    }

    public synchronized Future<ResultSet> runQuery(String query) {
        // You might want to throw an Exception here if lastQuery is not null (i.e. a query is running)
        lastQuery = cnx.preparedStatement(query);
        return exec.submit(new Callable<ResultSet>() {
            public ResultSet call() {
                try {
                    return lastQuery.executeQuery();
                } finally { // Close the statement after the query has finished and return it to null, synchronizing
                    synchronized (DB.this) {
                        lastQuery.close();
                        lastQuery = null;
                    }
                }
            }
            // Or wrap the above Future<ResultSet> so that Future.cancel() will actually cancel the query
    }

    public synchronized void cancelQuery() {
        lastQuery.cancel(); // I hope SQLite supports this
        lastQuery.close();
        lastQuery = null;
    }

}