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.
Good One!
ReplyDeleteReally Explains read consitency with example!
Was not able to understand few Oracle feedback as it was in different langueage.
really nice blog to learn
ReplyDelete