How to begin multiple sessions with Extensible Storage Engine

Question

In the documentation for JetBeginSession, it notes that the session is the unit of granularity that defines a transaction, it defines where the cursor is in your currently open table, it defines the currently active index. On one session nothing else can be done. But they do note:

To increase concurrency and parallel access to the database, multiple sessions can be begun.

Which is what i would like. I would like to open a second session to the database.

Background

The ESE is somewhat convoluted in how you get it going:

JetCreateInstance(out instance, "UniqueInstanceName"); //Create a uniquely named instance of the ESE in our process

JetInit(instance); //initialize the instance
   JetBeginSession(instance, out sessionID); //initialize a session on the instance
      JetAttachDatabase(sessionID, filename); //attach a database file to our session
         JetOpenDatabase(sessionID, filename, "", out databaseID, 0); //open the database file in our session

            //...now we can open table, get data, etc
            //E.g. JetOpenTable(sessionID, databaseID, "Customers", null, 0, JET_bitTableReadOnly, out tableID);

         JetCloseDatabase(sessionID, databaseID);
      JetDetachDatabase(sessionID, filename);
   JetEndSession(sessionID, 0);
JetTerm(instance);

And that all works.

But how do we open another session?

The ESE documentation, while sparse, does hint at the ability to have multiple sessions going for the same database:

• To increase concurrency and parallel access to the database, multiple sessions can be begun.
• JET_bitTableDenyRead - The table cannot be opened for read-access by another database session. (implying that sometimes it can be opened for read-access by another session)
• JET_bitTableDenyWrite - The table cannot be opened for write-access by another database session. (implying that sometimes it can be opened for write-access by another session)
• JetOpenDatabase - This function can be called multiple times for the same database.
• JET_bitDbExclusive - Allows only a single session to attach a database. Normally, several sessions can open a database. (emphasis mine)

The naive approach would be to begin another session:

//Startup the instance
JetCreateInstance(out instance, "UniqueInstanceName");
JetInit(instance);

   //Make first session
   JetBeginSession(instance, out sessionID);
   JetAttachDatabase(sessionID, filename);
   JetOpenDatabase(sessionID, filename, "", out databaseID, 0);

      //Startup second session
      JetBeginSession(instance, out session2ID);
      JetAttachDatabase(session2ID, filename);
      JetOpenDatabase(session2ID, filename, "", out database2ID, 0);

      //Teardown second session
      JetCloseDatabase(session2ID, database2ID);
      JetDetachDatabase(session2ID, filename);  <----hangs
      JetEndSession(session2ID, 0);

   //Teardown first session
   JetCloseDatabase(sessionID, databaseID);
   JetDetachDatabase(sessionID, filename); 
   JetEndSession(sessionID, 0);

//Terminate instance
JetTerm(instance);

Except the call to JetDetachDatabase in the second session hangs.

• Checking the documentation of JetDetachDatabase gives no help.
• Checking theJetAttachDatabase we come across something worrying.

There's an error code the implies it is an error to try to attach a database that has already been attached by a different session:

JET_errDatabaseSharingViolation: The database file has already been attached by a different session.

How to open multiple sessions?

So now rather than flail about too randomly, i'll ask for the correct way to do it.

How to begin multiple sessions with Extensible Storage Engine

Bonus

I did flail about somewhat randomly. If the call to JetDetachDatabase is hanging, lets just not call it! It completely violates the documented rules:

• JetOpenDatabase says i must call JetAttachDatabase first
• JetAttachDatabase says i must call JetDetachDatabase

But trying it:

//Startup the instance
JetCreateInstance(out instance, "UniqueInstanceName");
JetInit(instance);

   //Make first session
   JetBeginSession(instance, out sessionID);
   JetAttachDatabase(sessionID, filename);
   JetOpenDatabase(sessionID, filename, "", out databaseID, 0);

      //Startup second session
      JetBeginSession(instance, out session2ID);
      JetOpenDatabase(session2ID, filename, "", out database2ID, 0);

      //Teardown second session
      JetCloseDatabase(session2ID, database2ID);
      JetEndSession(session2ID, 0);

   //Teardown first session
   JetCloseDatabase(sessionID, databaseID);
   JetDetachDatabase(sessionID, filename); 
   JetEndSession(sessionID, 0);

//Terminate instance
JetTerm(instance);

And it actually seems like it happens to work.

...yay?

Answer 1

What you did in your "flailing" was the correct way to access the database from multiple sessions.

JetAttachDatabase() opens the file and associates it with an instance. It only needs to be called once.

JetOpenDatabase() opens a handle to the database within the session and can be called on each session.