pl sql row level locking for synchronization ?

Posted on 2013-01-09
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?

1.begin 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; 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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 77

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 77

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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DB monitor SW 21 73
pivot rows to columns 1 45
Oracle DB Slows After Datapump Until Next Reboot 27 132
Oracle create type table from existing table%rowtype ? 6 63
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

733 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