Multiple query read consistency

Nothing new and shiny this time, but a write up about an aspect of one of the cornerstones of Oracle: read consistency. The concepts manual has a very good chapter about this topic called Data Concurrency and Consistency. A project I was auditing had to select data from multiple tables and interface that data to another system. So I adviced the developers to make sure all the queries would query the data from the same timestamp to avoid seeing the related data in an inconsistent state. A simple piece of advice, but I realized I had never implemented it myself, nor did the developers. In fact, I have never seen production code that uses anything different than the default read committed transaction isolation level. Nevertheless I know quite a lot of batches and interfaces that would need "multiple query read consistency" to function correctly.

In the past I had done a few experiments with the serializable transaction isolation level and concluded that this was something especially for banks, and since I never worked for a bank (a 4 hour consultancy job excluded) I did not use that isolation level ever for real. The transaction level read consistency is applicable for "multiple query read consistency" as well, but it looks strange in my opinion: you start out with saying you are beginning a serializable transaction, but you are only querying. Since I like things to be clear, this didn't appeal to me very much. But it works.

Another option I considered were cursor expressions. You can combine all the queries with the use of this feature, but the documentation clearly states:

A nested cursor is implicitly opened when the containing row is fetched from the parent cursor.

So the subqueries will get executed at a different timestamp in PL/SQL. Option dismissed.

Next I investigated the dbms_flashback package. Provided that the undo_retention period is set sufficiently large, this looks like a neat solution. You start out your piece of code with:

dbms_flashback.enable_at_system_change_number(dbms_flashback.get_system_change_number);

This makes sure all subsequent queries will run as of this timestamp. When you are done querying, you end the section with:

dbms_flashback.disable;

And everything is back as normal. Looks good; in fact I adviced this piece of code to the developers. But then I wondered how they did this sort of thing in pre-Oracle9i times, and I returned to the Concepts Manual and realized it was the simple and ancient:

set transaction read only;

or, in PL/SQL:

dbms_transaction.read_only;

This one is the most simple and exactly what is needed in this situation. According to the documentation:

Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.

In the test below, you can see how all options work. The setup involves two tables: orders and order_lines, where the orders table contains a denormalized column called "total_price" which is the sum of all total_prices of the order_lines. Also note the on delete cascade foreign key between the two tables.

rwijk@ORA11G> create table orders (id, customer_name, total_price)
  2  as
  3  select 1, 'Customer A', 10 from dual union all
  4  select 2, 'Customer B', 20 from dual union all
  5  select 3, 'Customer C', 30 from dual
  6  /

Tabel is aangemaakt.

rwijk@ORA11G> alter table orders add constraint orders_pk primary key (id)
  2  /

Tabel is gewijzigd.

rwijk@ORA11G> create table order_lines (id, order_id, product, quantity, price_per_unit)
  2  as
  3  select 1, 1, 'product P', 2, 3 from dual union all
  4  select 2, 1, 'product Q', 1, 4 from dual union all
  5  select 3, 2, 'product P', 4, 3 from dual union all
  6  select 4, 2, 'product Q', 2, 4 from dual union all
  7  select 5, 3, 'product P', 1, 3 from dual union all
  8  select 6, 3, 'product Q', 3, 4 from dual union all
  9  select 7, 3, 'product R', 3, 5 from dual
 10  /

Tabel is aangemaakt.

rwijk@ORA11G> create sequence ol_seq start with 8 increment by 1
  2  /

Reeks is aangemaakt.

rwijk@ORA11G> alter table order_lines
  2    add constraint ol_pk
  3    primary key (id)
  4  /

Tabel is gewijzigd.

rwijk@ORA11G> alter table order_lines
  2    add constraint ol_orders_fk
  3    foreign key (order_id)
  4    references orders(id)
  5    on delete cascade
  6  /

Tabel is gewijzigd.

rwijk@ORA11G> create procedure add_order_line
  2  ( p_order_id       in orders.id%type
  3  , p_product        in order_lines.product%type
  4  , p_quantity       in order_lines.quantity%type
  5  , p_price_per_unit in order_lines.price_per_unit%type
  6  )
  7  is
  8  begin
  9    insert into order_lines
 10    ( id
 11    , order_id
 12    , product
 13    , quantity
 14    , price_per_unit
 15    )
 16    values
 17    ( ol_seq.nextval
 18    , p_order_id
 19    , p_product
 20    , p_quantity
 21    , p_price_per_unit
 22    );
 23    update orders
 24       set total_price = total_price + p_quantity * p_price_per_unit
 25     where id = p_order_id
 26    ;
 27  end;
 28  /

