Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

Current Read Mode

Document Type | Technical Information

Category | Administration

Document Number | TADTI065

 

Overview

This guide explains how to perform updates in Current Mode, which reads the final value at the moment the actual block is accessed, not at the SQL start time, through examples.
 

Comparison: Consistent Mode VS Current Mode

 
Consistent Mode
Current Mode
Name
(SQL trace)
Query
Current
Name
(tbSQL autotrace)
consistent gets
db block gets
Definition
Mode that reads data blocks with read consistency guaranteed through TSN verification process
Mode that reads the final value at the moment the actual block is accessed
Features
- Most SELECT queries use this mode
- Reads consistently based on the query start time
- Even if values change during reading, reads values as of query start time
Main Occurrences
- During DML or large-scale sorts requiring disk sort
- When performing SELECT FOR UPDATE
- When reading extent information of a target segment during table full scan

 

Method

Creating Example Table Data

drop table acc1;
create table acc1(accno number, amt number, tamt number);
insert into acc1 select level, 1000, 2000 from dual connect by level <= 100000;
ALTER TABLE acc1 ADD CONSTRAINT PK_acc1_accno PRIMARY KEY(accno);
exec dbms_stats.gather_table_stats('TIBERO','ACC1',estimate_percent=>100,method_opt=>'FOR ALL
COLUMNS SIZE 254');

 

Performing Read Operation in Current Mode

  • Read account balance in Current mode.
  • If a change occurs to account balance during update, it will be affected. (Uses current value)
update account
set total_balance = balance
where account_number=7788;

In the example execution below, the account's total_balance becomes 1100 (current read mode applied) through the UPDATE statement in Session B.
No.
Session A
Session B
1
SQL> select amt, tamt
from acc1
where accno=7788;
AMT      TAMT
---------- ----------
     1000       2000
 
2
SQL> update acc1
set amt=amt+100
where accno=7788;
 
3
SQL> select amt, tamt
from acc1
where accno=7788;
AMT      TAMT
---------- ----------
     1100       2000
SQL> select amt, tamt
from acc1
where accno=7788;
AMT       TAMT
---------- ----------
      1000      2000
4
 
SQL> update acc1
set tamt=amt
where accno=7788;
5
 
(row lock occurred at no. 2)
(waiting for row in acc1 table where accno=7788)
6
SQL> commit;
 
7
 
- After commit at no. 6, update is performed
- Since amt column is the update target row, current read mode is applied (amt value 1100 used)
8
SQL> select amt, tamt
from acc1
where accno=7788;
AMT      TAMT
---------- ----------
     1100       2000
SQL> select amt, tamt
from acc1
where accno=7788;
AMT      TAMT
---------- ----------
     1100       1100
9
 
SQL> commit;
10
SQL> select amt, tamt
from acc1
where accno=7788;
AMT      TAMT
---------- ----------
     1100       1100
SQL> select amt, tamt
from acc1
where accno=7788;
AMT      TAMT
---------- ----------
     1100       1100
 

 

Related to