Multiple independent H2 databases within one JVM

[Basil Bourque]

Is it possible to start up and shut down multiple H2 databases within a JVM?

My goal is to support multi-tenancy by giving each user/account their own database. Each account has very little data. Data between the accounts is never accessed together, compared, or grouped; each account is entirely separate from the others. Each account is only accessed briefly once a day or a few times a month. So there are few upsides to housing the data together in a single database, and some serious downsides.

So my idea is that when a user logs in for a particular account, that account’s database is loaded. When that user logs out, or their web app session (Vaadin app) times out, that account’s database is closed, it's data flushed to storage, and possibly a backup performed. This opening and closing would be happening for any number of databases in parallel.

Benefits include minimizing the amount of memory in use at any one time for caching data and indexes, minimizing locking and other contention, and allowing for smooth scaling.

I'm new to H2, so I'm not sure if its architecture can support this. I'm asking for a denial or confirmation of this capability, along with any tips or caveats.

----

Taken from my Question on StackOverflow.com. 

[Ryan How]

Yep no probs at all. You can have multiple databases open at the same time and open and close them on different threads as you like.

You'll just need to do the appropriate connection handling linked to the session open / close.

Scaling is going to depend on your application architecture too. If you are horizontally scaling it isn't going to work with embedded databases?. Or are you using TCP and having a h2 server on a different server?

The downside I can think of is that you can't query all databases for any non-user tasks. Have to update all schemas individually, etc. So on each session start it is going to almost be like starting a separate application.

Probably fits quite nicely with vaadin architecture. Just link it to the Application start and end (Don't they call a session an application?)


Ryan

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Noel Grandin]

Just note that we load all metadata for a given database into memory at startup.

Some people opening lots of little databases that spend most of their time inactive have experienced memory problems
because of this.

Other than that, it should work fine.

Let us know if you run into any performance issues.

[Basil Bourque]

Is that metadata all unloaded upon closing that particular database? I expect to shut down any database not currently in use by a user.

[Noel Grandin]

Yes

[Basil Bourque]

Regarding Vaadin, the "Application" class/idea was one of the major changes in Vaadin 7 from Vaadin 6. That class/idea are gone. Now we have the much more flexible "VaadinSession" that wraps a Servlet session, and owns one or more instances of one or more "UI" classes. Each "UI" instance is the entire content present in a web browser's window/tab (or a Portlet). In other words, your Vaadin app can have multiple open browser window/tabs running simultaneously. I made a diagram of this architecture. This Question and this Question on StackOverflow may be useful, as well as the Application Lifecycle chapter of The Book Of Vaadin.

You could hold one reference/connection to H2 in the VaadinSession for all the "UI" instances (all the open windows). Or each "UI" instance could have its own H2 reference/connection. I don’t know enough about H2 yet to know which is appropriate.

There are hooks for the creation & destruction of the VaadinSession, where I would be starting/stopping that user's own H2 database.

[Ryan How]

I'd be inclined to have a connection pool per session. Then create and destroy the connection pool. That way it will manage connections however you want to use them and you don't have to keep a control connection open on the database.

But there are so many ways to do it!. It always confuses me which is the "best" one :)

Ryan

[Silvio]

That is exactly why I use H2. Our systems or made up of independent scripts that implement subsystems and are completely self-containing, meaning they implement their own (Web) user interfaces and contain their own resources and database. Such subsystems can just be copy/pasted to create new ones based on existing ones.

I have developed a two-level JDBC connection cache for this. Connections are accessed by key (driver, url, user, password) and the cache limits the total number of open connections (and optionally the number of open databases/keys and the number of connections per database/key).

I do not think using a connection pool per session is a good idea...