title: H2 In memory database inconsistent when using threads
body: <p>I've been sitting on this problem from several hours and am absolutely stumped as to what is going on.</p>
  
  <p>Basically I am running a Swing Worker task in an ExecutorService thread pool and passing an in memory H2 database to test some database interactions. What's happening is a situation where I'm getting a referential integrity error <em>sometimes</em> as a result of inserting a SnapshotFV into the db when there needs to be a Snapshot inserted first. </p>
  
  <p>Now if I break right before executing the threads, I can check the database and see that the Snapshot row does exist in the database, however if I break within in the doInBackground method on the line that produces the problem, that row no longer is in the database, even though the datasource object has the same object Id as before. The H2 database seeming to have reset at some point in the chain. It's hard to tell but it seems like it doesn't necessarily happen everytime when I debug, however it definitely happens everytime if I just run.</p>
  
  <p>If I set the thread pool to 1, no problems.</p>
  
  <p>During debugging I've checked all the object ID's to make sure nothing weird is happening with something getting set to null or something. The datasource and snapshot IDs are the same </p>
  
  <pre><code>    ExecutorService taskExecutor = Executors.newFixedThreadPool(8);
  
      for (File f : files) {
           FVGatherer task = new FVGatherer(f, dataSource, snapshot);
           taskExecutor.execute(task);
      }
  
      taskExecutor.shutdown();
      try {
          taskExecutor.awaitTermination(60, TimeUnit.MINUTES);
      } catch (InterruptedException e) {
          ...
      }
  
  
      ...
  
  
      private static class FVGatherer extends SwingWorker&lt;Void, Void&gt; {
          private final File f;
          private final XDataSource dataSource;
          private final Snapshot snapshot;
  
          ...
          constructor
          ...
  
          @Override
          protected Void doInBackground() throws Exception {
              FV fv = getFV(f, dataSource);
              FI fi = getFI(f, dataSource);
  
              if (fv != null &amp;&amp; fi != null) {
                  SnapshotFV sfv = dataSource.createSnapshotFV(snapshot, fv, fi);
                  snapshotFVs.add(sfv);
              }
  
              progressIncrementer.run();
              return null;
          }
  </code></pre>
  
  <p>The erorr:</p>
  
  <blockquote>
    <p>13:25:20.021 [pool-4-thread-1] ERROR JdbcUtilities - catching
    org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: 
    "FK_SNAPSHOTFV: PUBLIC.SNAPSHOTFV FOREIGN KEY(SNAPSHOT_ID)
    REFERENCES PUBLIC.SNAPSHOT(ID) (1)"; SQL statement:
    insert into SNAPSHOTFV (SNAPSHOT_ID, FV_ID, FI_ID) values (?, ?, ?) [23506-175]</p>
  </blockquote>
  
  <p>Any ideas?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Another detail that I just noticed. The getFV and getFI also insert objects into the database and if I have thread pool set > 1 then then they as well seem to not show up in the database in the doInBackground method. Again, set the thread pool to 1 and break in the same place and the FV, FI, and snapshot appear in the database. Could it be that these methods are causing some exception or issue that is not being reported that invalidates and as a result resets the in memory database? I can post the code for these methods if requested. </p>
  
  <p><strong>EDIT2</strong></p>
  
  <p>Failing on line:</p>
  
  <pre><code>SnapshotFV sfv = dataSource.createSnapshotFV(snapshot, fv, fi);
  </code></pre>
  
  <p>Stack trace:</p>
  
  <pre><code>at org.h2.message.DbException.getJdbcSQLException(DbException.java:332) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.message.DbException.get(DbException.java:172) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.message.DbException.get(DbException.java:149) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.constraint.ConstraintReferential.checkRowOwnTable(ConstraintReferential.java:368) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:310) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.table.Table.fireConstraints(Table.java:894) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.table.Table.fireAfterRow(Table.java:911) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.command.dml.Insert.insertRows(Insert.java:162) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.command.dml.Insert.update(Insert.java:115) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.command.CommandContainer.update(CommandContainer.java:79) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.command.Command.executeUpdate(Command.java:253) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:154) ~[h2-1.3.175.jar:1.3.175]
  at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:140) ~[h2-1.3.175.jar:1.3.175]
  at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) ~[commons-dbcp2-2.1.jar:2.1]
  at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) ~[commons-dbcp2-2.1.jar:2.1]
  at data.sources.JdbcUtilities.insertItem(JdbcUtilities.java:92) [bin/:?]
  at data.sources.SnapshotFVSource.create(SnapshotFVSource.java:61) [bin/:?]
  at data.PooledDataSource.createSnapshotFV(PooledDataSource.java:347) [bin/:?]
  at util.SnapshotVersionUtil$FVGatherer.doInBackground(SnapshotVersionUtil.java:262) [bin/:?]
  at util.SnapshotVersionUtil$FVGatherer.doInBackground(SnapshotVersionUtil.java:1) [bin/:?]
  at javax.swing.SwingWorker$1.call(Unknown Source) [?:1.8.0_60]
  at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_60]
  at javax.swing.SwingWorker.run(Unknown Source) [?:1.8.0_60]
  at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_60]
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_60]
  at java.lang.Thread.run(Unknown Source) [?:1.8.0_60]
  </code></pre>
  
  <p><strong>Edit 3</strong></p>
  
  <p>This problem seems to only exist for me when using the in-memory database. A physical disc database seems to work. Hmm...</p>