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…