Procedure is aangemaakt.

rwijk@ORA11G> create function sleep(p_seconds in number) return number
  2  as
  3  begin
  4    dbms_lock.sleep(p_seconds);
  5    return null;
  6  end;
  7  /

Functie is aangemaakt.

rwijk@ORA11G> exec dbms_stats.gather_table_stats(user,'orders',cascade=>true)

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> exec dbms_stats.gather_table_stats(user,'order_lines',cascade=>true)

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> alter session set plsql_optimize_level = 0
  2  /

Sessie is gewijzigd.

The PLSQL_OPTIMIZE_LEVEL is set to 0 to avoid the automatic bulk fetching since version 10. A cursor for loop will now fetch row-by-row. And because of the function sleep it will do so with an interval.

rwijk@ORA11G> begin
  2    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
  3    for r1 in
  4    ( select id
  5           , customer_name
  6           , total_price
  7           , sleep(5) s
  8        from orders
  9    )
 10    loop
 11      dbms_output.put_line
 12      ( 'Order ' || to_char(r1.id) ||
 13        ' having total price ' || to_char(r1.total_price) ||
 14        ' consisting of:'
 15      );
 16      for r2 in
 17      ( select product
 18             , quantity * price_per_unit total_price_per_order_line
 19          from order_lines
 20         where order_id = r1.id
 21      )
 22      loop
 23        dbms_output.put_line
 24        ( '* ' || r2.product || ': ' || to_char(r2.total_price_per_order_line) ||
 25          '     (' || to_char(sysdate,'hh24:mi:ss') || ')'
 26        );
 27      end loop;
 28    end loop;
 29    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 30  end;
 31  /
23:04:38
Order 1 having total price 10 consisting of:
* product P: 6     (23:04:43)
* product Q: 4     (23:04:43)
Order 2 having total price 20 consisting of:
* product P: 12     (23:04:48)
* product Q: 8     (23:04:48)
Order 3 having total price 30 consisting of:
* product P: 3     (23:04:53)
* product Q: 12     (23:04:53)
* product R: 15     (23:04:53)
23:04:53

PL/SQL-procedure is geslaagd.

This piece of code will be used throughout this test. It shows all orders with their order_lines using two queries. You want both the queries to execute at the same time. If you don't enforce this, you may end up with output as can be seen in the next piece of code:

rwijk@ORA11G> remark
rwijk@ORA11G> remark  While the below block is executing,
rwijk@ORA11G> remark  open a new session and execute:
rwijk@ORA11G> remark
rwijk@ORA11G> remark  exec add_order_line(2,'product R',4,10)
rwijk@ORA11G> remark  delete orders where id = 3;
rwijk@ORA11G> remark  commit;
rwijk@ORA11G> remark
rwijk@ORA11G> begin
  2    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
  3    for r1 in
  4    ( select id
  5           , customer_name
  6           , total_price
  7           , sleep(5) s
  8        from orders
  9    )
 10    loop
 11      dbms_output.put_line
 12      ( 'Order ' || to_char(r1.id) ||
 13        ' having total price ' || to_char(r1.total_price) ||
 14        ' consisting of:'
 15      );
 16      for r2 in
 17      ( select product
 18             , quantity * price_per_unit total_price_per_order_line
 19          from order_lines
 20         where order_id = r1.id
 21      )
 22      loop
 23        dbms_output.put_line
 24        ( '* ' || r2.product || ': ' || to_char(r2.total_price_per_order_line) ||
 25          '     (' || to_char(sysdate,'hh24:mi:ss') || ')'
 26        );
 27      end loop;
 28    end loop;
 29    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 30  end;
 31  /
23:04:54
Order 1 having total price 10 consisting of:
* product P: 6     (23:04:59)
* product Q: 4     (23:04:59)
Order 2 having total price 20 consisting of:
* product P: 12     (23:05:04)
* product Q: 8     (23:05:04)
* product R: 40     (23:05:04)
Order 3 having total price 30 consisting of:
23:05:09

