Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle Locking

Posted on 2006-06-14
13
Medium Priority
?
868 Views
Last Modified: 2013-12-11
Hi,

I am using Oracle 10

I having been going round in circles for ages trying to work out a solution, here is the scenario:

I have a table which has a column that indicates the status of the row, i.e. closed or open

I want to be able to recycle the row by finding the id of the first row that is closed and put my new data into that row.

I know how to do this in isolation by on the lines of "Select id from myTable where status = 'closed' and rownum = 1" "or also by using rowid instead as its a constant"

However, I want to ensure that if process a retrieves the id, then that row is locked out until it has been written to and the status is now open in case in the meantime process b gets given the same id and process b overwrites what process a has done.  I am thinking of doing this as a "select .... for update", however I am struggling to find out if a row is locked already, the skip locked would return nothing if the first row is locked as it seems to process the skip locked statement *after* finding the first row with 'closed' status.

The only other way I was thinking was to use the data dictionary objects (v$lock, v$session, etc.), but they only from what I gather show the object that is locked and not the row.  Sadly this cannot be done using a stored procedure as I could have done it that way.  Only other avenue I am thinking of if this helps is to view the transaction id of each row (if this can be done or anyone knows how to do this) and perform a join on one of the data dictionary tables (v$transaction ??) to see if the transaction is still active.

Thanks in advance
0
Comment
Question by:michaelfollett
[X]
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
  • 5
  • 4
  • 2
13 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16909146
Use row_number function to get the rownum for each id ordered by trans_date
Update only the first record

update TableA set col1=............
WHERE rowid = (select rid from(select rowid rid, row_number() over(partition by id order by trans_date) r1 from TableA) where r1=1);
0
 

Author Comment

by:michaelfollett
ID: 16909586
Thanks for your answer,

This would actually not work in my case as the steps need to be:

1.  Retrieve the id of the first row that is available to write to
2.  Lock this row until has been written to, and ensure that any other sessions that perform step 1 do not get the same id as any other ones.
3. Write the updates information to the redundant row

Thanks
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16909867
Specify nowait with "for update" clause and catch the exception using using user defined exception.

declare
cursor c1 is select rid from(select rowid rid, row_number() over(partition by id order by trans_date) r1 from TableA) where r1=1;
v_rid rowid;
cursor c2 is select * from TableA for update nowait;
c2t c2%rowtype;
rec_lock exception;
PRAGMA EXCEPTION_INIT(rec_lock, -54);
BEGIN
OPEN C1;
LOOP
fetch C1 INTO v_rid;
EXIT WHEN C1%NOTFOUND;
   BEGIN
   OPEN C2;
   fetch c2 INTO c2t;
   update TableA set ..........   where current of c2;
   CLOSE C2;
   commit;
   EXCEPTION WHEN rec_lock THEN dbms_output.put_line('Row Locked');
   END;
END LOOP;
close c1;
END;
/
0
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 16

Expert Comment

by:MohanKNair
ID: 16909871
Small correction in my earlier code

>> cursor c2 is select * from TableA for update nowait;

It should be

cursor c2 is select * from TableA where rowid=v_rid for update nowait;
0
 

Author Comment

by:michaelfollett
ID: 16909918
As I said in my first posting I cannot do this as a stored procedure and also it should be transparent that this is actually going on so i do not want to throw any exceptions.

I can see what this is trying to do but it is not suitable for my scenario
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16909942
The other option is to create a function which returns false if a row is locked. rowid is passed to the function. This function can be used in the where clause of the SQL query.
0
 

Author Comment

by:michaelfollett
ID: 16911860
Ideally I would like to do this as a view of locked rows, does anyone know if this is possible?
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 1000 total points
ID: 16913841
Even the view also has to make use of a function. You can know that a row is locked in exception block only.
0
 
LVL 27

Expert Comment

by:sujith80
ID: 17091079
Michael,
A few questions.

1. You said that it cannot be done using Stored Procedures. Its not clear. Do you want to do the same using standalone SQL statements?
2. Do you want multiple threads to process different records at the same time?

Rgds,
Sujith.
0
 

Author Comment

by:michaelfollett
ID: 17091323
Hi,

1. I want to do this with a standalone SQL statement
2. Yes, different threads need to get different answers so that they do not overwrite each others results

Thanks
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 1000 total points
ID: 17098054
Michael,

Practically it is impossible to do this task with a single standalone SQL. However you can do it with an anonymous block as shown below. This piece of code can be run from multiple sessions(threads) simultaneously. The call to get_Rowid will return different rowids to different sessions.

The code goes as follows:

declare
 l_rowid rowid;
begin
 
 l_rowid := get_Rowid; --this function returns the rowid to process

 if l_rowid is not null  
  --Do your processing that takes 2 mins here using the l_rowid;
 end if;

end;
/

Psuedo code for function:
 Here the logic is that - you pick one record with status "closed" and update the status to an intermediate status and use that rowid to do the processing in your session. So that no other session will pick up the same record for processing. DBMS_LOCK is being used to ensure that concurrent sessions are not accessing the same record.

Function get_Rowid
return rowid
as
 lock_handle varchar2(256);
 l_rowid rowid;
begin
 --allocate a lock handle
 dbms_lock.allocate_unique('test_lock',lock_handle);

 dbms_lock.request(lock_handle);
  --here pick one record with status = 'closed' and update the status to an intermediate status.
  begin
   select rowid into l_rowid
   from mytable
   where status = 'closed'
   and rownum < 2;

   update mytable
   set status = 'processing'
   where rowid = l_rowid;

   commit;
  exception
   when others then
    null
  end;

 dbms_lock.release(lock_handle);

  return l_rowid;  
end;
/


I might have overlooked a cpl of things as I dont have a working dbms_lock package to try out the code now. You can further optimize the calls to allocate handle by caching the lock handle and you should take care of your transactio flow also.

Hope this helps.

Rgds,
Sujith.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

618 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