Lock and get next n records from table in oracle

oraclequeryquery-performance

I have a oracle table PHONE_NUMBER which has 2 columns PHONE and STATUS

PHONE_NUMBER 
PHONE STATUS
1     U
2     O
3     U

U stands for used , O is open/UnUSED .

in my input I will get n as input number , which signifies the number of phones I want to reserve(update status to U from O )

I want a query that will lock(something like SELECT FOR UPDATE) the any n available row (rows that are not locked yet and having status as O )

Constraints: Table has millions of Used and unsed phones

comments:

problem is i Want n (input) rows to be locked with a particular status . i.e i have to work with rownum

I will be getting multiple concurrent calls in my service with sqli_n as input number of records to select :
so

SELECT * FROM PHONE_NUMBER WHERE STATUS='O' and rownum<=:sqli_n FOR UPDATE ;

this query wont work for me it will select only first n rows and if the first n rows are locked in 1 call the next/concurrent call will be stuck /wait until the first call updates the status to Used .

for update no wait will fail the above query

for update skip lock will also not work with row num

Best Answer

So, you have discovered that you can't do what you want; so you may want to do what you can by redesigning your process. One approach might be to introduce a new column, e.g. session_id, to indicate what application session, if any, is processing the row.

Each session will then start by atomically reserving a set of rows:

UPDATE phone_number SET session_id='<whatever>' 
WHERE status='O' AND session_id IS NULL AND ROWNUM<=:sqli_n;
COMMIT;

It will then do whatever it needs to do and finally release the reserved rows:

UPDATE phone_number SET session_id=NULL 
WHERE session_id='<whatever>';
COMMIT;

You'll likely need to revise indexes on this table.

Related Solutions

Trigger that records values for 5 columns from 3 tables when a value for column Z in table 3 is of a certain value

If you want to insert data into a different table instead of the original table from the INSERT/UPDATE statement, you need an "Instead Of" trigger.

You find examples on how to handle Instead Of triggers here: http://psoug.org/reference/instead_of_trigger.html and http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020

In the body of your trigger you first need to get the additional information like fname, lname, etc. With a query like this one:

SELECT si.fname into l_fname, si.lname into l_lname, c.phone into l_phone, c.email into l_email
  FROM si_table si, contact_table c, bookinglog_table b
 WHERE si.student_id=c.student_id AND c.event_id=b.event_id, AND si.test=b.test
   AND <add a clause to uniquely identify a single record>;

Remember to declare the cariables l_fname, l_lname, etc. in the DECLARE block of the trigger:

DECLARE
  l_fname varchar2;
  l_lname varchar2;
  l_phone varchar2;
  l_email varchar2;
BEGIN
...

Even though this solution might work, it is actually not correct. You better create the table grade_table as follows. According to the database normalization form 4 you should not store detailed data multiple times.

CREATE TABLE grade_table (
  student_id number,
  event_id number,
  test number,
  grade NUMBER
);

When you query the table grade_test and need the details on the student, etc. you simply join the detail tables.

Updating duplicate records with different data

Using the ranking ROW_NUMBER() function will work. First give row numbers to all rows in both tables, then join using these row numbers, then update:

with 
  oldt as
  ( select fileNo , folder, fileType,
           row_number() over (partition by fileNo, folder
                              order by fileType)
             as rn
    from oldtable
  ),

  newt as
  ( select fileNo , folder, fileType,
           row_number() over (partition by fileNo, folder
                              order by fileType)
             as rn
    from newtable
  ),

  upd as
  ( select 
        n.fileType,
        o.fileType as old_fileType
    from newt n
      join oldt o
      on  n.fileNo = o.fileNo
      and n.folder = o.folder
      and n.rn     = o.rn 
  ) 

update
    upd
set 
    fileType = old_fileType ;

SQLfiddle seems to be giving error for Oracle, so it has been tested in SQL Server only: SQLfiddle-test (but this syntax should be valid for Oracle, too).

Tested in Oracle, the above doesn't work, sadly. I think because statements that start with WITH can only be SELECT statements. Even if the query is rearranged (I tried several rewrites), Oracle throws various errors. The only way I managed to have it working is after adding another column in newtable and a unique constraint on it. Then the following works (nid is the added primary key column).

Tested in Oracle's Live SQL site:

update 
  ( with 
      oldt as
      ( select fileNo , folder, fileType,
               row_number() over (partition by fileNo, folder
                                  order by fileType)
                 as rn
        from oldtable
      ),
    newt as
      ( select fileNo , folder, nid,
               row_number() over (partition by fileNo, folder  
                                  order by fileType)
                 as rn
        from newtable
      ),
    upd as
      ( select 
            n.nid,
            o.fileType as old_fileType
        from newt n
          join oldt o
          on  n.fileNo = o.fileNo
          and n.folder = o.folder
          and n.rn     = o.rn 
      ) 
    select 
        up.fileType,
        ( select upd.old_fileType
          from upd 
          where upd.nid = up.nid 
        ) as old_fileType
    from newtable up
  ) x
set fileType = old_fileType ;

Best Answer

Related Solutions

Trigger that records values for 5 columns from 3 tables when a value for column Z in table 3 is of a certain value

Updating duplicate records with different data

Related Question