Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Unanswered: For update + rownum

    Hello,

    Is there a way to select a resultset for update using ROWNUM :

    This sql statement gives me a "ORA-00907: missing right parenthesis" error :
    Code:
    SELECT * FROM (
                SELECT
                        REFERENCEID
                        ,PRIORITY
                    FROM
                        KRS.KKB_INQUIRIES
                    WHERE
                        PICKEDUP = 0
                        AND (
                            STATUS = 0
                            OR STATUS = 2
                            OR STATUS = 3
                            OR STATUS = 5
                            OR STATUS = 6
                            OR STATUS = 8
                            OR STATUS = 10
                            OR STATUS = 11
                            OR STATUS = 13
                            OR STATUS = 14
                        )
                    ORDER BY
                        PRIORITY DESC
                        ,CREATION DESC
                    FOR UPDATE SKIP LOCKED
    ) WHERE ROWNUM <= 40
    When I try this I get a "ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc." error :

    Code:
    SELECT * FROM (
                SELECT * FROM (
                            SELECT
                                    REFERENCEID
                                    ,PRIORITY
                                FROM
                                    KRS.KKB_INQUIRIES
                                WHERE
                                    PICKEDUP = 0
                                    AND (
                                        STATUS = 0
                                        OR STATUS = 2
                                        OR STATUS = 3
                                        OR STATUS = 5
                                        OR STATUS = 6
                                        OR STATUS = 8
                                        OR STATUS = 10
                                        OR STATUS = 11
                                        OR STATUS = 13
                                        OR STATUS = 14
                                    )
                                ORDER BY
                                    PRIORITY DESC
                                    ,CREATION DESC
                        )
                    WHERE
                        ROWNUM <= 40
            )
        FOR UPDATE SKIP LOCKED
    What I need is a query that returns me the first 40 rows after it sorts it by the Priority and Creation columns and they must be locked for update.

    Can anybody help me on this ?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    does Operating System name & version along with Oracle version to 4 decimal places matter to solution?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2002
    Posts
    37
    Quote Originally Posted by anacedent View Post
    does Operating System name & version along with Oracle version to 4 decimal places matter to solution?
    Haven't understood your question well. but here is more info about the environment :

    Database product version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    JDBC driver name : Oracle JDBC driver
    JDBC driver version : 10.1.0.5.0
    DataStoreHelper name is: com.ibm.websphere.rsadapter.Oracle10gDataStoreHelp er@44a044a0.
    JDBC driver type : ""

    OS : AIX 5.3

  4. #4
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    msegmx,

    you quoted the error message right, only the emphasis was wrong.

    Instead of quoting
    cannot select FOR UPDATE from view with DISTINCT, GROUP BY ...
    the emphasis should be:
    cannot select for update from VIEW with distinct, group by ...
    You are obviously trying to lock a aggregated or distincted column of a view.

    you have a situation like this:
    Code:
    SQL> create table test(a number, b varchar2(64), c date);
    
    Table created.
    
    SQL> insert into test select trunc(rownum/10), 'Row# ' || trunc(rownum/10), trunc(sysdate) from all_objects where rownum < 40;
    
    39 rows created.
    
    SQL> create or replace view v_test as select a, b, c, count(*) d from test group by a,b,c ;
    
    View created.
    
    SQL> select * from v_test;
    
             A B                                                                C                           D
    ---------- ---------------------------------------------------------------- ------------------ ----------
             3 Row# 3                                                           07-JUN-10                  10
             1 Row# 1                                                           07-JUN-10                  10
             2 Row# 2                                                           07-JUN-10                  10
             0 Row# 0                                                           07-JUN-10                   9
    
    SQL> select a, b, d from v_test where a > 2 for update;
    select a, b, d from v_test where a > 2 for update
                        *
    ERROR at line 1:
    ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
    
    
    SQL>
    If you look at the definition of the view, its obvious, that those aggregates and/or groupings cannot be locked for update.

    You can confirm that by
    Code:
    select object_type 
    from   all_objects 
    where owner  = 'KRS'
    and    object_name = 'KKB_INQUIRIES';
    If I'm right, the output should be 'VIEW' or 'MATERIALIZED VIEW' (or a SYNONYM pointing to a view)
    Last edited by magicwand; 06-07-10 at 19:14.
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  5. #5
    Join Date
    Oct 2002
    Posts
    37
    Hi Magicwand,

    ok, I understand that what you described thoroughly, thanks for that

    what I want to ask here is :

    Let's say we have a table A
    Code:
    tableA
    --------
    ID
    STATUS
    CREATION
    now, we have 2 threads running on different machines. Each thread wants to select only the last inserted 20 IDs where the status column has a value of zero.

    Code:
    select * from (select ID from tableA where status = 0 order by CREATION DESC) where rownum <= 20
    ok, it's working great. but since there are 2 threads, the second thread would select the same IDs since the first thread's select didn't lock the rows.

    this is not valid :
    Code:
    select * from (select ID from tableA where status = 0 order by CREATION DESC for update skip locked) where rownum <= 20
    after googling, I managed to make it work with this statement :

    Code:
    SELECT 
    	a1.ID
    FROM 
    	tableA a1, ( 
        SELECT 
                ID 
            FROM 
                ( 
                    SELECT 
                            ID
                        FROM 
                            tableA 
                        WHERE
                            STATUS = 0
                        ORDER BY 
                            CREATION DESC
                ) 
            WHERE 
                ROWNUM < 20 
    ) b1 
    WHERE 
    a1.ID = b1.ID 
    AND            a1.STATUS = 0 
    FOR UPDATE OF a1.STATUS SKIP LOCKED
    but there's a bug occurring : the first thread locks the last inserted 20 rows, but since the second thread also wants only the last inserted 20 rows, it just skips them and select zero rows instead of selecting the rows inserted at position 21 - 40.

    so I still need help on this.

  6. #6
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    msegmx,

    That's not a bug, but intended behaviour (you are selecting the first n records and within those records you skip the locked ones)

    You have to be very careful with that "skip locked" approach (if the row level locks held by the other session are released, you will get a different set of rows in the SELECT ... FOR UPDATE statement and the actual UPDATE statement you will finally issue down the road (or better: down the code)

    A serious and clean way to resolve this problem involves PL/SQL and a temporary table, however:

    Infrastructure:
    Code:
    SQL> create table test(a number, b varchar2(64), c date);
    
    Table created.
    
    SQL> create global temporary table tmp_test (row_id rowid) on commit delete rows;
    
    Table created.
    
    SQL> insert into test select trunc(rownum/10), 'Row# ' || rownum, trunc(sysdate) + 1 from all_objects where rownum < 40;
    
    39 rows created.
    
    SQL>

    In my example, the LIMIT parameter of the FETCH statement does the work instead of rownum:

    Code:
    declare
      cursor c1          is select * from (select rowid from test where a = 2 order by b asc) for update skip locked;
    
      type   tRowidTbl   is table of rowid index by pls_integer;
             vRowidTbl   tRowidTbl;
    begin
      open  c1;
        fetch c1 bulk collect into vRowidTbl LIMIT 6;
        close c1;
        forall i in vRowidTbl.FIRST..vRowidTbl.LAST
           insert into tmp_test (row_id) values (vRowidTbl(i)) ;
    end;
    /
    If you execute the above block in 2 different sessions, you will get:

    Session1:
    Code:
    SQL> select a.* from test a, tmp_test t where a.rowid = t.row_id;
    
             A B                    C
    ---------- -------------------- ------------------
             2 Row# 20              09-JUN-10
             2 Row# 21              09-JUN-10
             2 Row# 22              09-JUN-10
             2 Row# 23              09-JUN-10
             2 Row# 24              09-JUN-10
             2 Row# 25              09-JUN-10
    
    6 rows selected.
    and in Session 2:
    Code:
    SQL> select a.* from test a, tmp_test t where a.rowid = t.row_id;
    
             A B                    C
    ---------- -------------------- ------------------
             2 Row# 26              09-JUN-10
             2 Row# 27              09-JUN-10
             2 Row# 28              09-JUN-10
             2 Row# 29              09-JUN-10
    
    4 rows selected.
    Pls. note, that it's imperative, that you hold the ROWIDs (otherwise you are in big trouble, when one of the other sessions releases their locks) and that you have to access the table ONLY via join with the ROWIDs from the temporary table.
    Last edited by magicwand; 06-08-10 at 09:31. Reason: typos fixed
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  7. #7
    Join Date
    Oct 2002
    Posts
    37
    Thanks for taking the time to answer my question.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •