This content has been marked as final. Show 5 replies
I am not sure why you are getting this error because each time you reference the NEXTVAL value of a sequence, the sequence is incremented immediately and permanently, whether you commit or roll back the transaction. Concurrent access of NextVal on a sequence will always return separate values to each caller. (Unless the sequence has been setup to cycle values - in which case duplicate values are possible).
Have you tried to run the procedure independently and see if it raises any exception while inserting data into TABLEA?
I dont have any other constraints. tableA.id is inserted as sequence.nextvalHow is sequence.nextval taken and how is it used.
I keep seeing SQLIntegrityViolationException for tableA.id.What's that but an abstraction of the real problem. Oracle developers are concerned with ORA-numbers
Looks like a race condition to me.Maybe so ... But not like you anticipate
How does Oracle ensure unique incremental values with sequence.nextval for multiple concurrent connections ?Do not waste your time worrying that Oracle does not know how to implement a scalable, thread safe sequence.
Spend your time locating the real problem.
But, here is someone that looked into it, not certain of the quality of the post, but it looks ok
What can I do in pl/sql to avoid this race condition ?Which race condition is that?
Did you make this simple test:
And did you check that you sequence isn't declared as CYCLE?
select max(id) from tableA; select sequence.nextval from dual;
Edited by: Peter on Jun 6, 2009 7:30 AM
- Added link
user7344164 wrote:Don't Insert Id from the insert command, Don't Select sequence in Java.
I have multiple connections to the db (multiple java threads each with one connection). Each thread invokes a stored procedure that inserts into tableA(id number primary key, someotherfield varchar2)
How does Oracle ensure unique incremental values with sequence.nextval for multiple concurrent connections ?Create a trigger on TableA - before insert on each row when ID is Null, and select sequence value in the trigger for the ID.
What can I do in pl/sql to avoid this race condition ?