DB connection in a separate thread - what's the best way?

Question

I am creating an application that accesses a database. Each time you access the database, the application waits for the job to complete. To support the user interface, I want to put all the database materials in a separate stream.
Here is my idea:

• db-thread creates all the database components it needs when it is created.
• Now the stream just sits and waits for a command
• If he receives a command, he takes an action and returns to standby. During this time, the main thread is waiting.
• db-thread works while the application is running

Does this sound normal?
What is the best way to get database results from db stream to main stream?
I still haven’t done much with streams, so I wonder if a db stream can create a query component from which the main stream reads the results. The main thread and the db thread will never access the request at the same time. Will it still cause problems?

Answer 1

First of all, if you have little experience with multithreading, do not start with the VCL classes. Use the OmniThreadLibrary for (among other things) these reasons:

• The abstraction layer is a task, not a thread, a much better way to deal with concurrency.
• You can easily switch between tasks in your thread and schedule them using the thread pool.
• All low-level details, such as flow stop, bidirectional communication, and more, take care of you. You can focus on database materials.

db-thread creates all the database components it needs when creating

This may not be the best way. Usually I only created components when necessary, but did not immediately destroy them. You should definitely open the connection in the thread pool thread and close it only after the thread has been inactive for some time and the pool will delete it. But it is also often recommended to store the cache of transaction objects and operators.

If he receives a command, he takes an action and returns to standby. During this time, the main stream is waiting.

The first part handles fine when using OTL. Nevertheless, there is no expectation of the main thread, this will not bring much advantage over accessing the database directly in the VCL stream in the first place. You need an asynchronous design to make the best use of multiple threads. Consider the standard form of the database browser, which has controls for filtering records. I handle this by (re-) starting the timer every time one of the controls changes. As soon as the user finishes editing timer events, say, after 500 ms, a task is launched that executes an operator that retrieves the data in accordance with the filter criteria. The contents of the grid are cleared, and it is populated only when the task is completed. This may take some time, so the VCL thread does not wait for the task to complete. Instead, the user can even change the filter criteria again, in which case the current task is canceled and a new one is started. OTL gives you an event to complete the task, so asynchronous design is easy to achieve.

What is the best way to get database results from db stream to main stream?

I usually do not use components that support data for multi-threaded db applications, but I use standard controls, which are representations for business objects. In database tasks, I create these objects, put them in lists, and the task completion event transfers the list to the VCL stream.

The main thread and the db thread will never access the request at the same time.

With all the components that load data on demand, you cannot be sure of that. Often, only the first records are extracted from db, and sampling continues after they are consumed. Such components, obviously, should not share threads.

Answer 2

What you are looking for is a standard data access technology called asynchronous query execution . Some data access components implement this function in a simple way. At least dbGo (ADO) and AnyDAC implement this. Let's look at dbGo.

The idea is simple - you name convenient methods of a data set, for example Open. The method starts the requested task in the background thread and returns immediately. When the task is completed, the corresponding event will be fired, notifying the application of the completion of the task.

The standard approach with GUI DB applications and the Open method is as follows (draft):

• Include eoAsyncExecute, eoAsyncFetch, eoAsyncFetchNonBlock in the ExecuteOptions dataset;
• disconnect TDataSource.DataSet from the data set;
• install the OnFetchComplete dataset for proc P;
• show "Hello! We are doing hard work to process your requests. Wait ...".
• calling the open data set method;
• when the query is completed, OnFetchComplete will be called, therefore P. And P hides the "Wait" dialog and connects the TDataSource.DataSet to the dataset.

There may also be a Cancel button in your Pending dialog box, which the user can use to cancel a request that is too long.

Answer 3

I implemented both strategies: creating a thread pool and creating adhoc threads.

I suggest starting with creating an adhoc stream, which is easier to implement and easier to scale.

Go only to the thread pool if (with a careful assessment) (1) there are a lot of resources (and time) invested in creating the thread, and (2) you have a lot of creation requests.

In both cases, you have to deal with passing parameters and collect the results. I suggest extending the stream class with properties that allow data to be transmitted.

Refer to the documentation of classes, components, and functions that use a thread to make sure they are thread safe, that is, they can be used simultaneously from different threads. If not, you will need to synchronize access. In some cases, you may find slight differences in thread safety. See DateTimeToStr for an example.

Answer 4

If you create your stream at startup and reuse it later when you need it, you must make sure that you disconnect the db components (grid ..) from the underlying data source (disableControls) every time you “process” the data.

For simplicity, I would inherit TThread and implement all the business logic in my class. The result dataset would be a member of this class, and I would connect it to db to write it in sync.

In any case, it is very important to delegate as much work as possible to the db server and to simplify the interface as much as possible. Firebird is my favorite db server: triggers, for favorites, custom UDF DLLs developed in Delphi, a lot of thread-safe db components with lots of examples and good support (forum): jvUIB ...

Luck