I was lurking around the forums for long, but now the time arrived for an actual registration
![Smile :)](./images/smilies/icon_smile.gif)
I started to develop applications with database backends about 6 years ago. I quickly met the standard issues with available components (not cross-platform; not supporting all mainstream databases; buggy or simply CRAZY expensive) and then I found Zeos. Since then this became the first component I install after a dev machine meltdown (I experiment with Windows APIs a lot
![Smile :)](./images/smilies/icon_smile.gif)
A recent job change forced me to upgrade from Delphi 7 to 10.2 so I started to patch / rewrite some of my past applications. There is one which is using a small (~7-10 tables) database backend, with about 5 worker threads. All threads should be able to read (and occasionally write) all tables. In the past I solved this by using ZQuery and writing separate DLLs for each database type but this is a lot pain to maintain. Furthermore I quickly realized that by using ZTables even the data-processing performance will improve where it's the most critical so if I rebuild this from scratch - why not to implement a better way.
I read a lot about multithreading applications with databases, but I am really curious if there are actually best practices to follow?
My main problem here is that the golden rule (each thread must have it's own database connection) introduces a new issue - if a thread is updating a table, no other threads will know that (on database level) the data was changed. This can be solved by sending a signal to refresh, but (according to my prior testing) this operation can be slow. An other idea is to "lock" the table which will be modified (in the application level; with a critical section or so) and update them all from code but this feels a bit hacky if you know what I mean.
So please, do not restrain yourselves. Hit me with the best practices or ideas of a multithreaded database application!