I have a QRunnable sub-class that takes a query and executes it in another thread (via global instance of QThreadPool). I found out that in some rare cases these clashes and the database is not correctly updated (using SQLite ver3). I figured that if the query is to operate on the same table it should not run in parallel but rather in sequence to avoid this. But how to implement it?
One idea I have is to have only one QRunnable constantly running and sending new queries via signals & slots (my subclass have signals already so that would not be a big deal). My problem with it is that it seems like a resource waste because most of the time it won't be doing anything.
Another idea is to create some kind of queue by using actual QThread and pushing the objects into it. It would guarantee they are executed in sequence. The problem with this is again waste of resources and managing the thread itself.
I suspect Qt Concurrent could be useful here but I cannot seem to wrap my head around it.
I would appreciate any advice! Thanks.
I am not sure if you have enough information on what you are trying to do. You might want to explain what type of processing gets done by each thread. At the very least, you probably want to keep track of whether the database is in use by another thread, and wait until it becomes available. You need to have resource intensive stuff be outside the GUI thread, assuming you have a GUI, but it sounds like you might not want to run more then one thread accessing the database at the same time.
It seems like you are asking for a way to queue tasks that access the database so they execute consecutively.
Is response time critical? IE, do you need to check to see if the same part of the database is being accessed by various items to see if you can run them concurrently?
I think QSqlDatabase used with Sqlite is not thread-safe?
Context: I am sub-classing QAbstractTableModel in order to create my own SQL enabled model as alternative to Qt native ones. I have separated the database updates into another thread to prevent the queries blocking the main (GUI) thread. In Qt native models inserting and removing rows takes ages even with very low amount of them (e.g. 5) at a time. The database layout and data of my model is mirrored in the memory and exposed to views (and updated according to what the update queries asynchronously deliver to the database at later time).
Now as I have said with the Undo & Redo my original approach sometimes stopped the queries before they were finished either because of a bad design in regards to QSqlDatabase and threading on my part or because the queries conflicted on the database level.
I have solved it like this:
I have created a static QThread object in the class and I push all update/insert/remove queries into it and start them with a signal. The database connection is created in the thread (once in the first such query) and QSqlQuery is created in there using that connection. Not only it solved the interrupted queries (and no doubt issues from using connection from another thread) but it is even slightly faster. For another rare occurrence of calling select while there are active update queries I have implemented a notification system that waits until there are no active queries updating the database table (with time out of course).
My testing currently shows no problems. Since I took the effort to optimize the queries (for instance they are always one query per any requested operation regardless of size/scope) it is very fast and seem perfectly safe even on large table with large requested operations (hundreds rows inserts/removes etc.). Even traversing undo & redo as quickly as I can click and in any random order does not show any lag or database table data corruption.