[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month18 days, 19 hours left to enroll

834 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