Shulou(Shulou.com)05/31 Report--

What are the Oracle transaction processes? in view of this question, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Transaction table:

The transaction table is stored in the undo segment header (undo segment header block), and each undo segment can hold up to 47 transactions.

The transaction table stores transaction records by row, with one transaction record per row.

After the transaction starts, the service process allocates a XID and stores the transaction information (XID UBA) in the segment header block of the undo.

Oracle tries to use one rollback segment per transaction. If there are too many transactions, there will be rollback segment reuse, and multiple transactions use the same rollback segment. And Oracle distributes transactions evenly in the rollback segment.

View current transaction information

Select xid,xidusn,xidslot,xidsqn,ubablk,ubafil from v$transaction; XID XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL06001C004B040000 6 28 1099 711 3 view all rollback segments. SYS@prod > Select * from vault rollname; 0 SYSTEM 1 _ SYSSMU1_3724004606 $2 _ SYSSMU2_2996391332 $3 _ SYSSMU3_1723003836 $4 _ SYSSMU4_1254879796 $5 _ SYSSMU5_898567397 $6 _ SYSSMU6_1263032392 $7 _ SYSSMU7_2070203016 $8 _ SYSSMU8_517538920 $9 _ SYSSMU9_1650507775 $10 _ SYSSMU10_1197734989 $you can see from the transaction information that the current transaction is using paragraph 6 undo. Find the location of the header block of undo paragraph 6: SYS@prod > select header_file,header_block from dba_segments where segment_name ='_ SYSSMU6_1263032392 $'; document HEADER_FILE HEADER_BLOCK 3 2083 is the header block of undo segment. You can dump it to view the transaction table: Alter system dump datafile 3 block 208select dbms_rowid.rowid_relative_fno (rowid), dbms_rowid.rowid_block_number (rowid) block,idfrom t1DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) BLOCK ID 1 91041 5 1 91041 5 1 91041 5 to view the location of the data block in the row, and then dump to view the block structure.

Transaction slot ITL:

The transaction slot is stored in the head of the data block, and the transaction modifies a data block, and the transaction information needs to be recorded in the transaction slot.

XID is both a number and an address.

1. The head block of which rollback segment is used

two。 Which line is used in the segment header block to record the transaction.

3. How many times does it cover? (how many times does it recycle).

Let's first briefly describe the flow of the transaction:

