Skip to main content

Search

Welcome to Tibero GTS!

How to Perform UPDATE in Current, Consistent Read Mode

Document Type | Technical Information

Category | Administration

Applicable Product Version | 7FS02PS

Document Number | TADTI100

Overview

This explains how to perform UPDATE in Current, Consistent Read mode using an example.
 
 

Method

 

Test Environment

[tibero7@rocky9.4:T724:/home/tibero]tbboot -version

Tibero 7 (DB 7.2.4) Build 303667
Linux bistro-build-centos_8.4 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux version (little-endian)

Patch files (FS02PS_700093a FS02PS_700103a FS02PS_344166f FS02PS_700110a FS02PS_342949f)
Compiled with "cc -no-pie -ggdb -fstack-protector-all -fno-strict-aliasing -fno-omit-frame-pointer -pipe -O3 -D_OPT_COMPILED -U_FORTIFY_SOURCE -D_USE_VIP -Wall -W -Werror-implicit-function-declaration -Wno-unused-parameter -Wpointer-arith -Wdeclaration-after-statement -DHAVE_CONFIG_H -D_WORDSIZE=64 -D_OS_LINUX -D_LINUX_X86_64 -D_X86 -DUSE_ASSERT -DTSAM -DTSAM_NO_ESDS_SEQUENCE -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -D_USE_FSYNC -D_CHECK_LICENSE -D_STBLOG -D_TAC -DFD_SETSIZE=8192 -D_CM_OBSERVER -D_USE_FALLOCATE -D_GNU_SOURCE -D_GNU_SOURCE -D_GNU_SOURCE -D_GNU_SOURCE -DHAVE_EPOLL"
Compiled at Oct 13 2025 01:59:51 build seq 303667 init rev {2023-11-24}

 

Create 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');

 

Example Execution 

The following example demonstrates a case where Current and Consistent mode reads are used together. 

  • Current mode: Account1.balance
  • Consistent mode: Account2.balance update Account1
update Account1 
	set Account1.total_balance 
	= Account1.balance + (select Account2.balance from Account2 where Account2.account_number=Account1.account_number) 
    where Account1.account_number=7788;

Account1.balance is read in Current mode, and Account2.balance is read in Consistent mode.

Even if changes occur in Account2 during the update, the update and delete statements use the starting point (pre-change) values.

 

In the following execution example, through the UPDATE statement of Session B, Account1.total_balance 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

 
2SQL>update acc1
set amt=amt+100
where accno=7788;		 
3SQL>update acc2
set amt=amt+200
where accno=7788;		 
4SQL>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        2000          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
----------    ----------   ----------     ----------
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 (no.2) is waiting for the row lock (row corresponding to accno=7788 in acc1 table) that occurred
7SQL>commit; 
8 

- Update is performed after (no.7) commit

- acc1.amt is the row being updated, so it is read in current mode (acc1.amt value of 1100 is used).
- acc2.amt is read in consistent mode (acc2.amt value of 1000 is used)

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        2000           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
- Name in SQL trace: query
- Name in tbSQL autotrace: consistent gets

  • Reads data blocks with read consistency guaranteed by checking TSN (Tibero System Number).
  • Mostly applies to SELECT queries, reading data consistently based on the query start time, so even if values change during reading, it reads the values as of the query start time.
  • Reads while checking TSN values; if values change, the TSN number increases, causing CR (Consistent Read) block operations that read past blocks from the undo segment, which is displayed as consistent gets in SQL AUTOTRACE.
  • Even when reading current blocks, if read in consistent mode, it is displayed as consistent gets in SQL AUTOTRACE.

 

Current Mode
- Name in SQL trace: current
- Name in tbSQL autotrace: db block gets

Reads the final value at the actual block access time, not at the SQL start time. Common cases include:

  • DML or large-scale sorting requiring disk sort
  • SELECT FOR UPDATE execution
  • Reading extent information of target segments during full table scans

 

 

 

Related to