Solved

Oracle Locking

Posted on 2006-06-14
13
847 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
  • 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
 
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
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.

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

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

Suggested Solutions

Title # Comments Views Activity
Oracle PL/SQL syntax 4 52
UNIX SCP 5 47
statspack purge automate 7 29
unable to get sorting resultset 15 47
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…
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

757 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

19 Experts available now in Live!

Get 1:1 Help Now