Committing over a Select For Update

I’ve read a couple of articles recently reflecting on changes and bugs roughly in and around read consistency in recent versions.

There’s one here from Jonathan Lewis that may or may not be a bug.

There was another one yesterday from Charles Hooper that definitely must be a bug because it’s related to whether a unique index is used as the access mechanism.

This reminded me of a good change in behaviour that I thought I had blogged about but it appears not.

I mentioned this in a comment on Charles’ post above and Mohamed Houri quite rightly questioned my assertion that this behaviour changed as recently as 10.2.0.5.

I can’t find the metalink note that I thought I had referenced before soooo if anyone can verify behaviour on versions before and between those that I currently have access to (need to get that laptop fixed) – 9.2.0.8 and 11.2.0.2 – then I’d be grateful, with particular interest in 7, 8, 8i, 10.2.0.4 and 10.2.0.5.

This is a simple, noddy example but horrendously bad practice, flawed and responsible for a serious duplicate messaging issue recently on a system that I know. But it’s PLSQL code that I reckon there’s still quite a lot of out there.

Forget about the lack of bulk operations, when you look at the results from 9.2.0.8 below, just think about concurrent executions and a SELECT FOR UPDATE driving a LOOP that COMMITS and what that COMMIT does to the supposed protection offered by the SELECT FOR UPDATE on subsequent loops. Thank goodness this is prevented in recent versions.

Test is:

SELECT * FROM v$version;  

DROP TABLE t1;  

CREATE TABLE t1  
AS  
SELECT ROWNUM col1  
FROM   DUAL  
CONNECT BY ROWNUM <=10;  
   
DECLARE  
 CURSOR c1  
 IS  
   SELECT col1 FROM t1 FOR UPDATE;  
 l1 t1.col1%TYPE;  
BEGIN  
  OPEN c1;  
  LOOP  
     FETCH c1 INTO l1;  
     EXIT WHEN c1%NOTFOUND;  
     COMMIT;  
 END LOOP;  
 CLOSE c1;
 DBMS_OUTPUT.PUT_LINE(l1);  
END;  
/ 

Result on 9.2.0.8 is:

SQL> SELECT * FROM v$version;  

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> 
SQL> DROP TABLE t1;  

Table dropped.

SQL> 
SQL> CREATE TABLE t1  
  2  AS  
  3  SELECT ROWNUM col1  
  4  FROM   DUAL  
  5  CONNECT BY ROWNUM <=10;  

Table created.

SQL>    
SQL> DECLARE  
  2   CURSOR c1  
  3   IS  
  4     SELECT col1 FROM t1 FOR UPDATE;  
  5   l1 t1.col1%TYPE;  
  6  BEGIN  
  7    OPEN c1;  
  8    LOOP  
  9       FETCH c1 INTO l1;  
 10       EXIT WHEN c1%NOTFOUND;  
 11       COMMIT;  
 12   END LOOP;  
 13   CLOSE c1;
 14   DBMS_OUTPUT.PUT_LINE(l1);  
 15  END;  
 16  / 
10

PL/SQL procedure successfully completed.

SQL> 

Result on 11.2.0.2 is:

SQL> SELECT * FROM v$version;  

BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> 
SQL> DROP TABLE t1;  

Table dropped.

SQL> 
SQL> CREATE TABLE t1  
  2  AS  
  3  SELECT ROWNUM col1  
  4  FROM   DUAL  
  5  CONNECT BY ROWNUM <=10;  

Table created.

SQL>    
SQL> DECLARE  
  2   CURSOR c1  
  3   IS  
  4     SELECT col1 FROM t1 FOR UPDATE;  
  5   l1 t1.col1%TYPE;  
  6  BEGIN  
  7    OPEN c1;  
  8    LOOP  
  9       FETCH c1 INTO l1;  
 10       EXIT WHEN c1%NOTFOUND;  
 11       COMMIT;  
 12   END LOOP;  
 13   CLOSE c1;
 14   DBMS_OUTPUT.PUT_LINE(l1);  
 15  END;  
 16  / 
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 9


SQL> 

As mentioned, if anyone can double check the behaviour on other versions, particularly 10.2.0.4 and 10.2.0.5 then please post.

P.S. Metalink note 14542.1 talks about committing over a select for update and mentions relevance for PLSQL from 10.2.0.5. This isn’t the note that I remember confirming the change.

P.P.S. Talk about horrendously bad practice, I missed the “CLOSE c1;”, corrected now!

