1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

lock and get next n records from table in oracle

Discussão em 'Outras Linguagens' iniciado por Stack, Maio 19, 2021.

  1. Stack

    Stack Membro Participativo

    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

    Updates for 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

    Continue reading...

Compartilhe esta Página