Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to select only unlocked rows

Posted on 2006-07-07
7
Medium Priority
?
1,363 Views
Last Modified: 2007-11-27
I have a long-running transaction that runs concurrently from multiple sessions.  Each transaction needs to update a randomly selected row from a table of 100 rows.  It needs to do this without waiting for the other transactions to release their update locks.  In other words, it needs to randomly select for update any of the rows which is not locked for update by another transaction.  If all the rows are locked, it's ok to fail with a resource busy error, however, if any row is not locked, then I want to select one of them for update (don't care which one).

As it stands now, the application does a "Select  random_row for update NOWAIT", but the incidence of resource busy errors has become unacceptable.  I can remove the NOWAIT, but then the delay waiting for resources to free up is unacceptable.

Any ideas?          
0
Comment
Question by:dqmq
[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
  • 4
  • 2
7 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17063551
If your transaction is in PL/SQL

you could have something like

declare
var_rowid rowid;
var_col1 table1.col1%type;
var_col2 table1.col2%type;
cursor c is select rowid,col1,col2 from table1;
NOWAIT_EXCEPT EXCEPTION;
PRAGMA EXCEPTION INIT(NOWAIT_EXCEPT , -00054);
BEGIN
FOR I IN C
LOOP
SELECT col1 into VAR_COL1 FROM TABLE1 WHERE ROWID = I.ROWID FOR UPDATE NO WAIT;
EXCEPTION
WHEN NOWAIT_EXCEPT THEN
CONTINUE;
END;
END LOOP;
END:


0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 1050 total points
ID: 17063570
made some syntax errors above, here's the corrected version
declare
var_rowid rowid;
var_col1 table1.col1%type;
var_col2 table1.col2%type;
cursor c is select rowid,col1,col2 from table1;
NOWAIT_EXCEPT EXCEPTION;
PRAGMA EXCEPTION INIT(NOWAIT_EXCEPT , -00054);
BEGIN
FOR I IN C
LOOP
BEGIN
SELECT col1 into VAR_COL1 FROM TABLE1 WHERE ROWID = I.ROWID FOR UPDATE NO WAIT;
// Update transaction here;

EXCEPTION
WHEN NOWAIT_EXCEPT THEN
DBMS_OUTPUT.PUT_LINE('Unable to lock' || rowid);
END;
END LOOP;
END:

0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 450 total points
ID: 17063711
>>the incidence of resource busy errors has become unacceptable.  I can remove the NOWAIT, but then the delay waiting for resources to free up is unacceptable.

you can create a function to procedure to hide the error message. for example , you can create a function as:

create or replace function getRow
return rowid
as
  ret rowid;
  cursor c is select rowid from test1;
begin
  for rec in c loop
    begin
      select rowid into ret from test1 where rowid = rec.rowid for update nowait;
      exit;
      exception
         when others then
           null;
    end;
  end loop;

  return ret;
end;
/
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 19

Expert Comment

by:actonwang
ID: 17063717
then you can use this function as:

(1) in pl/sql block:
     declare
         rid rowid;
     ..
     begin
         rid := getRow;
     ...        
         update <table> set ... where rowid = rid;
         commit;
     end;

(2) in sql*plus:
     variable rid varchar2(20);

     exec :rid := getrow;

     update <table> set ... where rowid = :rid;


Acton
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17063720
>>cursor c is select rowid from test1;
>>select rowid into ret from test1 where rowid = rec.rowid for update nowait;

     replace "test1" by your own table name.
0
 
LVL 42

Author Comment

by:dqmq
ID: 17082962
Thank you both for the suggestions. Since both use essentially the same technique, I am splitting points with most going to sathyagiri. Partly that's for being first, but also because I don't particularly like the "when others then null" handler.

BTW, I am grading 'B' because neither solution addresses the case where all rows are locked and neither really selects randomly from the unlocked rows.  But, I have since figured out those details. Also, I oversimplified and didn't mention that there really are more than 100 rows in the table, but only 100 are qualified for update based on a "where" condition. The truely complicating factor is when a row changes between the open cursor and the select for update such that it no longer qualifies for the update.  So it is necessary to re-assert the where condition on the select for update and handle no_data_found similar to a locked row.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17087368
for random, it is easy to implement and I forgot to put it in. I guess you already get it.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.

704 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