P.P.P.S. That missing CLOSE reminds me of something else I meant to blog about when someone asked me about whether it was really necessary, as was stipulated in their coding standards, to have all this “IF c1%ISOPEN THEN CLOSE c1;” type code – it’s all about cursor scope…

About these ads

10 Responses to Committing over a Select For Update

  1. Same results on 10.2.0.4 and 11.1.0.7 – both match your output for 11.2.0.2:

    BANNER
    -------------------------------------------------------
    Oracle Database 10g Release 10.2.0.4.0 - Production
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
     
    SQL>
    SQL> DROP TABLE t1;
     
    Table dropped.
     
    SQL>
    SQL> CREATE TABLE t1
      2  AS
      3  SELECT ROWNUM col1
      4  FROM   DUAL
      5  CONNECT BY ROWNUM 
    SQL> DECLARE
      2   CURSOR c1
      3   IS
      4     SELECT col1 FROM t1 FOR UPDATE;
      5   l1 t1.col1%TYPE;
      6  BEGIN
      7    OPEN c1;
      8    LOOP
      9      FETCH c1 INTO l1;
     10      EXIT WHEN c1%NOTFOUND;
     11      COMMIT;
     12    END LOOP;
     13    DBMS_OUTPUT.PUT_LINE(l1);
     14  END;
     15  /
    DECLARE
    *
    ERROR at line 1:
    ORA-01002: fetch out of sequence
    ORA-06512: at line 9
    
    BANNER
    ---------------------------------------
    Oracle Database 11g Release 11.1.0.7.0
    PL/SQL Release 11.1.0.7.0 - Production
    CORE    11.1.0.7.0      Production
    TNS for 32-bit Windows: Version 11.1.0.
    NLSRTL Version 11.1.0.7.0 - Production
     
    SQL>
    SQL> DROP TABLE t1;
     
    Table dropped.
     
    SQL>
    SQL> CREATE TABLE t1
      2  AS
      3  SELECT ROWNUM col1
      4  FROM   DUAL
      5  CONNECT BY ROWNUM 
    SQL> DECLARE
      2   CURSOR c1
      3   IS
      4     SELECT col1 FROM t1 FOR UPDATE;
      5   l1 t1.col1%TYPE;
      6  BEGIN
      7    OPEN c1;
      8    LOOP
      9      FETCH c1 INTO l1;
     10      EXIT WHEN c1%NOTFOUND;
     11      COMMIT;
     12    END LOOP;
     13    DBMS_OUTPUT.PUT_LINE(l1);
     14  END;
     15  /
    DECLARE
    *
    ERROR at line 1:
    ORA-01002: fetch out of sequence
    ORA-06512: at line 9
    
    • Dom Brooks says:

      Cool – so I’m definitely mistaken about 10.2.0.5 being the fix version.
      Maybe that’s why I can’t find that note…

      I know I’ve had dreams about code before, but dreams about metalink notes… that’s sick.

      • I wonder if you are remembering Metalink (MOS) Doc ID 14542.1, “COMMITTING OVER A SELECT FOR UPDATE” – Applies to PL/SQL – Version: 10.2.0.5 to 11.2.0.2 – Release: 10.2 to 11.2
        That article seems to imply that the advice only applies to versions 10.2.0.5 and above, but it appears that the behavior exists in 10.2.0.2 and 10.2.0.4 also.

        A search through MOS finds several related articles, but not much that references 8i or 9i. It appears that there were several patches released to fix false ORA-01002 errors in the 8i and 9o timeframe – I wonder if Oracle also fixed missed ORA-01002 errors at the same time? Reference Doc ID 2361084.8, Bug 2361084, “ORA-1002 possible when not expected after ROLLBACK TO SAVEPOINT” Versions greater than/equal to 8.1.7.3 but less than 10.1.0.2

    • Here is the output from 10.2.0.2, where the same error message is also output:

      BANNER
      ---------------------------------------------------------
      Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
      PL/SQL Release 10.2.0.2.0 - Production
      CORE    10.2.0.2.0      Production
      TNS for 64-bit Windows: Version 10.2.0.2.0 - Production
      NLSRTL Version 10.2.0.2.0 - Production
       
      SQL>
      SQL> DROP TABLE t1;
      DROP TABLE t1
                 *
      ERROR at line 1:
      ORA-00942: table or view does not exist
       
      SQL>
      SQL> CREATE TABLE t1
        2  AS
        3  SELECT ROWNUM col1
        4  FROM   DUAL
        5  CONNECT BY ROWNUM 
      SQL> DECLARE
        2   CURSOR c1
        3   IS
        4     SELECT col1 FROM t1 FOR UPDATE;
        5   l1 t1.col1%TYPE;
        6  BEGIN
        7    OPEN c1;
        8    LOOP
        9      FETCH c1 INTO l1;
       10      EXIT WHEN c1%NOTFOUND;
       11      COMMIT;
       12    END LOOP;
       13    DBMS_OUTPUT.PUT_LINE(l1);
       14  END;
       15  /
      DECLARE
      *
      ERROR at line 1:
      ORA-01002: fetch out of sequence
      ORA-06512: at line 9
      
      • Dom Brooks says:

        So, I await with interest some results from Oracle 7 and Oracle 8/8i, maybe even earlier 9i versions.

        Like Mohamed, I thought had a vague recollection of this not being allowed in older versions. So when this change in behaviour was revealed by an upgrade, I was surprised. Surprised that it was even allowed in 9i but not having anything older to verify you start to blindly accept that this must have been the way it was, etc, etc.

  2. Dom Brooks says:

    The Oracle 7 documentation also backs up what Mohamed was saying (documentation can of course be wrong so anyone still running an Oracle 7 database – a verification much appreciated).

    From Oracle 7.3.4 documentation, http://download.oracle.com/docs/cd/A57673_01/DOC/dcommon/oin/index.htm:
    “All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. So, you cannot fetch from a FOR UPDATE cursor after a commit.”

    So, the 9.2.0.8 result – an aberration?

    • Houri Mohamed says:

      Dom,
      And here below is the 8i behaviour
      SQL> select * from v$version;

      BANNER
      —————————————————————-
      Oracle8i Enterprise Edition Release 8.1.7.4.0 – Production
      PL/SQL Release 8.1.7.4.0 – Production
      CORE 8.1.7.0.0 Production
      TNS for Solaris: Version 8.1.7.4.0 – Production
      NLSRTL Version 3.4.1.0.0 – Production

      SQL> drop table t1;

      Table dropped.

      SQL> CREATE TABLE t1 (col1 number);

      Table created.

      SQL> insert into t1 values(1);

      1 row created.

      SQL> insert into t1 values(2);

      1 row created.

      SQL> insert into t1 values(3);

      1 row created.

      SQL> insert into t1 values(4);

      1 row created.

      SQL> insert into t1 values(5);

      1 row created.

      SQL> commit;

      Commit complete.

      SQL> DECLARE
      2
      3 CURSOR c1
      4
      5 IS
      6
      7 SELECT col1 FROM t1 FOR UPDATE;
      8
      9 l1 t1.col1%TYPE;
      10
      11 BEGIN
      12
      13 OPEN c1;
      14
      15 LOOP
      16
      17 FETCH c1 INTO l1;
      18
      19 EXIT WHEN c1%NOTFOUND;
      20
      21 COMMIT;
      22
      23 END LOOP;
      24
      25 CLOSE c1;
      26
      27 DBMS_OUTPUT.PUT_LINE(l1);
      28
      29 END;
      30
      31 /
      DECLARE
      *
      ERROR at line 1:
      ORA-01002: fetch out of sequence
      ORA-06512: at line 17

      • Dom Brooks says:

        Fantastic Mohamed.

        Sooo, a 9i blip (possibly more restrictive to certain subversions – I’ve only tested 9.2.0.8)

        It’s funny – I hardly believed that this was possible in 9i until I saw what is now clear to be a reversion to the norm in this upgrade.

        And pre-9 being such a distant memory now, I convinced myself that it must have always been this way…

        What the story is with this phantom metalink note that I seem to remember is, I’m not sure. The only thing that makes sense is that I saw the one mentioned by myself and Charles above, saw the relevance of 10.2.0.5 onwards and bingo 1+1 =3…..

        9i is unsupported of course so you’d think this was all irrelevant but just how many 9i systems are still out there and does anyone on these do the above behaviour.

        Maybe I was unlucky and found the only 9i system with this coding practice (in 5 or 6 different messaging modules) in existence.

        Thanks

  3. Rasto Kmahi says:

    we were using 9i becuase of our ebusinness suite. Last week (may 2012) we upgraded directly from 9i to 11G. And I got the same error as fetch out of sequence. My code was the same as yours. And now in 11G it is not working. I was searching the net for a solution but I ended up. I will change my code.
    good luck .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 69 other followers

%d bloggers like this: