What could possible be wrong with the following function:-
public int RunIdentityInsert(String sql) throws SQLException {
Statement stmt = (Statement) conn.createStatement();
stmt.execute(sql);
return getScalarAsInt("SELECT @@IDENTITY AS NewID");
}
How about the bane of my programming life, threads? I've got loads of programs that have been running on servers for years using the above code, and mostly without a problem. Until yesterday.
My function added a record to a table containing about 6,000 rows, but the insert returned an id of about 250,000. It was only after looking at all the other possible reasons that this probably cause hit me: what if another thread ran another insert statement at pretty much exactly the same time? That would surely cause the same symptoms.
To be honest, I don't actually know if that was the cause, but it seems to me that the code should look like this:-
public int RunIdentityInsert(String sql) throws SQLException {
synchronized (conn) { // Need in case another thread also does an insert!
Statement stmt = (Statement) conn.createStatement();
stmt.execute(sql);
return getScalarAsInt("SELECT @@IDENTITY AS NewID");
}
}
