Overview

DML Flow

What is DML?

- Abbreviation of DML (Data Manipulation Language)

- Data manipulation language / Language used to practically process data stored in the database.

- Includes Insert/Delete/Update.

Method

Detailed Procedure

Shared Cache Lookup and Execution Plan Confirmation

Check the library cache to see if the same SQL statement exists. (Compares characters, spaces, and case sensitivity)

Y (Same SQL statement exists): Soft Parsing occurs (fetches parse-tree and query execution plan from library cache and executes).

N (Same SQL statement does not exist): Hard Parsing occurs (creates a new parse-tree, optimizer determines execution plan and executes, then stores it in library cache).

Undo Segment Allocation

Undo Segment Composition

Undo Header (stores transaction information) + Undo Record (stores actual transaction changes)

Transaction information stored in the Undo Header includes transaction ID, status, and block address value for the Undo of the transaction.

Undo Allocation Process

• Areas past the Undo Retention period in the current Undo Segment are reused. (Retention period can be changed by parameter)
• The above step is internally retried up to 3 times. (Online area usage call)
• If the Online area cannot be secured in the above step, Offline Undo Segment is activated as Online and used. (Activates inactive area instead of increasing file size)
• If the allocation area cannot be secured in the above step, an arbitrary other Undo Segment is taken. (If set to USGMT_STEAL_IGNORE_RETENTION=Y, areas not past the retention period are used. This parameter was made public from version 7.2.4.)
• If no area can be secured through the above process, the file size is increased (extended) and the Undo Segment area is expanded.

Check Block Presence in Buffer Cache

Check Free Buffer

Free Buffer is searched before actual data modification.

Locks the buffer and then copies the block to be modified from the data file.

The DBMS first searches the block to be read in the Buffer Cache; if not found, it reads from disk, loads it into Buffer Cache, and performs read/write operations.

Related parameter: DB_CACHE_SIZE (adjusts buffer cache size).

Apply to Redo Buffer

Change/Insert/Delete in Redo Buffer

Changes according to DML are stored in the Redo Buffer before Buffer Cache and through this buffer are stored in the Redo Log File.

If an instance failure occurs, the contents recorded in the log file are replayed to recover Cache Blocks, and uncommitted transactions are rolled back.

Store Previous Values in Undo Segment

Store Transaction Previous Values in Undo Segment

If the necessary transaction contents are stored in the Redo Log Buffer in the above step, the pre-change values are recorded in the Undo Segment secured in step 3.

This is used for rollback, read consistency maintenance, and transaction recovery as needed by the transaction.

Store in Data Buffer

Store Changed Values in Data Buffer

Stores changed data values in the DB Buffer Cache.

Through this operation, values requested by the transaction are changed and stored in the DB Buffer Cache.

Transaction End

Finalize Changed Values (commit) and End Transaction

• When commit is performed, TSN (Tibero System Number: database version or commit version, generated and applied when the transaction commits) and commit records are written to the Redo Buffer.
• LGWR writes the changes in the Redo Buffer to the Redo Log file.
• Commit information is stored in the transaction table in the Undo Segment header.
• DBWR writes values in the DB Buffer Cache to the Data file blocks, called CheckPoint. (The values in memory are now fully applied to the actual Datafile)
• If data is modified again after being allocated but not yet written to the data file on disk, the buffer block is called a Dirty Buffer. When a certain level of Dirty Buffers accumulates or a user issues a checkpoint command, the data is immediately written to the Data file. (The reason for waiting until a certain amount accumulates is to avoid performance issues from excessive repeated block I/O)

Additional Explanation

When Querying from Another Session Before Commit

Read consistency is maintained. (The session in transaction sees changed values, but other sessions see the original values: Read Consistency)

At the time of Select, a TSN for the query time is assigned and compared with the TSN of the block to maintain consistency. (Of course, after commit, the changed values are visible)