Statement.getGeneratedKeys not always returning the keys

[Thomas]

Hi,

I've got a trivial piece of code:

---

public synchronized long insertVertex() throws SQLException
{
Statement st = con.createStatement();
st.executeUpdate("INSERT into vertices () VALUES () ",
Statement.RETURN_GENERATED_KEYS);
ResultSet results = st.getGeneratedKeys();

if (results.next())
{
return results.getLong(1);
}

throw new SQLException("Could not retrieve latest vertices.id");
}

---


Now the issue is that this mostly returns the generated index when
executed quickly in succession, but not always. I tried to create a
testcase, with two threads, but have failed to recreate the issue thus
far.
I am thus not able to reliably retrieve the generated indices. This
issue occurs with multiple tables, but also with a table with a single
column called `id':

---
CREATE TABLE vertices (id IDENTITY PRIMARY KEY);
---

This is with h2 version 1.3.165. I've looked at the changelog of
1.3.166, but didn't see any relevant fixes wrt my problem. It looks
like some kind of concurrency issue, but I don't know how to address
it with h2. I'm running an application that does multiple updates,
delete from's and inserts at the same time in different threads on
multiple tables, but the affected method is synchronized, so I don't
understand why that would be an issue.

java version "1.7.0_03"
Java(TM) SE Runtime Environment (build 1.7.0_03-b04)
Java HotSpot(TM) 64-Bit Server VM (build 22.1-b02, mixed mode)

Thanks in advance,
Thomas

[Noel Grandin]

What does your JDBC URL looks like?
ie. what parameters are you using.

[Thomas]

        Class.forName("org.h2.Driver");
        this.con = DriverManager.getConnection("jdbc:h2:"+dbname, "sa", "");
        con.setAutoCommit(true);

I just set it once when getting the connection.
That's it, nothing fancy.

Op dinsdag 17 april 2012 11:00:30 UTC+2 schreef Noel Grandin het volgende:

[Noel Grandin]

If you are sharing a session/connection between 2 threads, and two of those threads try to execute statements at the same time, then you might see this kind of problem.

You probably need to either (a) use a connection pool or (b) synchronise your entire access to the DB.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/93IGWW7-65sJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Thomas]

Understood.
So I'm currently synchronizing the methods that modify this particular table. (It would make perfect sense to have concurrency issues without this).
Multiple concurrent reads shouldn't be an issue I presume, or do these also require locking and is that causing my issue?

Thanks.

To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

[Noel Grandin]

The problem is that the data is stored on a per-session basis, not on a per-table basis.

In general, JDBC is not guaranteed to be thread-safe, so calling the same connection from multiple threads is going to cause you more problems elsewhere eventually.

I seriously suggest you use a connection pool.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/16KF_dXhg5EJ.

To post to this group, send email to h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database...@googlegroups.com.

[Thomas]

Alright, it's now much clearer to me what is required in multi-threaded situations with JDBC.
Thanks.