What does MV_STORE = false mean when connecting to H2 database?

Question

I am working on an Embedded database w/ a GUI to communicate with it.

I am curious what the MV_STORE = false means in my URL?

public DBconnect() throws SQLException, ClassNotFoundException{
        clients = FXCollections.observableArrayList();
        Class.forName("org.h2.Driver");
        c = DriverManager.getConnection
                ("jdbc:h2:~/Database;MV_STORE=false", "admin", "Fitness1");
    } 

Weiland

h2database.com/html/mvstore.html#storageEngine – Pascoe

Good question. But please use proper English when posting here: “I am” or “I’m”, not “im”. This site is meant to be more like Wikipedia, less like a casual chat room. I edited your text this time. – Sonya

Answer 1

Storage engine

H2 has offered more than one storage engine for saving data.

➥ MVStore is one of those storage engines, the latest, and the current default.

To quote the thorough documentation page on MV_STORE:

Storage Engine for H2

For H2 version 1.4 and newer, the MVStore is the default storage engine (supporting SQL, JDBC, transactions, MVCC, and so on). For older versions, append ;MV_STORE=TRUE to the database URL.

Presumably, setting that flag to false engages the alternative storage engine.

Choosing MVStore or not

As for why you might choose to use or avoid MVStore, I do not know the details. A search engine turned up this old Google Groups post in 2015-08 by the name Thomas, so I wonder if this might be Thomas Mueller, the inventor of H2.

Hi,

The MVStore is relatively new and not yet as mature as the old storage mechanism (the PageStore). See also the documentation at http://h2database.com/html/mvstore.html . Some advantages compared to the PageStore are: multi-version, simpler, more concurrent, writes less, optimized for SSDs. Disadvantage is that it temporarily needs more disk space, and is currently a bit slower.

The PageStore is quite mature and will be supported in the future for some time. However, support will be phased out eventually.

Regards,

Thomas

I assume that now in 2020 versus 2015, and as the default for H2 version 1.4, the part about “new and not yet as mature” is no longer the case.

Multiversion concurrency control (MCC or MVCC)

From some quick searching and reading, it seems a major difference between the newer MVStore and the older PageStore is support for multiversion concurrency control (MCC or MVCC).

• MVCC support is a key feature of the newer MVStore, and is always enabled.
• MVCC support in the older PageStore was experimental, and eventually dropped.

See Issue pages:

• Always use MVCC with MVStore and never use it with PageStore #1204 opened 2018-06.
• Remove useless MVCC / no MVCC conditions from MVStore code #1209

---

By the way, using a javax.sql.DataSource implementation is generally recommended for getting connections rather than using DriverManager as seen in your sample code.

H2 provides the org.h2.jdbcx.JdbcDataSource class as such an implementation. See this documentation page for the list of options you can set.

JdbcDataSource ds = new org.h2.jdbcx.JdbcDataSource();
ds.setURL( "jdbc:h2:~/Database" );
ds.setUser( "scott" );
ds.setPassword( "tiger" );
return ds ;  // Return as the more general `javax.sql.DataSource` rather than H2-specific implementation.

Sonya

What would be the alternative storage engine and in what circumstance would one set the MV_STORE to false? – Weiland

@Weiland I added another section to address your comment. – Sonya