PL/SQL-procedure is geslaagd.

This query output is clearly wrong, even though the database moved from one consistent state to the other. So here are some of the earlier discussed alternatives. You'll have to trust me that I opened another session to execute the statements mentioned in the remarks.

rwijk@ORA11G> insert into orders
  2  select 3, 'Customer C', 30 from dual
  3  /

1 rij is aangemaakt.

rwijk@ORA11G> update orders
  2     set total_price = total_price - 40
  3   where id = 2
  4  /

1 rij is bijgewerkt.

rwijk@ORA11G> insert into order_lines
  2  select 5, 3, 'product P', 1, 3 from dual union all
  3  select 6, 3, 'product Q', 3, 4 from dual union all
  4  select 7, 3, 'product R', 3, 5 from dual
  5  /

3 rijen zijn aangemaakt.

rwijk@ORA11G> delete order_lines
  2  where price_per_unit = 10
  3  /

1 rij is verwijderd.

rwijk@ORA11G> commit
  2  /

Commit is voltooid.

Restore the situation to the original state.

rwijk@ORA11G> set transaction isolation level serializable
  2  /

Transactie is ingesteld.

rwijk@ORA11G> remark
rwijk@ORA11G> remark  While the below block is executing,
rwijk@ORA11G> remark  open a new session and execute:
rwijk@ORA11G> remark
rwijk@ORA11G> remark  exec add_order_line(2,'product R',4,10)
rwijk@ORA11G> remark  delete orders where id in (1,3);
rwijk@ORA11G> remark  commit;
rwijk@ORA11G> remark
rwijk@ORA11G> begin
  2    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
  3    for r1 in
  4    ( select id
  5           , customer_name
  6           , total_price
  7           , sleep(5) s
  8        from orders
  9    )
 10    loop
 11      dbms_output.put_line
 12      ( 'Order ' || to_char(r1.id) ||
 13        ' having total price ' || to_char(r1.total_price) ||
 14        ' consisting of:'
 15      );
 16      for r2 in
 17      ( select product
 18             , quantity * price_per_unit total_price_per_order_line
 19          from order_lines
 20         where order_id = r1.id
 21      )
 22      loop
 23        dbms_output.put_line
 24        ( '* ' || r2.product || ': ' || to_char(r2.total_price_per_order_line) ||
 25          '     (' || to_char(sysdate,'hh24:mi:ss') || ')'
 26        );
 27      end loop;
 28    end loop;
 29    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 30  end;
 31  /
23:05:09
Order 1 having total price 10 consisting of:
* product P: 6     (23:05:14)
* product Q: 4     (23:05:14)
Order 2 having total price 20 consisting of:
* product P: 12     (23:05:19)
* product Q: 8     (23:05:19)
Order 3 having total price 30 consisting of:
* product P: 3     (23:05:24)
* product Q: 12     (23:05:24)
* product R: 15     (23:05:24)
23:05:24

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> rollback
  2  /

Rollback is voltooid.

rwijk@ORA11G> insert into orders
  2  select 3, 'Customer C', 30 from dual
  3  /

1 rij is aangemaakt.

rwijk@ORA11G> update orders
  2     set total_price = total_price - 40
  3   where id = 2
  4  /

1 rij is bijgewerkt.

rwijk@ORA11G> insert into order_lines
  2  select 5, 3, 'product P', 1, 3 from dual union all
  3  select 6, 3, 'product Q', 3, 4 from dual union all
  4  select 7, 3, 'product R', 3, 5 from dual
  5  /

3 rijen zijn aangemaakt.

rwijk@ORA11G> delete order_lines
  2  where price_per_unit = 10
  3  /

1 rij is verwijderd.

rwijk@ORA11G> commit
  2  /

Commit is voltooid.

