How to properly access query-results created in background thread?

Question

I want to execute a database query in a background thread. The OmniThread library will help me with all the thread stuff, but there is one thing I don't understand so far:

Every thread needs a separate database connection. The background thread therefore creates the DB connection, creates the query and then executes it.

Now I could access the query results using the query object of the background thread.
But after the query is executed, I want to access the query results in the main thread.

If I just refer to the background-thread query object, does this cause problems because I am accessing a DB connection in another thread?

As I understand, in this case the main thread would not have it's separate DB connection and use the one from the background thread which is not good.

Where is my thinking distorted and what's the right way to do this?

Answer 1

The best way is probably to not use db-aware components in the GUI. Threads should communicate with the database and store information in business objects, which can be then sent to the main thread (which will display them).

Multithreading is hard, not only from the implementation perspective but also from the view of the application design. It's usually best if you think of the background threads as a separate layer with well-defined inputs and outputs.

Answer 2

If your OTL task needs to load a sorted list of companies that match the criteria:

// create and open query to fetch list of companies
while not qryCompanies.Eof do begin
  C := TCompany.Create;
  try
    C.LoadFromDataset(qryCompanies);
    Companies.Add(C);
  except
    C.Free;
    raise;
  end;
  qryCompanies.Next;
end;

C is your business object for a company. It may by an object (TCompany) or an interface (ICompany) implemented by the object. Companies is a TList<TCompany> or TList<ICompany>. At the end of the task you send the list of companies to the VCL thread:

Task.Comm.Send(TOmniMessage.Create(MSGID_LIST_OF_COMPANIES, Companies));

In the form where you want to display the list of companies you handle the OnTaskMessage event of the otlEventMonitor instance that is monitoring your task:

procedure TListBaseFrame.otlEventMonitorTaskMessage(
  const task: IOmniTaskControl);
var
  MsgID: word;
  MsgValue: TOmniValue;
begin
  task.Comm.Receive(MsgID, MsgValue);
  Assert(MsgValue.IsInterface);
  if fLoaderTask = task then begin
    SetLoadedData(MsgID, MsgValue.AsInterface); // or MsgValue.AsObject);
    fLoaderTask := nil;
  end;
end;

The list of companies replaces the previous list and can be displayed in the grid.

Similarly you could return a single company object / interface to be displayed and edited.

Two things that are worth thinking about:

• If you have so far preferred objects to interfaces, writing multi-threaded programs may be a reason to reconsider that. If you create your objects in a background thread, then pass them to the VCL thread and forget about them in the background thread, then objects may work well. I found however that much better performance can be had by caching objects in the application, and loading only records from the database that haven't been loaded yet, or that have changed. All my tables have a change index (64 bit integer, a time stamp may work as well) attached to it, that is changed with every update. Instead of executing a

  select * from foo where (...) order by (...)
  

  I only ever execute a

  select id, change_index from foo where (...) order by (...)
  

  then check in the cache whether an object with the same id (primary key) and change index already exists, if so return the cached object, and only if not create a new business object and load all columns.

  But if you cache objects you will have references to them from multiple threads, and ownership issues soon get so complicated that lifetime management based on reference counts is the only way to stay sane. Using interfaces instead of objects helps a lot in this regard.

• Adding a synchronization object to each business object is necessary, if multiple threads can access them concurrently. It is of course possible, but may introduce additional complexity and potential deadlocks. If you implement your business objects as immutable, then no locks are needed at all. I am using that approach more and more, and while it takes some getting used to it it can simplify things a lot.