5 Replies Latest reply: Jun 6, 2009 10:01 PM by SanjayRs RSS

    Multiple connections Sequence.nextVal - SQL Integrity exception

    673660
      hi,

      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)
      I dont have any other constraints. tableA.id is inserted as sequence.nextval
      I keep seeing SQLIntegrityViolationException for tableA.id.
      Looks like a race condition to me.

      How does Oracle ensure unique incremental values with sequence.nextval for multiple concurrent connections ?
      What can I do in pl/sql to avoid this race condition ?

      Thanks in advance.
        • 1. Re: Multiple connections Sequence.nextVal - SQL Integrity exception
          630199
          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?

          Regards,
          Jo
          • 2. Re: Multiple connections Sequence.nextVal - SQL Integrity exception
            105967
            Can you post your stored procedure to look where the error is coming from


            also the exact error-message would be handy

            Edited by: Leo Mannhart on Jun 6, 2009 11:40 AM
            • 3. Re: Multiple connections Sequence.nextVal - SQL Integrity exception
              riedelme
              Check your table constraints. Maybe a different (foreign key?) constraint is being violated than the one you think
              • 4. Re: Multiple connections Sequence.nextVal - SQL Integrity exception
                Peter Gjelstrup
                Hi,
                I dont have any other constraints. tableA.id is inserted as sequence.nextval
                How 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
                http://blog.aggregatedintelligence.com/2008/12/oracle-sequences-and-concurrency.html

                What can I do in pl/sql to avoid this race condition ?
                Which race condition is that?



                Did you make this simple test:
                select max(id) from tableA;
                
                select sequence.nextval from dual;
                And did you check that you sequence isn't declared as CYCLE?


                Regards
                Peter

                Edited by: Peter on Jun 6, 2009 7:30 AM
                - Added link
                • 5. Re: Multiple connections Sequence.nextVal - SQL Integrity exception
                  SanjayRs
                  user7344164 wrote:
                  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)
                  Don't Insert Id from the insert command, Don't Select sequence in Java.
                  How does Oracle ensure unique incremental values with sequence.nextval for multiple concurrent connections ?
                  What can I do in pl/sql to avoid this race condition ?
                  Create a trigger on TableA - before insert on each row when ID is Null, and select sequence value in the trigger for the ID.


                  HTH
                  SS