Insert max + 1 from one table in two different table in batch update from java

Question

I have requirement of auto increment value in 2 tables with referential relation. My requirement is to insert data in two tables :- Table A = Rowid , other columns Table B = Rowid , other columns I need to insert data from different methods in both tables. Currently, I have taken this approach :- a. Query the max from Table A and add 1 to it. b. Insert data in both tables with value obtained in step 1 using batch update. However, flaw with approach is if two users/threads call this method at same time , they may end up having same value and I will get referential intergity error on Table A and Table B will have 2 rows with same row id .

I can syncronize my method to avoid that , however I have multiple different methods doing same due to some other functionality.

Ideal way to do this is to use autogenerated column, but out DBA is not allowing same. Also, this referential integrity between two table needs to be maintained by application , actual constraint is not allowed by DBA.

I am using Spring jdbctemplate on java side and DB2 on database side. However, problem is independent of poth.

Please note I know there are many questions looks similar to this one. But now one is updating two table and also from outer process. — Panther, Nov 30 '16 at 09:42
I am assuming that using a SEQUENCE or higher isolation levels is not an option. Correct? — data_henrik, Nov 30 '16 at 10:05
@data_henrik right DBA is asking to handle in application code. — Panther, Nov 30 '16 at 11:23

Stavr00 · Accepted Answer · 2016-11-30T20:05:27.853

1

SELECT MAX()+1 is not an acceptable way to create sequences for high volume, concurrent transactions.

A DBA refusing to use the concurrency features of DB2 such as ISOLATION LEVEL, IDENTITY or SEQUENCE objects is unfortunate.

There are application workarounds that can be used:

Wrapping the first statement in a FINAL TABLE in order to return the inserted values, including the MAX()+1 generated for TableA.rowid.

Using the return keys of the prepared statement:

PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
PreparedStatement prepareStatement(String sql, String[] columnNames)

Then the values returned are applied to the TableB.rowid

edited Nov 30 '16 at 20:05

answered Nov 30 '16 at 15:01

Stavr00

3,219
1
16
28

Totally agree with you. Alas many people make this error of max + 1 instead of letting the system manage the autoincrementation and retrieve the result thanks to final table ... – Esperento57 Nov 30 '16 at 17:57
Dude tone it down a bit. Pathetic really and you didn't mention referential integrity. – danny117 Nov 30 '16 at 20:04

Insert max + 1 from one table in two different table in batch update from java

1 Answers1