Keeping database access off the main thread

BIZ TMS Aurelius
1

I'm trying to replicate something I did with Android studio in Delphi. In Android studio I did a project to pull down data from a REST server and use it in the GUI saving it to a local SQLite database to allow it to work off line. Android Studio made it very difficult to run queries against the database in the main thread.

The advice was to use the ORM to query the database and then map the results from the ORM's entities into domain entities. Same when doing requests to the REST server. Kotlin makes managing the lifetime of your entities easy in Aurelius the manager does that.

My idea was to create a manager in the main thread and create a new manager in each thread with OwnsObjects set to false. Then add the objects to the main thread manager so that I could use them in the GUI and have that manager manage the lifetime of the entities.

I noted what was said has been said about initialising the MappingExplorer before using a thread. This doesn't appear to be working as I expected. I am getting some memory leaks when I close the app down from .

---------------------------
Unexpected Memory Leak
---------------------------
An unexpected memory leak has occurred. The unexpected small block leaks are:

13 - 20 bytes: Unknown x 4
21 - 28 bytes: UnicodeString x 1
29 - 36 bytes: TDriverProject x 1, UnicodeString x 2
37 - 44 bytes: UnicodeString x 4
53 - 60 bytes: TProjectItem x 2, TAssociationProxyController x 4
61 - 68 bytes: TInternalProxyInfo x 4
69 - 76 bytes: TStringList x 4

---------------------------
OK   
---------------------------

I would welcome any advice how others may be approaching this problem.

Here's an example of the code I am using to get a TList of entities and use it with an Aurelius dataset in the GUI.

tprocedure TdmOrganisation.LoadProjects( OnLoaded: TProc< TList< TProject > > );
begin
  if Assigned( OnLoaded ) then
  begin
    FreeAndNil( FProjects );
    var
    Explorer := TAppUtils.GetInitialisedExplorer;
    TTask.Run( procedure
      begin
        try
          var
          M := TObjectManager.Create( Pool.GetPoolInterface.GetConnection, Explorer );
          try
            M.OwnsObjects := False;
            FProjects := M.Find< TProject >.List;

            TThread.Synchronize( nil, procedure
              begin
                for var Project in FProjects do
                  FManager.AddOwnership( Project );
                OnLoaded( FProjects );
              end
            );

          finally
            M.Free;
          end;

        except
          on E: Exception do
            TMSLogger.ExceptionFormat( 'Load Projects: {%s},{%s}', [ E.classname, E.message ] );
        end;
      end
    );

  end;
end;
2

Hi Steve,

The explorer initialization only needs to happen once in the beginning of the application and it's not related to the memory leaks.

Your code doesn't take into account the associated objects, if the TProject entity does have associations for TDriverProject and TProjectItem, that's the reason of the issues. Also if you have lazy-loaded entities, the manager must be "alive" at the time the lazy-loaded properties are loaded.

I personally think it's easier if you simply keep the manager alive while you still have the list. I mean, keep your code similar to what you have, but instead of destroying the manager, simply keep a reference to it and keep it alive while you have the form open or until you reload the projects (and thus destroy the FProjects list)

3

Hi Wagner,

Thanks for getting back to me so quickly.

I don't get how that will work (I'm a slow learner :frowning: ).

I'm trying to present the user with different lists of things and save what they pick in another entity. Later I will want to save that entity back to the database. If I create a different TObjectManager for each pick-list I show, and not move the entities into the main thread's object manager I suspect I am going to get into a right old muddle.

I just don't see how I can easily keep the database access off the main thread because I have to create and destroy the TObjectManager I use to access the database within that thread. Perhaps I'm over thinking it.

Should I be thinking about creating a TThread descendant that would work as a kind of repository that would take and give me entities and manage the lifetime of them?

Regards

Steve

4

FYI https://developer.android.com/training/data-storage/room/async-queries
Room is Google's ORM, the link above explains how they insist on background threads for queries because Android will try and kill your app if you block the main thread for too long

5

You should "move" the entities into the main thread. But also "move" the manager that holds the threads, so you don't need to handle the memory management and evicting of all entities.

