Solved

pl sql row level locking for synchronization ?

Posted on 2013-01-09
9
1,230 Views
Last Modified: 2013-01-13
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;

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.
0
Comment
Question by:sandyhere4u
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 6

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,
Javier
0
 
LVL 76

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.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables003.htm#ADMIN11633
0
 
LVL 6

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.
0
 
LVL 76

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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sandyhere4u
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).
0
 
LVL 6

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,
Javier
0
 
LVL 76

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)
is
begin
     insert into table2 (select * from table1 where id_no=in_id_no);
     exception when dup_val_on_index then null;
     commit;
end;
/
0
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 500 total points
ID: 38768643
Hi,

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.

Regards,
Franck.
0
 

Author Closing Comment

by:sandyhere4u
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now