Derby - Getting RETURN_GENERATED_KEYS doesn't seem to be thread safe when using the same Connection to create the PreparedStatement

Question

I run two INSERT sql calls one after the other with Statement.RETURN_GENERATED_KEYS each insert has it's own unique ID assigned to it.

Each call is generated with connection.prepareStatement where the connection can be re-used by the different threads (connection pool or embedded database - either way it's the same issue).

So that when I do:

result = preparedStatement.getGeneratedKeys();
result.next();
return result.getInt(1);

The number that comes back doesn't appear to be thread safe!! It's usually grabbing the generated ID from the first thread that went through.

Basically if I run multiple threads, the INSERTS work fine, but then trying to get the automatically generated unique ID it brings back the same ID. Again, I suspect this is because the connection is shared and how I do

connection.prepareStatement(sqlString, Statement.RETURN_GENERATED_KEYS);

I see there are a number of alternative parameters but I'm not familiar with them. How can I somehow guarantee that each preparedStatement will later return it's own unique auto-generated ID?

UPDATE: This only seems to happen with the embedded derby database driver...

Answer 1

To avoid this you have to close the PreparedStatement from:

connection.prepareStatement(sqlString, Statement.RETURN_GENERATED_KEYS);

BEFORE you create another preparedStatement. It looks like there might be an assumption in the Embedded Derby driver that the code calling the database, at least for INSERTS, will never be threaded. If you are going to thread it, then do NOT use the embedded driver.

Answer 2

You could use your own Java synchronization block around your three lines of code, as in:

synchronized( preparedStatement )
{
    result = preparedStatement.getGeneratedKeys();
    result.next();
    return result.getInt(1);
}