[h2] Generated keys not returned if sharing a connection across threads

[u . g . dietrich]

If sharing a connection across multiple threads, I sometimes get a "No data 
is available [2000-175]" if returning a generated key. The same code runs 
fine if I use one connection per thread.

*Question:* Is it known and accepted behavior, that connections may not be 
used concurrently from separate threads?

   - If yes, it would be good to state this in the h2 documentation, since 
   the topic arises from time to time, see for example 
   
https://groups.google.com/forum/#!searchin/h2-database/identity$20concurrency/h2-database/za6Ox1mrFIU/P_ZZm59ZiaMJ
   - If no, this can be considered a bug report.

To reproduce, execute the following scala app:


object H2BugReport extends App {

  import java.sql._
  import scala.concurrent.ExecutionContext.Implicits.global
  import scala.concurrent.Future

  Class forName "org.h2.Driver"
  val url = "jdbc:h2:mem:"
  val connection = DriverManager getConnection (url, "sa", "")
  
  connection.createStatement execute """
    CREATE SEQUENCE treeEntriesIdSeq;
    CREATE TABLE TreeEntries (
      id      BIGINT DEFAULT (NEXT VALUE FOR treeEntriesIdSeq) PRIMARY KEY,
      name    VARCHAR(256) NOT NULL
    );
    CREATE TABLE DataEntries (
      id      BIGINT PRIMARY KEY,
      name    VARCHAR(256) NOT NULL
    );
  """

  val insertTree = connection prepareStatement (
    "INSERT INTO TreeEntries (name) VALUES (?);",
    Statement.RETURN_GENERATED_KEYS
  )

  val insertData = connection prepareStatement (
    "INSERT INTO DataEntries (id, name) VALUES (?, ?);"
  )

  Future {  // comment out this block, and the other block executes fine
    (1 to 50) foreach { n =>
      insertData setLong (1, n)
      insertData setString (2, s"$n")
      insertData executeUpdate
    }
  }

  (1 to 50) foreach { n =>
    println(s"inserting $n in tree")
    insertTree setString (1, s"$n")
    insertTree executeUpdate
    val keys = insertTree.getGeneratedKeys
    keys.next
    keys.getLong(1)
  }
}


This will yield:


inserting 1 in tree
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Keine Daten 
verfügbar
No data is available [2000-175]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
    at org.h2.message.DbException.get(DbException.java:172)
    at org.h2.message.DbException.get(DbException.java:149)
    at org.h2.message.DbException.get(DbException.java:138)
    at org.h2.jdbc.JdbcResultSet.checkOnValidRow(JdbcResultSet.java:3111)
    at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3117)
    at org.h2.jdbc.JdbcResultSet.getLong(JdbcResultSet.java:637)
    at 
bugreport.h2.H2BugReport$$anonfun$2.apply$mcJI$sp(H2BugReport.scala:48)


-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.