SQL & PL/SQL

Hi,

I am using oracle 10g.
I am new to Oracle locks. I have two tables Table1(id_no, employee, salary) and Table2(id_no, employee, salary).
I need to pull any requested row from Table1 into Table2 only once on demand. I have a procedure to pull data and there could be more than one requests try to call same procedure to pull a row from Table1 into Table2 at any given time.

I coded below to achieve row level lock. if one transaction gets row level lock on Table1 at 2, so other Transactions should wait till the lock is released at line 2 or 5 to avoid duplicates.
But below code is not working, I am getting duplicates when I call this using two concurrent java threads.
How do I control this concurrency issue so that I can avoid duplicate entries in Table2. Could any one please help?

1.begin
2.select 0 into emp_cnt
3.from Table1 where id=id_no
4.for update;

5.update Table1 set employee='xyz'
6.where id=id_no;

7.select count(*) into table2_cnt from Table2 where id=id_no;

8.if(table2_cnt =0) then
9.code to insert above row from Table1 to Table2;
10.end if;

11.commit;
12.End;

Edited by: 980916 on Jan 9, 2013 5:48 PM