Working Around H2 Database “Bug”
A few days ago I ran into a serious problem with my usage of the H2 database as an in-memory database for a ton of data. Like we're talking nearly 70 million rows in a day. It's a lot. The problem is that I need to be able to INSERT this same data into (optionally) two databases. This makes is most logical to build up a (large) SQL string and then send it to one, or more, databases through JDBC. Seems pretty simple.
Until it gets too big.
My errors were because I was adding a lot of data to this table. So much that it was blowing out the JDBC Connection. So I put in "breaks" into the SQL string and then chopped up the transactions by those breaks. What I didn't think about then, and did think about this morning was: What if I broke up the statements?
So I tried that.
// break up the SQL into it's component statements String[] parts = null; if (!error) { parts = sql.split("\n;"); if ((parts == null) || (parts.length == 0)) { error = true; log.warn("Unable to break up the SQL into statements!"); } } /** * Now let's make the JDBC Connection and Statement and then * execute each of the statements against it. */ try { ... for (int i = 0; i < parts.length; ++i) { stmt.executeUpdate(parts[i].trim()); } ... } catch (SQLException se) { ... }
I broke up the large SQL by the statement terminations, and then ran through all the statements one after another, until the complete package was done. The effect was dramatic: It worked!
Not just that, it was far better because now we were able to once again maintain the transactional integrity of the INSERTs so that I could use these tables with one another without having to worry about when certain rows were inserted into certain tables. Much better.
So it may still be a bug in H2, but it's easy enough to work around.