When working with same connection on multiple threads, db state found is stale

[ea0921]

I am working with multiple threads and all of them uses the same connection and user as well, so when I am reusing the thread, I am performing below operations to refresh the connection object state:

repository.Models.Refresh();
//Refresh view for all packages
repository.RefreshModelView(0);

However, if Thread1 has created an element E1. And even after above refresh statements, if thread2 gets data of elements with sql query, then also E1 is not found in the data state.

Now, in case I explicitly refresh all packages using below code, then only I am able to find E1 element in Thread2 as expected.

public void ReloadAllPackages(EA.Repository repository)
        {
            foreach (EA.Package rootPackage in repository.Models)
            {
                ReloadPackageRecursive(rootPackage, repository);
            }
        }

        public void ReloadPackageRecursive(EA.Package package, EA.Repository repo)
        {
            if (package == null) return;

            // Reload this package
            repo.ReloadPackage(package.PackageID);

            // Recursively reload all subpackages
            foreach (EA.Package subPackage in package.Packages)
            {
                ReloadPackageRecursive(subPackage, repo);
            }
        }


But this will take too much time if too many packages are there and too many elements and diagrams.

Is there any alternate way for refreshing state of connection in different threads when reusing the same connection?

[Geert Bellekens]

That's because your "refresh" isn't actually doing much at all.

If you suspect something has been updated you better dump your objects in memory and reload them in your other thread (using Repository.GetElementByID or something)

In that case you have a fresh element, and things like .Elements will be freshly loaded from the database.

Getting data using an SQL Query should always work. If that doesn't work, it means you object hasn't been saved to the database yet.

Geert

[ea0921]

Getting data with SQL is not working, if explicit refresh is not done.
But just as package reloads are done, we get this data for newly added elements.


My usecase is such that:

Thread1: creates connection and creates an element
Thread2: reuses connection, and now when looking for all elements, it is not able to find Thread1 created element. Even if it is visible on ui as well if logged in with another user. Once, package reload is done, then I am able to find newly created elements

[Geert Bellekens]

Getting data with SQL is not working, if explicit refresh is not done.
But just as package reloads are done, we get this data for newly added elements.

Both actions are not related to eachother.
A refresh affects the state of your collections in memory (refreshing their data from the database)

Getting data from the database is not related to whatever state your objects in memory have.

I'm guessing the reason you are seeing this effect is because the refresh takes a bit of time.
You would probably get the same effect if you simply sleep() for a while

Geert

[Geert Bellekens]

Also, working with multiple threads is tricky.
EA's API was not designed to be multithreading safe, so you will get conflicts if you are not careful.

Geert

[ea0921]

I will check for the sleep().

There is one more case,
Thread1: Created connection with user1
Thread2: Created connection with user2 and added new elements
Now Again Thread1: is active and it fetches all elements from DB then, new elements are not found, unless package reload is done.

Also, I have performed these 2 commands before reusing the older connection:
1. repository.Models.Refresh();
//Refresh view for all packages
2. repository.RefreshModelView(0);

Still, new elements are not found

[Geert Bellekens]

Thread2: Created connection with user2 and added new elements

Technically the moment you call Update() the new elements are being inserted in the database.
From the moment that transaction has been committed you should be able to get these elements in another thread using a database query.

I guess have PCS in between could complicate things a bit since that might do some kind of caching, but other then that, there is nothing more to it.

As I mentioned, the "refresh" methods you are executing are not related to the state of the database, so they are basically useless except for the fact that they take some time to execute, which gives the other tread the time to commit the transaction.

Geert