pl sql row level locking for synchronization ?

Posted on 2013-01-09
Medium Priority
Last Modified: 2013-01-13

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?

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;


Note: This procedure can be called by multiple processes at a time with same request, but the procedure should pull the row from Table1 into Table2 only once.
Question by:sandyhere4u
  • 3
  • 3
  • 2
  • +1

Expert Comment

by:Javier Morales
ID: 38762018
Hi sandyhere4u,

The only way to ensure you avoid duplicates when multiple users are or may insert in a table is using sequences.

Oracle ensures that once a value is given by a sequence object, the next value given will be the next. That would be the method to fill two tables with "crosstransfering" rows and avoid duplicate id's.

if you need to run a process, and make others to wait until it ends, to avoid a PL/SQL code that copy files from one table to another, you have to use an external control (such as a table with a single row for "PL_COPY_ROWS_IS_RUNNING" (commit) because oracle reads don't lock other reads, and would allow it being running more than once and having duplicates.

By the way, what I think you need is that TABLE2 were a materialized view that gets values from TABLE1 when commit or when refreshing it on demand. That would solve your code issues and would "replicate" automatically when values are changed in TABLE1.

create materialized view TABLE2_MV as select * from TABLE1 refresh on commit;

Open in new window

Hope this would helps,
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38762663
Are the rows in table2 just used for the session that inserts them?

If so, take a look a global temporary tables.  This is what they were designed for.


Expert Comment

by:Javier Morales
ID: 38762731
@slightwv I don't think Table2 could be temporary, because on commit (or end of session) all data from Table2 would be lost.

The author wants to avoid duplicates if two processes check files and perform insert of same rows at the same time.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38763108
>>I don't think Table2 could be temporary,

It all depends on what is done with the data in table2 once a process inserts it.  We need more infomration about the process before we can say what will or will not work.

I can easily see table2 being used as a staging table by the process.  Once the process is done doing what it does, the data is no longer necessary.

If the process inserts into table2 and the data must persist long after the process finishes then yes, a global temp table will not work.

Author Comment

ID: 38763334
Actually Table1 has huge data and Tabl2 has relatively less data but we pull any requested record from Table1 into Table2 on demand or on fly.

Data in both tables must be persisted for ever.

@techlevel : yes, I want to avoid duplicates if multiple processes trying insert same row at same time.

I thought first of all contending processes could get lock at Update statement followed by "for update". and rest of the processes wait at 7 till first one commits, then they will check
existence of the record in Table2 which will avoid inserting duplicate rows as first process inserted already inserted the row.

I have similar logic working in other procedure, but some how it is not working here.

Could you please let me know if there are any factor that cause row level lock not to work?

I have index on id_no(Table1).

Expert Comment

by:Javier Morales
ID: 38763519
Hi Sandyhere4u,

Row level locks allows other processes to read the table, so you will hit to the same issue again.

I would perform what I suggest in my first comment. Using a table for controlling, or using a materialized view (works as a table, you can index it, etc.), refreshed on commit (on table1).

hope this helps, kind regards,
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38764051
When you say you have a 'procedure', is this an actual stored procedure or a defined process?

From what you posted, it looks like you do this on a row by row basis.

Can you create an oracle stored procedure to process a single id_no?  then you can trap the dup_val_on_index exception and basically ignore it?

Something like:

create or replace procedure row_migrate(in_id_no in number)
     insert into table2 (select * from table1 where id_no=in_id_no);
     exception when dup_val_on_index then null;
LVL 15

Accepted Solution

Franck Pachot earned 1500 total points
ID: 38768643

I see the following issues in your code:

1. the select fir update in line 2 is not very useful as the following update in line 5 is locking the same row in the same way.

2. the select count(*) do not lock rows, so if a concurrent insert has inserted the rows - and not yet commited - the it just do not see that row

3. it is easy to look a row that exists. But here you need to log the 'inexistance' of a row so that you don't have 2 conccurent transactions seeing that there is no row in table 2.

Here are a few suggestions:

1. the best way to avoid duplicates is to have a unique constraint. It is enforeced by a unique index and then the lock will be done in the index entry. When a row is already inserted for the same value then another insert will wait on the lock to see if the concurrent insert is commited or not, and will fail. So you just have to enclose the insert in a:
 begin insert ... exception when dup_val_on_index then null; end;
to avoid to do the insert in that case.

But that solution may have a performance overhead (insert is done then rolled back)

2. Have a look at dbms_lock that let you request a lock on a value (for example based on the id_no) whithout any table. with that you serialize your procedure when called for the same id_no, so the second one will wait until the first one commits and see the inserted row.


Author Closing Comment

ID: 38772114
The Code I gave also worked when I changed locking table Table3 instaed of instaed Table1, provided Table1 is subset of Table3.

I could not use materialized views as Tbale2 and Table1 need to be treated independent.
Whole Data in Table1 changes once in a day. Committed records in Table2 should be independent of source Table1 and should not be modified unless there is explicit update.

I also agree with Franck. Thank you so much, appreciate your help.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month5 days, 15 hours left to enroll

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question