Open a database more than once in different instances

May 29, 2013 at 5:55 PM
In the ESE documentation it says that if you open a database read only, then it can be opened by multiple processes. I am trying to open a database twice in the same process but using different instances. This fails with a JET_errDatabaseSharingViolation . This seems illogical, is it possible to open a database twice in the same process but using separate instances?
Developer
May 30, 2013 at 1:52 AM
It sure does look that way. We have a single global table internally with the names of all of the different databases currently attached. Each entry in the table can only belong to a single instance. In the multi-process case, they do not conflict.

Do you have a scenario in mind? There may be another way to do it...

-martin
May 30, 2013 at 8:17 AM
Thanks for the prompt reply Martin

my scenario is that I have a large number of databases created and maintained by different organisations that are clustered together to perform different tasks or views of a problem. So simplistically I have one database that references many others and presents the data they contain as a consolidated dataset. I cannot merge these into a single database for commercial reasons. Any database may be multiply referenced at the same time. Until recently I had a single instance for my process until I hit the limit of the number of open databases per instance (6 or 7 I believe). I moved to one instance per database to allow up to 1024 opened and hit this problem.
I guess I could implement my own table of open databases and manage the instances this way?
Developer
Jun 4, 2013 at 7:38 PM
Since they're being opened read-only, you don't need transactional consistency / isolation. You could open them once with a read-only instance, and then share that instance.

JET_SESID sesidUpdatable; // Used to update tables in database A.
JET_SESID sesidReadOnlyOrg1; // Used to read from database B.
JET_SESID sesidReadOnlyOrg2; // Used to read from database C.

It would be a bit more cumbersome because you have multiple sesid's to keep track of, each of which are independent. And it may add complexity to your code to determine which instance to refer to for which sesid, but it ought to be technically possible.

Does that make sense? (I know this response is a bit rushed.)

-martin
Jun 5, 2013 at 7:39 AM

I have done this martin and it works just fine thanks, it wasn’t too hard as I was already keeping session ids separately tracked.

Essentially I keep a count of how often a database is opened and decrement when I closed, only really detach when the count is 0

Its just fine but I must admit it would be nice if we could just open read only databases willy nilly J