0

I'm new with DB2 and have this problem: 2+ independ (test) processes are creating new test data records into the same table. To do this I have to fetch the last existing (biggest) ID from this table, increment it and us it for the new record. The smallest ID has to be 1000 for some reasons. There is no sequence belonging to the ID columnd, nor any other automatic mechanism, but it hast to be unique of course.

I wrote this SQL statement:

SELECT ID FROM FINAL TABLE(
  INSERT INTO table_x (ID,NAME)
  VALUES((SELECT COALESCE(MAX_ID, 1000) MAX_ID FROM (
    SELECT * FROM (SELECT MAX(ID) AS MAX_ID FROM table_x))
  )+1,'Ben')
)

The statement works, but it happens running this statements multiple times at the time there is still a concurrency issue because identical ID.

Obviously I have to do something more. I guess table locking could be a solution or maybe there are other best practices? I found some articles about NEXTVAL, but it works only with sequences.

Yes, I found this DB2 INSERT INTO … SELECT lock but I didn't really see a solution for my case.

Thanks for any hints!

Rainer
  • 1,067
  • 2
  • 14
  • 29
  • That design will not scale, you should get the table recreated to use an identity column (generated always by default). Otherwise try using statement-level isolation to influence locking , example: `SELECT ID FROM FINAL TABLE( INSERT INTO table_x (ID, NAME) VALUES( (SELECT COALESCE(MAX(ID), 1000)+1 FROM table_x with RR) , 'Ben') ); `. You can also try `with RS` . Probably you are using default isolation. – mao Apr 21 '21 at 14:26
  • Thanks. But unfortunatly testers often have to accept existing environments, so there is no chance to change the table design. I try 'with RS'. Default isolation .. I don't know the concept behind so far. – Rainer Apr 21 '21 at 14:40
  • 1
    Wit this approach the race condition is unavoidable, unless you're willing to sacrifice concurrency by locking more than you need. Nothing should prevent you from creating a sequence for test purposes and using it for value generation. – mustaccio Apr 22 '21 at 11:58

0 Answers0