Oracle transaction principle Inquiry 1 -- oracle Core Technology Reading Note 5, oracle1 -- oracle

Oracle transaction principle Inquiry 1 -- oracle Core Technology Reading Note 5, oracle1 -- oracle

1. Conflict Resolution

Suppose there is a system that only has two users: You and me, and we are constantly modifying and querying a small part of the data in the system.

If you are performing a batch of modification operations in the database and I am querying, I cannot see the modifications you have made, until you tell me that you can see all the changes you have made (you have committed the transaction ). Therefore, in oracle, there must be an efficient way to identify which data I can see and which data I cannot see.

From the opposite perspective, 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 others that all the modified data is visible ). In more extreme cases, you may need to decide to roll back the transaction. In this case, you also need an efficient mechanism to associate all the undo records and sort them in the generated order, these changes can be rolled back in reverse order.

2. Transactions and undo

When creating a database, you must create an undo tablespace. At the same time, oracle will automatically create multiple undo segments in the undo tablespace, and automatically add, expand, and contract as the database load changes.

Transaction Management starts from the undo segment and is centered on this. The first block (segment header block) of the undo segment contains the following structure: Extended ing, extended control header (same as other types of segment header blocks), transaction table, and transaction control area (special structure ). 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 10 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 indicates the slot number in the transaction table. It is just a sequence. From 0x00 to 0x21, the 11g version has 34 slots.

State indicates the transaction state: 9 indicates that the transaction is not active, and 10 indicates that the transaction is active. From here, we can see that the transaction on the hex 0x02 slot is active.

Cflags indicates the status of the transaction that is using the transaction slot: 0x00 indicates that the transaction is not active, 0x80 indicates that the transaction is active, 0x10 indicates that the transaction is dead, and 0x90 indicates that the transaction is rolled back.

Wrap # indicates the number of times the transaction slot in the transaction table is reused. It is part of the XID. 0x2013 indicates that the transaction slot is reused for 8211 times.

Uel indicates the pointer of the current active transaction to the next transaction slot in the transaction slot (that is, if another transaction occurs, the index on the transaction slot pointed by UEL will be used ).

Scn indicates the SCN used to start, submit, and roll back a transaction.

Dba indicates uba: The undo block address in the first part. This DBA is the starting point of rollback, that is, the address of the UNDO block where the last record of transaction modification is located. This allows oracle to find the last undo record generated by the transaction during crash recovery to know where to start processing rollback.

Nub indicates the number of UNDO blocks used by the current firm. When the transaction is rolled back, we can see that this value will gradually decrease.

Cmt indicates the closest to the current submission timestamp, starting from, January 1, January 1, 1970 (recorded in seconds ). 0 indicates that the transaction is active.

2.1 start and end of a transaction

When a session starts a transaction, it first obtains an undo segment, obtains a record from the transaction table of the undo segment, and then adds the value of wrap # Of the record, change the state to active (10), and modify other columns in the transaction table (for example, set cmt to 0 ). Because this is also a modification to the database block, a redo Change vector (operation code 5.2) that is finally written to the redo log file will be generated and written to the database. In this way, the session has an active transaction.

Similarly, when the transaction is completed (usually the user commit), the state is set back to free (9) and other columns are updated, such as writing the current SCN into the scn column. Similarly, this modification to the database block also generates a redo Change vector (operation code 5.4), which is finally recorded in the redo log. This moment is very important, because at this moment your session is writing the current content of the log buffer to the disk by issuing a command to the log write process (lgwr), and waiting for the log write process to confirm that the write is complete, to protect submitted changes. (This sending Command requires the lgwr process to output the redo log to the disk, which is actually very simple. Once the redo log operation code that enters the log buffer is found to be 5.4, that is, commit transaction records, immediately refresh the logs in all the buffers to the output disk, and then notify the session that puts this commit record into the log buffer .)

A transaction id is assigned to each transaction. The transaction id consists of the undo segment number, the entry index number in the transaction table, and the latest wrap # value in the transaction entry. So when you see something like 0X0009. when the transaction id is 002.00002013, you will know that in the undo segment 9, this transaction uses 2nd transaction table records, 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 condition to query the dba_rollback_segs view.

2.2 transaction table

We have already introduced the structure of the transaction table. Now let's look back at it. The main objective of the transaction table is nothing more than the following:

1. Check whether the transaction is committed or still active.

2. SCN of the committed transaction

3. Location Information of the latest undo record generated by the transaction for rollback

4. undo volume generated by the transaction

If a transaction must be rolled back, or a session is forcibly killed, make the smon (system monitoring process) have to roll back its transaction, or if the instance crashes, during instance recovery, smon must roll back all active transactions during crash. At this time, you can easily find all the active transactions (with the status equal to 10), and find the last undo block (dba column) being used by each transaction ). You can then search back Based on the undo block linked list corresponding to each transaction and apply each undo record, because each undo record points to the previous undo record in the transaction.

2.3 brief introduction to undo Blocks

The undo block has many similarities with the normal data block: The Block header records a number of control information and metadata, and the row directory lists the positions of each item stacked in the block, several undo records are stacked up and down in the block, and the free space is located in the middle of the block. However, the biggest difference between the table row and the undo record is that the undo record is not modified, so once the undo record is written into the block, it will always be kept in the same position. (After the table row is modified, if the original position does not fit, it will be copied to another new position, so that the block will leave a messy pointer and a temporary empty space ).

An unfamiliar fact is that a single undo block can also contain undo records of multiple transactions. A transaction will request the ownership of the undo block in an exclusive way, pin, use the block until the block is full (the transaction requests the new undo block and updates its transaction table slot to point to the new block) or commit the transaction. If there is still free space in the undo block after the transaction is committed, the block will be added to the candidate linked list of the idle block pool in the undo segment header. In this case, the undo block will be used by other transactions for its remaining space.