ORA-00001: unique constraint violated error in oracle jdbc

Question

I have this 2 jdbc statement (Oracle 10g with isolation level read committed)

delete from emp where emp_id = 1;
insert into emp (emp_id,address,.....) value (1,'newyork',.....);

emp_id,address is the primary key

Unfortunately in a multithreaded environment I get ORA-00001: unique constraint violated while inserting the record.

When I run in a single thread I don't see any issue.

After investigation I found that

• first session deletes and then inserts a record but is not yet committed.
• second session deletes it
• first session now commits it
• second session now tries to insert it and throws the error .

Let me know if I am missing anything.

How to solve this problem in oracle.I don't like the solution of single threaded ,I also don't like the solution of retry.

Any clean solution?

Answer 1

Make sure you have enabled AUTOCOMMIT in your program, then check whether the row exists or not before inserting it to prevent ORA-00001. Here's the PL/SQL block.

declare
  v_rows_count number;
begin
  select count(*) into v_rows_count from emp where emp_id = 1;
  if v_rows_count = 0 then
    -- Insert the row
  else
    -- Do not insert the row
  end if;
end;
/

Don't forget to commit the change later.