Research on the principle of Oracle Transaction 1--oracle Core Technology reading notes five

1. Conflict resolution

If there is a system that only you and me two users, and we are constantly in the system of a small number of data modification and query operations.

If you're doing a batch of modifications in the database, and I'm doing a query, I can't see the changes you've made until you tell me that you can see all the changes you've made (you committed the transaction). So within Oracle, there must be an efficient way to identify what data I can see and what data I can't see.

From the opposite point of view, when you commit a transaction, you need an efficient mechanism for everyone else to see that the transaction has been committed (that is, to tell someone that all of your modified data is visible). More extreme, you may need to decide to roll back the transaction, so you also need an efficient mechanism to correlate all undo records in the order in which they were generated, so that you can roll back those changes in reverse order.

2. Transactions and Undo

When creating a database, you must create an undo table space. At the same time, Oracle automatically creates multiple undo segments in the undo tablespace and automatically adds, expands, and shrinks as the database load changes.

Transaction management starts with the undo segment and is centered on this. The first block (segment header block) of the undo segment contains the following structure: Extended mappings, extended control headers (as with other types of segment header blocks), transaction tables, transaction control areas (special structures). The approximate structure of the transaction table is as follows:

TRN TBL:

Index state cflags wrap# uel SCN dba NUB Cmt

0x00 9 0x00 0x2013 0x001b 0x0000.016f1fc1 0x0180083e 0X00000001 1302762364

0x01 9 0x00 0x2014 0X001a 0x0000.016f1f54 0x0180083e 0X00000001 1302762364

0X02 0X80 0x2013 0x001d 0x0000.016f20fc 0x0180083e 0X00000001 0

0X03 9 0x00 0x200c 0x001c 0x0000.016f20d8 0x0180083e 0X00000001 1302762364

0x04 9 0x00 0x200f 0x001f 0x0000.016f1c75 0x0180083f 0X00000001 1302762364

.........

INdex represents the slot number in the transaction table, just a sequence, starting with 0x00 to the end of 0x21, the version of 11g has 34 slots.

State indicates the transaction status: 9 means the transaction is inactive, 10 is active on behalf of the transaction, and from here we see that the transaction on the 16-number slot 0x02 is active.

Cflags represents the state of a transaction that is using a transaction slot: 0x00 represents an inactive transaction, 0x80 represents an active transaction, 0x10 represents a dead transaction, 0X90 represents a dead transaction that is rolled back

wrap# represents the number of times a transaction slot on a transaction table has been reused, which is part of the XID. The 0x2013 indicates that the transaction slot was reused 8,211 times at this time.

UEL represents the pointer to the next transaction slot in the transaction slot of the current active firm (that is, if a new transaction occurs, the index on the transaction slot pointed to by Uel is used).

The SCN represents the SCN that initiates, submits, and rolls back the service.

The DBA represents Uba: the undo block address of the first part, which is the starting point for (rollback) rollback, that is, the address of the undo block where the last record of the transaction modification was recorded. This enables Oracle to find the last undo record generated by the transaction when the crash is resumed, so that it knows where to start processing the rollback.

Nub represents the number of undo blocks used by the current transaction. When a transaction is rolled back, you can see that the value is progressively reduced.

The CMT represents the closest commit timestamp, which is recorded in seconds, starting 0 morning, January 1, 1970. 0 indicates that the transaction is active.

2.1 Start and end of a transaction

When the session starts a transaction, it takes an undo segment, gets a record from the transaction table in the undo segment, increases the value of the record's wrap#, changes the state to active (10), and modifies other columns of the transaction table (such as CMT 0). Since this is also a modification to the database block, a redo vector (opcode 5.2) is generated for the final write redo log file and written to the database. In this way, the conversation has an active transaction.

Similarly, when a transaction is completed (usually a user commit), the state is set back to free (9), and some other columns are updated, such as the SCN for the current period is written to the SCN column. Similarly, this modification of the database block also generates a redo vector (opcode 5.4), which is eventually recorded in the redo log. This moment is quite special because your session is writing to the log write process (LGWR) Release command to write the current contents of the log buffer to disk and wait for the log write process to confirm that the write is complete to protect the committed changes. (This Send command requires the LGWR process output redo log to disk is actually very simple, once found into the log buffer of the redo log opcode is 5.4, that is, commit transaction records, immediately the log of all buffers to flush the output disk, completed and told to put this commit record into the log buffer session.) ）

Each transaction is assigned a transaction ID, and the transaction ID consists of the undo segment number, the entry index number in the transaction table, and the most recent wrap# value in the transaction entry. So, when you see a transaction id like 0x0009.002.00002013, you know: This transaction is in Undo Section 9, with the 2nd Transaction table record, and the wrap# value is 0x2013. If you want to see which undo segment this is, and the corresponding segment header position, you can use the SEGMENT_ID column as the query criteria to query the Dba_rollback_segs view.

2.2 Transaction Tables

In the above we have introduced the structure of the transaction table, and now look back, the main purpose of the transaction table is nothing more than the following:

1. Show whether the transaction is committed or still active

2. SCN for committed transactions

3. Location information for the most recent undo record generated by a transaction, for easy rollback

4. Undo amount generated by transaction

If a transaction must be rolled back, or if a session is forcibly killed, so that Smon (the system monitoring process) must roll back its transaction, or if the instance crashes, during instance recovery, Smon must roll back all active transactions at the time of the crash. At this point it is easy to find all active transactions (state equals 10) and find the last undo block (dba column) that each transaction is using. Each undo record can then be searched back based on the Undo block list for each transaction, since each undo record points to the previous undo record in this transaction.

2.3 Undo Block Brief Introduction

Undo blocks have many similarities to ordinary blocks of data: The volume section records a number of control information and metadata, and the row directory lists the locations where each item in the block is stacked, and several undo records are stacked from the bottom up in the block, and the block free space is in the middle of the block. However, the biggest difference between table rows and undo records is that the undo record is not modified, so once the undo record is written to the block, it remains in the same position forever. (After the table row has been modified, if the original position does not fit, it will be copied to another new location, so that the block will leave a messy pointer and a temporary void).

A less well-known fact is that a single undo block can also contain undo records for multiple transactions. A transaction requests the ownership of the undo block exclusively, pins it, and then uses the block until the block is full (at which point the transaction requests a new undo block and updates its transaction table slot to point to the new block) or the transaction commits. If there is still free space in the undo block after the transaction commits, the block is added to the candidate list of the free block pool in the Undo segment header. If this happens, the undo block will be used by other transactions for its remaining space.

Research on the principle of Oracle Transaction 1--oracle Core Technology reading notes five