Skip to Content
0

CURRENT_IDENTITY_VALUE inconsistency

Apr 10, 2017 at 11:59 PM

88

avatar image

It seems like CURRENT_IDENTITY_VALUE() function in HANA can mess things up when you have at least two tables in your session with identity columns. Just try this sample code:

create column table test1(id integer not null primary key generated by default as identity, x integer);
create column table test2(id integer not null primary key generated by default as identity, x integer);

create procedure temp_identity_p as
  i integer;
  j integer;
begin
  for i in 1..500 do
    insert into test1(x) values (:i);
    select current_identity_value() into j from dummy;
    if i > 100 then
      insert into test2(x) values (:j);
    end if;
  end for;
end;

call temp_identity_p;

select * from test1 where id <> x;
select * from test2 where id <> x-100 order by id;

After executing the procedure test1 table looks as expected. Values from 1 to 500, same values in "id" and "x" columns.

But in test2, x should come from test1.id (sort of a link to it) with a delay of 100. So you expect:

1,101

2,102

3, 103 and so on

But it's not the case in every record.

4,3

5,4

records follows. It seems like CURRENT_IDENTITY_VALUE() sometimes gets its value from the last insert of test2 instead of test1. However it's pretty obvious that CURRENT_IDENTITY_VALUE() is executed right after test1 insertion.

This is very dangerous since it can mess up your foreign keys in your code.

Do you know any solution for this problem?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

avatar image
Former Member Jun 06, 2017 at 10:06 PM
1

While this may not help, you could remove the identity key from the table and manage it yourself by creating a specific sequence (sq_mysequence) and callint it's sq_mysequence.nextvalue when doing the insert, or (before doing the insert into test1, putting it in a variable then reusing it for all subsequent insert in test2).

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jun 06, 2017 at 10:11 PM
1

What is the revision of your database ? executing this on 122.05 return on result in the test2 select query. looking manually at all the results, they seems fine to me.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you for your answer, and your time!

Our revision is 112.03, but strangely enough, now everything works fine for me too.

It's so embarrassing, but I cannot reproduce it. There has been no version change since my post. Maybe this behaviour was caused by some settings or coincidence, that has been changed since then. But I'm sure I spent a lot of time with it, and it caused a lot of trouble to us. I really don't understand...

0
avatar image
Former Member Jul 19, 2017 at 01:57 PM
1

For (1..500) might be executed in parallel. current_identity_value will probably not return the last inserted value from this thread, but any thread. In your case, execution of foreach with i = 100 started and inserted, but then another thread processed where i = 1 and inserted. then code switched back to the select current_ident_value and got 1?

Instead of using current_identity_value which might not be correct as it mixes identity created from any table and threads and sessions, proactively get the sequence to use, then push it to the child table. You will end up with less issue. here is a draft but I am not sure will compile

on mssql you would have fixed this by using the output close, to return the inserted primary key of that specific insert, but it's seems it's not possible on hana as far as the insert documentation is concerned.

create column table test1(id integer not null primary key, x integer);
create column table test2(id integer not null primary key, x integer);
create sequence sq_test1;
create sequence sq_test2;

create procedure temp_identity_p as
  i integer; sq_i integer;
  j integer; 
begin
  for i in 1..500 do
    selelct sq_test1.nextval into sq_i from dummy;
    insert into test1(id, x) values (:sq_i, :i);
    
    if i > 100 then
      insert into test2(id, x) values (sq_test2.nextval, :sq_i);
    end if;
  end for;
end;


Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you!

Sequence is the way to go, instead of auto generated identity and current_identity_value.

In my opinion, using current_identity_value makes no sense at all in any cases, because of the thread-issue.

0
Gergely Humicskó Jun 28, 2017 at 09:31 AM
0

Ooops I did it again. :)

In the same sql console window that I used last time. But last time it refused to mix up the result, now it does it again.


1.jpg (83.5 kB)
2.jpg (76.0 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jul 19, 2017 at 02:15 PM
0

... and the last example with sequence show that you will insert the sequence of i into test2, and not your i variable generated by the for, so so it's normal you might not have test2.x > 100 because those are two different var.

Share
10 |10000 characters needed characters left characters exceeded