In a thread, create a new manager, retrieve the list, and then "send" the manager to the main form to be used by the main thread. Don't destroy the manager until you know you are done with the entities it holds.

6

Here's what "I think" I know and what I am finding challenging. Please let me know if I have made a false assumption.

  1. The manager owns the connection but as I understand it we need to have a separate connection for each thread.

  2. The manager will automatically query the database when it wants. For example to load a proxied list. It normally does this in the main thread.

  3. Android would prefer us to access the database form a separate thread.

  4. FMX controls and Live Bindings don't know how to page data.

In my mind what you suggest would work the first time I have to run an expensive query but what would happen the next time I have to do it. How can I make it work asynchronously? Use a critical section perhaps? I'm sorry Wagner perhaps I'm just too thick to get this but I can't be the only one who is struggling with it.

That's why I was trying to work with two managers, a short lived one to query the database and a longer lived one to manage the lifetime of the objects. I think I might have been too precious in my desire to eliminate all queries from the main thread.

Maybe if I just used Manager.Merge( MyEntity) after the user picks one from the list and then free the manager I created in the thread it would give me close to what I desire.

On a side point to anyone else reading this. I think we might be able to page data in FMX if we use the TMS FNC database adapter. I need to explore that further but it could be a much simpler way of getting round this fear I have of blocking the main thread in Android. However that would mean I would have to use FNC controls like TMSFNCTableView rather than a TListView and live bindings.

7

The manager owns the objects (entities) it knows about. That means when the manager is destroyed, it will destroy all the entities it "owns". It doesn't own the (database) connection - if that's what you mean. You can create multiple managers using the same database connection (same IDBConnection interface),

The manager might query the database to load lazy-loaded proxies, yes. That's the only situation where the manager queries the databases without you asking it explicitly ().
() Well, you are doing it "explicitly", when you read a proxied property. For example, when you do Customer := Invoice.Customer, and `Customer association is not loaded, the manager will perform the query at that time.
It's executed in the same thread as you are running the code that reads the property.

Yes.

I can't comment on that. I'm not expert on FMX or Live Bindings. I usually avoid it to have full control about what I'm doing and not have to fight the framework.

It's not much different from what you are doing. I just can't give you a full code because of course it's your business logic, but the idea is more or less this one (not tested):

procedure TdmOrganisation.LoadProjects( OnLoaded: TProc< TList< TProject > > );
begin
  if Assigned( OnLoaded ) then
  begin
    ProtectedCode(procedure
      begin
        FreeAndNil(FProjects);
        FreeAndNil(FManagerHoldingTheProjects);
      end;
    var
    TTask.Run( procedure
      begin
        try
          var
          M := TObjectManager.Create( Pool.GetPoolInterface.GetConnection, Explorer );
          try
{            M.OwnsObjects := False;}
            var LocalProjects := M.Find< TProject >.List;

            ProtectedCode(procedure
              begin
                FProjects := LocalProjects;
                FManagerHoldingTheProjects := M;
                OnLoaded( FProjects );
              end
            );

          finally
{            M.Free;}
          end;

        except
          on E: Exception do
            TMSLogger.ExceptionFormat( 'Load Projects: {%s},{%s}', [ E.classname, E.message ] );
        end;
      end
    );

  end;
end;

I hope you get the idea. The process of loading database data will stay in thread.
But then, after it finishes, you pass the list and the manager to the main form, to be used from the main thread. Note I explicitly commented the parts of the code that destroy the manager and that disables the OwnsObjects. Keep it alive as usual.

When you destroy the list, destroy the manager as well. Same from the main thread when you close the form. That means when the manager is destroyed, all the objects will be destroyed too.

Then for thread-safety, all you have to do is protect the code that access the same variables from multiple threads, so they don't conflict. Since FProjects and FManagerHoldingTheProjects can be modified from both the main thread and the database thread, protect access to it. I created a generic ProtectedCode method for explaining purposes, but there you should just use a TMonitor or TCriticalSection to protect such code.

I hope it's clearer now.

8

Thanks once again for your help Wagner, much appreciated.

1 Like
9

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.