rwijk@ORA11G> remark
rwijk@ORA11G> remark  While the below block is executing,
rwijk@ORA11G> remark  open a new session and execute:
rwijk@ORA11G> remark
rwijk@ORA11G> remark  exec add_order_line(2,'product R',4,10)
rwijk@ORA11G> remark  delete orders where id in (1,3);
rwijk@ORA11G> remark  commit;
rwijk@ORA11G> remark
rwijk@ORA11G> begin
  2    dbms_flashback.enable_at_system_change_number
  3    ( dbms_flashback.get_system_change_number
  4    );
  5    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
  6    for r1 in
  7    ( select id
  8           , customer_name
  9           , total_price
 10           , sleep(5) s
 11        from orders
 12    )
 13    loop
 14      dbms_output.put_line
 15      ( 'Order ' || to_char(r1.id) ||
 16        ' having total price ' || to_char(r1.total_price) ||
 17        ' consisting of:'
 18      );
 19      for r2 in
 20      ( select product
 21             , quantity * price_per_unit total_price_per_order_line
 22          from order_lines
 23         where order_id = r1.id
 24      )
 25      loop
 26        dbms_output.put_line
 27        ( '* ' || r2.product || ': ' || to_char(r2.total_price_per_order_line) ||
 28          '     (' || to_char(sysdate,'hh24:mi:ss') || ')'
 29        );
 30      end loop;
 31    end loop;
 32    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 33    dbms_flashback.disable;
 34  end;
 35  /
23:05:24
Order 1 having total price 10 consisting of:
* product P: 6     (23:05:29)
* product Q: 4     (23:05:29)
Order 2 having total price 20 consisting of:
* product P: 12     (23:05:34)
* product Q: 8     (23:05:34)
Order 3 having total price 30 consisting of:
* product P: 3     (23:05:39)
* product Q: 12     (23:05:39)
* product R: 15     (23:05:39)
23:05:39

PL/SQL-procedure is geslaagd.

rwijk@ORA11G> rollback
  2  /

Rollback is voltooid.

rwijk@ORA11G> insert into orders
  2  select 3, 'Customer C', 30 from dual
  3  /

1 rij is aangemaakt.

rwijk@ORA11G> update orders
  2     set total_price = total_price - 40
  3   where id = 2
  4  /

1 rij is bijgewerkt.

rwijk@ORA11G> insert into order_lines
  2  select 5, 3, 'product P', 1, 3 from dual union all
  3  select 6, 3, 'product Q', 3, 4 from dual union all
  4  select 7, 3, 'product R', 3, 5 from dual
  5  /

3 rijen zijn aangemaakt.

rwijk@ORA11G> delete order_lines
  2  where price_per_unit = 10
  3  /

1 rij is verwijderd.

rwijk@ORA11G> commit
  2  /

Commit is voltooid.

rwijk@ORA11G> remark
rwijk@ORA11G> remark  While the below block is executing,
rwijk@ORA11G> remark  open a new session and execute:
rwijk@ORA11G> remark
rwijk@ORA11G> remark  exec add_order_line(2,'product R',4,10)
rwijk@ORA11G> remark  delete orders where id in (1,3);
rwijk@ORA11G> remark  commit;
rwijk@ORA11G> remark
rwijk@ORA11G> begin
  2    dbms_transaction.read_only;
  3    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
  4    for r1 in
  5    ( select id
  6           , customer_name
  7           , total_price
  8           , sleep(5) s
  9        from orders
 10    )
 11    loop
 12      dbms_output.put_line
 13      ( 'Order ' || to_char(r1.id) ||
 14        ' having total price ' || to_char(r1.total_price) ||
 15        ' consisting of:'
 16      );
 17      for r2 in
 18      ( select product
 19             , quantity * price_per_unit total_price_per_order_line
 20          from order_lines
 21         where order_id = r1.id
 22      )
 23      loop
 24        dbms_output.put_line
 25        ( '* ' || r2.product || ': ' || to_char(r2.total_price_per_order_line) ||
 26          '     (' || to_char(sysdate,'hh24:mi:ss') || ')'
 27        );
 28      end loop;
 29    end loop;
 30    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 31  end;
 32  /
23:05:39
Order 1 having total price 10 consisting of:
* product P: 6     (23:05:44)
* product Q: 4     (23:05:44)
Order 2 having total price 20 consisting of:
* product P: 12     (23:05:49)
* product Q: 8     (23:05:49)
Order 3 having total price 30 consisting of:
* product P: 3     (23:05:54)
* product Q: 12     (23:05:54)
* product R: 15     (23:05:54)
23:05:54

PL/SQL-procedure is geslaagd.