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; } 4 Answers
Answers 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; } } Answers 2
A solution to your problem could be Thead.stop(), which has been deprecated centuries ago (you can find more on the topic here).
To implement the similar behavior it is suggested to use the Thread.interrupt(), which is (in the context of Task) the same as the the Task.cancel().
Solutions:
- Fill your
calculationMethodwithisCancelled()checks. - Try to interrupt an underling operation through an other
Thread.
The second solution is probably what you are looking for, but it depends on the actual code of the calculationMethod (which I guess you can't share).
Generic examples for killing long database operations (all of this are performed from another thread):
- Kill the connection to the Database (assuming that the Database is smart enough to kill the operation on disconnect and then unlock the database).
- Ask for the Database to kill an operation (eg.
kill <SPID>).
EDIT:
I hadn't see that that you specified the database to SQLite when I wrote my answer. So to specify the solutions for SQLite:
- Killing the connection will not help
- Look for the equivalent of sqlite3_interrupt in your java SQLite interface
Answers 3
Maybe you can invoke thread instance t1, t1.interrupt() method, then in the run method of thread( Maybe calculationMethod), add a conditional statement.
public void run() { while (!Thread.currentThread().isInterrupted()) { try { // my code goes here } catch (IOException ex) { log.error(ex,ex) } } } Answers 4
With WAL mode (write-ahead logging) you can do many queries in parallel to the sqlite database
WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
Perhaps these links are of interest to you:
https://stackoverflow.com/a/6654908/1989579 https://groups.google.com/forum/#!topic/sqlcipher/4pE_XAE14TY https://stackoverflow.com/a/16205732/1989579
0 comments:
Post a Comment