Dave Tolls wrote:What are the unique columns that are causing the issues?
Why aren't you using PreparedStatements?
You seem to be manually calculating the next index. Doesn't the DB have some sort of sequence/auto-increment mechanism for ids?
Dave Tolls wrote:Without locking your table that produces the index whenever you need a new number out of it you cannot guarantee it will not have been nabbed by another connection.
Especially since you aren't even doing this in single transactions.
You are getting a connection, which you use to find the next number.
You then close that connection and grab a new one to update the number.
At any point in there, prior to the final commit, another connection could come in and grab that self same number.
Is there a reason you can't use the inbuilt mechanism, because you will be single-threading your app this way.
Dave Tolls wrote:That's something that's on the database side.
You would need to tell the db to lock the table, and I've no idea how you would do that with, is it DB2?
ETA: It would still be nice to know why exactly you can't use any inbuilt auto generator.
Dave Tolls wrote:If you have a bunch of tables that reference the same id then just pass it around.
You can get generated keys from an INSERT, I think it's on ResultSet (you need to tell the statement to return generated keys).
That gives you your ID, and then pass that on down the chain of other dependent INSERTs.