3 Replies Latest reply on Aug 26, 2014 4:57 PM by Gaff

    Concurrency issue faced in Oracle 11G



      We are facing an issue  with our code . Java + Oracle.

      We have a lookup table . get a select for update lock on it, fetch the value use it and then insert the fetched value in another table where it has unique constraint on the said field.

      But sometimes when a lot of processing is happening we  are getting unique constraint error . Could it be because of JDBC driver ?

      Just to emphasis the whole  application works fine for 99% of the time  only sometimes it is failing. When we rerun it goes to success.

      Any advise?

        • 1. Re: Concurrency issue faced in Oracle 11G

          So you are using the select for update on the source table, to serialise the insert into the other table - to ensure no unique constraint violations occurs on the other table?


          Is this done via PL/SQL  code? Or Java code? Where and when is the commit done? If the source row ever updated - or is the select for update only to serialise and not change the source row too?

          • 2. Re: Concurrency issue faced in Oracle 11G
            Zlatko Sirotic

            I fully agree with Billy that we need more information about your problem.


            However, I cannot resist quoting the beautiful words of Thomas Kyte from his excellent book

            "Expert Oracle Database Architecture - Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions" (second edition 2010, pages 290-291):


            Using Autocommit


            My final words on bad transaction habits concern the one that arises from using the popular programming APIs ODBC and JDBC.

            These APIs “autocommit” by default.


            Consider the following statements, which transfer $1,000 from a checking account to a savings account:

            update accounts set balance = balance - 1000 where account_id = 123;

            update accounts set balance = balance + 1000 where account_id = 456;

            If your program is using ODBC or JDBC when you submit these statements, they (silently) inject a commit after each UPDATE.

            Consider the impact of this if the system fails after the first UPDATE and before the second. You’ve just lost $1,000!


            I can sort of understand why ODBC does this. The developers of SQL Server designed ODBC,

            and this database demands that you use very short transactions due to its concurrency model (writes block reads, reads block writes, and locks are a scarce resource).

            What I can’t understand is how this got carried over into JDBC, an API that is supposed to support “the enterprise.”

            It is my belief that the very next line of code after opening a connection in JDBC should always be

            Connection conn = DriverManager.getConnection


            conn.setAutoCommit (false);


            This returns control over the transaction back to you, the developer, which is where it belongs.

            You can then safely code your account transfer transaction and commit it after both statements have succeeded.

            Lack of knowledge of your API can be deadly in this case.

            I’ve seen more than one developer unaware of this autocommit “feature” get into big trouble with his application when an error occurred.



            Do you use setAutoCommit (false) ?




            • 3. Re: Concurrency issue faced in Oracle 11G

              Confusing process flow.  Am I to understand that you lock the lookup table (which isn't updated)?  Why?


              Also, the unique constraint violation would appear to have absolutely nothing to do with concurrency.  You looked up a value and you inserted it into a destination table (along with some other data?).  Then you looked up another value and got the same lookup value back.  You then try to insert that into the destination table and get a unique constraint violation.  How is this a concurrency issue?  Are you removing the lookup value from the lookup table after using it?



              (Corrected  a type-O.)