Oracle Scratchpad

October 12, 2015

Read Consistency

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 7:25 pm BST Oct 12,2015

I posted a note a few days ago about read consistency, the Cross Session PL/SQL Function Result Cache, deterministic functions, and scalar subqueries. The intent of the article was to make clear the point that while you might think that declaring a PL/SQL function to be deterministic or in the PL/SQL Result Cache might make a query that calls the function perform faster, if that function contained its own SQL statement then your code might not be producing self-consistent results and (even worse) if you had used the Result Cache option your code might actually cause other session to get wrong results if you tried to “set transaction read only” or “alter session set isolation_level = serializable”

It occurred to me a couple of days after writing that article that perhaps there’s a much more basic point I ought to make as well. I’ll make it about PL/SQL but it’s something that applies across all programming languages – it merits being italicised and emphasised, and possibly even the addition of 5 exclamation marks:

Any time you execute more than one SQL statement in a PL/SQL procedure the results of executing that procedure may not be self-consistent unless you have explicitly locked your session SCN to a fixed value!!!!!

Similarly: if you run a report from SQL*Plus which goes: “select this, select that, select the total of the other” – your report may not be self-consistent; if you have a web-application which goes: “select the customer balance, select the customer orders outstanding, select the customer credit notes” – your screenful may not be self-consistent.

By default Oracle operates at statement-level read-consistency for SQL. In a multi-user system if you run two SQL statements and someone else changes the data and commits between your two statement executions then the result from the second statement may not be consistent with the result from the first. Here’s a sillly little bit of code you can use to demonstrate the concept:

set serveroutput on

create table t1
nologging
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4 
) 
select 
        rownum id,    
        rownum n1,
        rownum n2,
        rpad('x',10,'x')  small_vc,
        rpad('x',100,'x') padding 
from 
        generator v1 
; 


create table t2 ( 
        id, 
        v1, 
        constraint t2_pk primary key(id) 
) 
organization index 
as 
select 
        cast(1 as number (6,0)), 
        cast('one' as varchar2(10)) 
from dual 
;
 
begin 
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 1'
        );

        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T2',
                method_opt       => 'for all columns size 1'
        );
end;
/

/*
   ----------------------------------------------------
   Table t3 only relevant to "serializable" experiments
   ----------------------------------------------------

create table t3 (
        id      number,
        v1      varchar2(10),
        padding varchar2(100) default rpad('x',100,'x')
)
initrans 4
;

alter table t3 add constraint t3_pk primary key(id) using index(
        create unique index t3_pk on t3(id) initrans 4
)
;

insert into t3 values (0,'0',rpad('x',100,'x'));
commit;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T3',
                method_opt       => 'for all columns size 1'
        );
end;
/

   ----------------------------------------------------
   End of optional t3 definition
   ----------------------------------------------------
*/

prompt  ===========================================================
prompt  Four second pause - only needed to avoid flashback problems
prompt  ===========================================================

execute dbms_lock.sleep(4)

-- ------------------------------------------------------------------------------
-- Options for "fixing" the SCN - plenty of scope for ORA-08177 when serializable
-- ------------------------------------------------------------------------------

-- set transaction read only;
-- execute dbms_flashback.enable_at_time(systimestamp)
-- execute dbms_flashback.enable_at_system_change_number(dbms_flashback.get_system_change_number)
-- alter session set isolation_level = serializable;

prompt  =====================
prompt  Starting PL/SQL block
prompt  =====================

declare
        m_v1    varchar2(10);
begin
        for r in (select * from t1 where rownum <= 10) loop

                select  v1
                into    m_v1
                from    t2
                where   t2.id = r.n2 - r.n1 + 1
                ;

--              insert into t3 values(r.id, m_v1, rpad('x',100));

                dbms_output.put_line(r.id || ' - ' || m_v1);
                dbms_lock.sleep(1);

        end loop;

end;
/

execute dbms_flashback.disable;
commit;

--
--      Code to be run from a second session once the 
--      first session displays the "loop running" banner
--

begin
        for r in 1..20 loop
                update t2 set v1 = lpad(r,10,0);
                commit;
                dbms_lock.sleep(0.5);
        end loop;
end;
.

The code covers several different tests – the basic test and the three “read-only” tests don’t need table t3 at all, which exists only so that we have something to insert into (and show a possible ORA-08177) for the serializable test. If you do the serializable test you might want to note what happens if you eliminate the initial insert of id = 0; you may also want to increase the number of rows selected, the number of cycles through the update loop, and the sleep times.

In principle the code emulates a classic “nested loop join done in SQL”, with one row in table t2, and a CURSOR FOR LOOP select from t1 being used to drive a select from t2 “for each row”. The peculiar predicate “t2.id = r.n2 – r.n1 + 1” inside the loop is an attempt to minimise the risk of any future release of the PL/SQL optimizer getting so clever that it puts the “constant” query for “t2.id = 1” outside the loop.

As the main loop cycles once per second, selecting the same row from t2 on each cycle, another session updates the row and commits twice every second. You might have thought that the main loop would keep returning the same value on every cycle – but each select starts at a new SCN. If you don’t take some sort of defensive action (set transaction read only, dbms_flashback) then you’ll get output similar to the following:


=================
Four second pause
=================

PL/SQL procedure successfully completed.

=====================
Starting PL/SQL block
=====================
1 - one
2 - 0000000002
3 - 0000000004
4 - 0000000006
5 - 0000000008
6 - 0000000010
7 - 0000000012
8 - 0000000014
9 - 0000000016
10 - 0000000018

PL/SQL procedure successfully completed.

I have seen many sites where this type of code is used. Typically it’s justified because the referenced table is a “very static” code table or a table that’s not supposed to change while the main loop is running – but eventually Murphy’s law will raise its ugly head.

(My favourite invocation of Murphy’s law came from my A-level physics teacher with respect to experimentation: “Constants aren’t and variables don’t”.)

2 Comments »

  1. True, but I’d also contend that people have “put up” with this behaviour for time immemorial. You think of any of the gazillions of old Oracle Forms applications out there which were not strictly self-consistent within a couple of blocks on screen, eg

    – master block on table “T” is refreshed once (showing cols 1,2,3)
    – each time you click on a row, then a detail block, also on table “T” to show cols 1,2,3,4,5,6,7 etc would be a new query each time

    or, not even self-consistent within a *single* block

    – refresh block
    – for each row as block is refreshed, fire post-query trigger, which in turn ran more SQL’s

    Is it “right” ? Probably not. Is it “adequate” – probably yes in the vast majority of cases. When people would detect it, the response was often “Hit refresh and tell me if its still a problem” :-)

    Comment by connormcdonald — October 13, 2015 @ 5:29 am BST Oct 13,2015 | Reply

  2. Connor,

    The reasons why it’s worth putting out a reminder with specific reference to PL/SQL are that (a) it’s easier for people to forget that there is a read-consistency effect to consider, and (b) a PL/SQL procedure is more likely to do something where read-consistency matters.

    With forms and web applications, developers tend to be aware that they are sending many (SQL) calls to the database and are therefore more likely to think of the cases where they need to be careful about consistency. With a single call to a PL/SQL procedure it’s too easy to think that “a single call to the database = read-consistency” and do it wrong.

    (You forgot to mention SQL*RPT and its wonderful ability to produce reports where the total didn’t match the sum of the sub-totals after running for hours ;)

    Comment by Jonathan Lewis — October 13, 2015 @ 8:41 am BST Oct 13,2015 | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.