Skip to main content

Search

Welcome to Tibero GTS!

Example of Performing UPDATE in Current and Consistent Read Modes

Document Type | Technical Information

Category | App Development

Document Number | TDETI007

 

Overview

When executing SQL in Tibero, there are cases where both Current mode and Consistent Read mode are used within the same query.
This article guides you through the process of checking how these two read modes are applied within the same query during transaction processing, using account tables (acc1, acc2).
 
 
 

Method

Creating Example Table Data

drop table acc1;
drop table acc2;
create table acc1(accno number, amt number, tamt number);
create table acc2(accno number, amt number, tamt number);
insert into acc1 select level, 1000, 2000 from dual connect by level <= 100000;
insert into acc2 select level, 1000, 2000 from dual connect by level <= 100000;

ALTER TABLE acc1 ADD CONSTRAINT PK_acc1_accno PRIMARY KEY(accno); 
ALTER TABLE acc2 ADD CONSTRAINT PK_acc2_accno PRIMARY KEY(accno);

exec dbms_stats.gather_table_stats('TIBERO','ACC1',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 254');
exec dbms_stats.gather_table_stats('TIBERO','ACC2',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 254');

 

Executing UPDATE with Mixed Read Modes

In the following example, account1.balance is read in Current mode, while account2.balance is read in Consistent Read mode.

update ๊ณ„์ขŒ1
set ๊ณ„์ขŒ1.์ด์ž”๊ณ  
             = ๊ณ„์ขŒ1.์ž”๊ณ  + (select ๊ณ„์ขŒ2.์ž”๊ณ  from ๊ณ„์ขŒ2 where ๊ณ„์ขŒ2.๊ณ„์ขŒ๋ฒˆํ˜ธ=๊ณ„์ขŒ1.๊ณ„์ขŒ๋ฒˆํ˜ธ)
where ๊ณ„์ขŒ1.๊ณ„์ขŒ๋ฒˆํ˜ธ=7788;
  • Even if changes occur in ๊ณ„์ขŒ2 during the update, the update and delete statements use the starting point (before changes) values at the beginning of execution.
  • In the following execution example, through the UPDATE statement in Session B, ๊ณ„์ขŒ1.์ด์ž”๊ณ  becomes 1100 + 1000, that is, 2100.
No
Session A
Session B
1
SQL> select acc1.amt  acc1_amt, acc2.amt acc2_amt,   acc1.tamt  acc1_tamt, acc1.amt+acc2.amt sum_amt from acc1, acc2 where acc1.accno=7788 and acc2.accno=acc1.accno;  ACC1_AMT  ACC2_AMT ACC1_TAMT  SUM_AMT ------------ -------------- -------------- ------------  1000  1000  2000  2000
 
2
SQL> update acc1 set amt=amt+100 where accno=7788;
 
3
SQL> update acc2 set amt=amt+200 where accno=7788;
 
4
SQL> select acc1.amt  acc1_amt, acc2.amt acc2_amt,   acc1.tamt  acc1_tamt, acc1.amt+acc2.amt sum_amt from acc1, acc2 where acc1.accno=7788 and acc2.accno=acc1.accno;  ACC1_AMT  ACC2_AMT ACC1_TAMT  SUM_AMT ------------ -------------- -------------- ------------  1100  1000  2000  2000
SQL> select acc1.amt  acc1_amt, acc2.amt acc2_amt,   acc1.tamt  acc1_tamt, acc1.amt+acc2.amt sum_amt from acc1, acc2 where acc1.accno=7788 and acc2.accno=acc1.accno;  ACC1_AMT  ACC2_AMT ACC1_TAMT  SUM_AMT ------------ -------------- -------------- ------------  1000  1000  2000  2000
5
 
SQL> update acc1 set acc1.tamt = acc1.amt +(select acc2.amt from acc2 where acc2.accno = acc1.accno) where acc1.accno = 7788;
6
 
Waits for the row lock (row corresponding to accno=7788 in the acc1 table) caused by (no.2)
7
SQL> commit;
 
8
 
- Update is performed after commit in (no.6). - acc1.amt is the row being updated, so it is accessed in current read mode (acc1.amt uses the value 1100). - acc2.amt is accessed in consistent read mode (acc2.amt uses the value 1000).
9
SQL> select acc1.amt  acc1_amt, acc2.amt acc2_amt,   acc1.tamt  acc1_tamt, acc1.amt+acc2.amt sum_amt from acc1, acc2 where acc1.accno=7788 and acc2.accno=acc1.accno;  ACC1_AMT  ACC2_AMT ACC1_TAMT  SUM_AMT ------------ -------------- -------------- ------------  1100  1200  2100  2300
SQL> select acc1.amt  acc1_amt, acc2.amt acc2_amt,   acc1.tamt  acc1_tamt, acc1.amt+acc2.amt sum_amt from acc1, acc2 where acc1.accno=7788 and acc2.accno=acc1.accno;  ACC1_AMT  ACC2_AMT ACC1_TAMT  SUM_AMT ------------ -------------- -------------- ------------  1100  1200  2100  2300
10
 
SQL> commit;
11
SQL> select acc1.amt  acc1_amt, acc2.amt acc2_amt,   acc1.tamt  acc1_tamt, acc1.amt+acc2.amt sum_amt from acc1, acc2 where acc1.accno=7788 and acc2.accno=acc1.accno;  ACC1_AMT  ACC2_AMT ACC1_TAMT  SUM_AMT ------------ -------------- -------------- ------------  1100  1200  2100  2300
SQL> select acc1.amt  acc1_amt, acc2.amt acc2_amt,   acc1.tamt  acc1_tamt, acc1.amt+acc2.amt sum_amt from acc1, acc2 where acc1.accno=7788 and acc2.accno=acc1.accno;  ACC1_AMT  ACC2_AMT ACC1_TAMT  SUM_AMT ------------ -------------- -------------- ------------  1100  1200  2100  2300
Note 

Consistent Mode

  • SQL Trace Name: query
  • tbSQL Autotrace Name: consistent gets
  • It reads data blocks in a way that guarantees read consistency by verifying the TSN.
  • Most SELECT queries fall under this category, reading data consistently based on the query start time and returning the values as of the query start even if they change during reading.
  • When reading data blocks, the TSN (Tibero System Number) value is checked, and if this value has changed, the TSN number increases. In this case, a CR (Consistent Read) block operation occurs where the past block is read from the Undo Segment, and the CR block is used.
  • In SQL AUTOTRACE, it is displayed as consistent gets.
  • Even if reading a current block, if accessed in Consistent mode, it is also counted as consistent gets in autotrace.
 

Current Mode

  • SQL Trace Name: current
  • tbSQL autotrace Name: db block gets
  • Reads the final value of the block at the time of actual access, not at the query start time.
  • Occurs mainly in the following situations: 
    - During DML operations 
    - Large sorting operations requiring disk sort 
    - When performing SELECT FOR UPDATE 
    - When reading segment extent information during Table Full Scan

 

 

 

Related to