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?
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):
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
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) ?
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.)