T. Kryazh T. Kryazh - 11 months ago 42
SQL Question

Getting JdbcBatchUpdateException while testing with EmbeddedDatabase H2

I get strange exception trying to test my code using EmbeddedDatabase H2:

java.lang.RuntimeException: org.h2.jdbc.JdbcBatchUpdateException: Timeout trying to lock table "CO_SCENARIO_1"; SQL statement:
at de.telekom.skses.test.dao.DatabaseFileConverterTest.fromFile(DatabaseFileConverterTest.java:99)

This is part of code I'm trying to test:

case "overriddenVariables.txt": {
try (Scanner sc = new Scanner(bOut.toString())) {
try (Connection con = dataSource.getConnection()) {
String aQuery = "UPDATE $tableName SET VALUE = ? WHERE ATTRIBUTE = ? AND MODIFIER = ?";
String line = "";
if (sc.hasNext()) {
line = sc.nextLine();
while (sc.hasNext()) {
if (line.startsWith("+")) {
String setting = line.substring(1);
try (PreparedStatement ps = con.prepareStatement(aQuery.replace("$tableName", setting))) {

while (sc.hasNext() && !(line = sc.nextLine()).startsWith("+")) {
int del1 = line.indexOf(":");
int del2 = line.indexOf(':', del1 + 1);
String attr = line.substring(0, del1);
String modifier = line.substring(del1 + 1, del2);
String value = line.substring(del2 + 1, line.length());
Clob myClob = con.createClob();
myClob.setString(1, value);
ps.setClob(1, myClob);
ps.setString(2, attr);
ps.setString(3, modifier);


} catch (SQLException e) {
logger.error("Error", e);
throw new RuntimeException(e);
} catch (Exception ex) {
logger.error("Error", ex);
throw new RuntimeException(ex);


StackOverflow says that I should set lock timeout, but I didn't find how to set it for EmbeddedDatabase. Also, in previous version I opened new PreparedStatement for each query and new Connection for each PreparedStatement, everything worked well, and I can't understand why it doesn't now. Could you please explain what I have to do to make it work again?

Sorry if something is incorrect, I'm new to Java EE.

Answer Source

As I remember using H2 in embedded mode, it has problems with concurrency. this is probably why you get the timeout for the lock on the table, since the table is still updated from your last batch when you try to execute the next one. When closing connection and preparedStatement every single time the execution time is way shorter, so you won't get the timeout.

Have you tried adding to the DatabaseURL ;MVCC=true as stated in this post? If this won't solve the problem, is it really necessary to use batches for your update?

EDIT: the post i linked before, links to the h2 website where you can find a description on how to change the lock timeout by adding ;LOCK_TIMEOUT=10000 to your databse URL. This would change it to 10 seconds, standard is 1 second