Hi all,
I have a question about a simple code that should automatically insert a
record in a table. The primary key for this record is automatically
generated during the INSERT statement, as the max value plus one.
The (simplified, since the real number of fields is 5) code is:
final String query = "INTO table (docId,seqNr) " +
"VALUES (?, (SELECT 1 + coalesce (max(seqNr), 0) "+
"FROM table " +
"WHERE seqNr BETWEEN 0 AND (9223372036854775807-1) ))";
Connection conn=daof.getConnection();
try {
conn.setAutoCommit(true);
PrepararedStatemen st = conn.prepareStatement(query);
st.setString(1,'myDocId');
st.executeUpdate();
conn.close();
}
catch (SQLException e) {
try { conn.close(); } catch (SQLException ee) {}
throw e;
}
I then create Runtime.getRuntime().availableProcessors() threads that
create a DataSource and start executing that code in parallel. What
happens is that I get many DUPLICATE KEY errors. I thought that in
AutoCommit all insert are supposed to be serialized, so they should
create a seqNr different since MAX(seqNr) is different at each call.
Is my reasoning correct? Or is it a different problem, maybe with jdbc
or postgresql?
Thanks,
Giuseppe
