Insert or Update using Oracle and PL/SQL

It's not an impossible situation you're encountering, as Oracle is telling you. You can get the described behavior if another process has inserted the key you're trying to insert but not yet committed. Updates won't see the inserted record, but the attempt to add the duplicate value to the unique index is prohibited even if the inserted row is not committed yet.

The only solutions that come to mind are to minimize the amount of time any uncommitted inserts are hanging around for this table, or to implement some sort of locking scheme, or to wait when your insert fails for the other transaction to complete.

Don't quite agree with DCookie.

IF session A inserts value "blue" (which is enforced to be unique), and then session B inserts value "blue", session B will wait on the lock from session A. If session A commits, then session B will get the constraint violation. if session A does a rollback, then session B will be allowed to continue.

Potentially, there is a very small scope for session A to insert a row and commit it, session B to get the constraint violation and then the row to be deleted before session B gets to update it. I'd judge that very unlikely though.

I'd first look at whether there is only one unique constraint on the target_total table. If not, you want to be very sure which constraint is causing the violation. Also check for unique indexes as well as constraints.

Check whether there is any datatype mismatch or an interfering trigger. A NUMBER(2,0) might not equal a 1.1 numeric value in a select match, but on insert the 1.1 would get truncated to a 1.0, potentially triggering a constraint violation. In my example, if a trigger had forced an uppercase "BLUE", then the select might fail to match on "blue", the insert might fail on a duplicate key on "BLUE", and the subsequent insert also fails to match on "blue".

Then check for variable naming. In an INSERT .... VALUES (identifier), then identifier must be a PL/SQL variable. However a SELECT * FROM table WHERE column = identifier, then identifier might be a column name not a PL/SQL variable. If there is a column name or a function of accountId, that would take precedence over the PL/SQL variable of the same name. It is a good habit to prefix PL/SQL variables to ensure there is never such a namespace conflict.

My only other idea is that, since you are running multi-threaded, is there any potential for the threads to conflict. This might be more likely in a live environment when threads might hit locks from other sessions. This might force them to synchronise in an odd fashion that doesn't crop up in testing.