1. To start a transaction, first Oracle assigns XID to the transaction, finds a rollback segment, stores the transaction information in the rollback segment header block, assigns a undo block to the transaction, and writes the address of the undo block to the transaction table (UBA address). two。 The transaction is ready to modify a data block and write transaction information (XID, UBA (this UBA points to the corresponding undo block) in the transaction slot of the header of the data block. 3. Start modifying the data, storing the pre-modified image of the block in the undo block.

The UBA in the transaction table is different from that in the transaction slot:

UBA in the transaction table:

The UBA in the transaction table is the last undo block pointing to the transaction operation and where the transaction rollback begins.

When rollback rollback, the starting point of the transaction rollback is located directly according to the UBA in the transaction table.

And know that there is a string between the rollback block and the rollback block.

UBA in the transaction slot:

The UBA of the transaction slot in the data block points to the corresponding undo block, which is meant to speed up the efficiency of constructing CR block.

When executing select, if the service process finds that there is a transaction in progress and is not committed, it will combine the current block and the undo block to generate the CR block (the corresponding undo block can be found more quickly).

The significance of recording XID in the transaction slot is to point to the transaction table and locate it directly to the location of the transaction table, which is related to the way Oracle is submitted. The specific reasons are described later.

The location that needs to be modified at the beginning of an DML transaction:

The transaction table of the rollback segment header has been modified

The transaction slot in the header of the data block is modified

Undo block modified

The row data of the data block is modified

(all of the above four changes will generate redo)

View the number of transaction slots:

Select ini_trans,max_trans from dba_tables where table_name ='T1'

Contention for transaction slots:

Session A starts the first transaction to modify a row of data in the block

Need to get a transaction slot in this data block (uncommitted)

(no commit transaction slot cannot be overwritten, only transaction slot that the transaction has committed can be overwritten)

Session B starts a second transaction to modify another row of data in the block

You also need to get a transaction slot in this data block

When the transaction slot gets the upper limit

Another transaction modifies other rows of the data block and needs to wait for the transaction slot to be released.

Resolve:

Can increase pctfree and reduce contention

In order to end the contention for transaction slots in Oracle, Oracle distributes insert operations across multiple blocks.

However, there is nothing to do with update and delete, and only transaction slots can be added, so transaction slot contention is easy to occur between update and delete.

The significance of recording XID in the transaction slot:

Oracle is combined with the removal of delay blocks to achieve fast submission:

If a transaction modifies 1000 blocks, the transaction information exists in 1001 blocks (undo segment header block)

When a transaction commits, it is slow to change the transaction record to a committed state at the location of 1001 blocks.

There may also be a situation where the transaction modifies 1000 blocks, and by the time it is about to commit, 800 blocks have already been written to disk.

It is impossible for Oracle to re-read these 800blocks into disk to modify the transaction recorded in the transaction slot of the block header to committed state.

Methods for Oracle to delay block removal:

When a transaction commits, Oracle updates only the transaction information of the undo segment header, depending on the number of buffer, and only part of the buffer is updated, and the remaining buffer Oracle clears the transaction record the next time these blocks are read.

Therefore, the transaction slot record in the data block may not be accurate. If the transaction recorded by the transaction slot in the data block is not committed, Oracle also needs to further judge whether the transaction is committed according to the XID to undo segment header transaction table in the transaction slot header. If the undo segment header transaction table records that the transaction has been committed, then Oracle will choose to trust the undo segment header and then modify the data block. And the transaction record in the transaction slot of the previous transaction is modified to be committed.

There are several ways to submit Oracle:

When there are few data blocks modified by the transaction:

When a transaction is committed

Modify the transaction status of the undo segment header record

Modify the transaction status recorded in the transaction slot of the block header

Modify the data row flag (transaction slot number, pointing to transaction slot, that is, Oracle row-level lock)

The blocks modified by transactions generally take a long time:

When a transaction is committed

The transaction table in the undo segment header and the transaction state in the transaction slot in the data block header are modified to committed.

The data row tag (row lock) clears the lock the next time select accesses.

(this is why select sometimes generates redo logs.)

There are many times when a transaction modifies blocks:

When a transaction commits

Only the record of the undo segment header is modified.

Transaction slot, the data row tag (row lock) modifies the clear lock on the next select access.

Another situation:

If a data block has not been read into buffer cache for a long time, and the transaction slot and lock of the data block have not been cleared, if it is read at this time, the service process will go to the transaction table in the header of the undo segment to determine whether the transaction has been committed. However, when the service process reads the transaction table in the header of the undo section, it finds that the transaction table has been overwritten 15 times, and Oracle will determine that the transaction has been committed, because the transaction cannot be overwritten in the transaction table. The service process then clears the record of the block transaction slot and clears the block row lock.

Summary of Select process:

(each row of data in the block will have a tag pointing to the transaction slot)

When the client executes the select query, the service process receives the request and reads the data blocks that meet the requirements into the buffer cache, and the service process reads the rows in the data block first.

If the business slot mark (row lock) is on the row of the data block, the service process goes to the transaction slot to see if the transaction recorded in that transaction slot has been committed. (due to the principle of fast commit and delayed block cleanup, the lock information does not represent the transaction.)

If the transaction recorded in the transaction slot has been committed, the service process clears the transaction slot mark (row lock) of the row record in the data block and reads the row data directly.

If the transaction recorded in the transaction slot is not committed, the Oracle will suspect that the service process will read the transaction status from the transaction table in the undo section header according to the XID in the transaction slot (the meaning of recording XID in the transaction slot).

If the transaction status in the transaction table is committed, the service process determines that the transaction corresponding to this row of data has been committed, clears the row flag, and changes the uncommitted state in the transaction slot to committed.

If the transaction status in the transaction table is uncommitted, Oracle determines that the transaction corresponding to the row in the data block is uncommitted, and the service process constructs the undo block based on the row data in the current block for consistent reading.

Summary of Update process:

To put it simply, if there is a transaction in the row of data, then you need to wait

If there is no transaction in progress for that row of data, then modify it normally.

Oracle two transactions can modify a data block at the same time, as long as the rows do not conflict. Concurrency of row-level locks

The answers to the questions about the Oracle transaction process are shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.