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

This article focuses on "how to understand Oracle affairs". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to understand Oracle affairs.

Oracle transaction

1 Overview

A transaction consists of one or more DML statements that start with the first DML statement and end with a DDL or DCL statement. You can use the SAVEPOINT command within a transaction to give the degree of control. Through the transaction mechanism to ensure that the actions done by this set of SQL statements are either executed successfully, completing the entire unit of work, or not at all

2 characteristics of transactions

3 transaction control

1 COMMIT command

Many people (even some experienced DBA) have an incomplete or complete misunderstanding of the oracle architecture when it comes to submission processing. The LGWR process flushes the contents of the log buffer to disk during all physical operations that occur during the COMMIT command. The DBWn process does nothing at all. This is a very important performance feature for oracle databases.

Note: when you execute the COMMIT command, the DBWn process does nothing.

In order to make a transaction persistent, all you need to do is write the changes that make up the transaction to disk (you don't have to have the actual table data on the data file on disk). If changes exist on disk in the form of multiple do log files, the transaction can be re-instantiated by restoring the data file from the backup made before the database damage and applying changes in the redo log file in the event of an event that damaged the database.

2 ROLLBACK command

During a transaction, Oracle saves an image of the data before the transaction. During the transaction, this image is provided to other sessions that query the data. If an error occurs, or if the session deliberately requests a rollback, it can also be used to automatically roll back the transaction.

The state of the data before the rollback is that the data has changed, but the information needed to reverse these changes is available. In order to meet the principle of isolation, this information is provided to all other sessions. The rollback restores the image before the data changes, thus discarding all changes; all rows inserted by the transaction are deleted, (leaving a question here) all rows deleted by the transaction are re-inserted into the table, and the updated rows return to the original state. Other conversations have no idea what's going on, and they will never see these changes. The session that handles the transaction now treats the data as data before the transaction starts.

3 SAVEPOINT command

Using a SavePoint allows the programmer to set a tag in the transaction that can be used to control the effect of the ROLLBACK command. In addition to rolling back the entire transaction and terminating it, you can reverse all changes made after a specific point while leaving the changes made before that point unchanged. The transaction itself continues: it is still not committed, it can still be rolled back, and it is still invisible to other sessions.

3.1 rules for using savepoint:

(1) all savepoint statements must contain a name. In the background, the savepoint name you create is associated with the system change number (system change number,scn). This is the object annotated by savepoint.

(2) the name of savepoint should not be repeated in a transaction, which is a series of SQL statements that end with a commit event. If you repeat a name, you will not see syntax errors or execution errors. Instead, the new savepoint overwrites the earlier savepoint, which in effect removes the previous save point.

(3) once a commit event occurs, either explicitly or implicitly, all existing storage points will be deleted from memory.

4 SELECT FOR UPDATE

The last transaction control statement is SELECT FOR UPDATE. By default, Oracle provides the highest level of concurrency: the reader does not interrupt the writer, and the writer does not interrupt the reader. Or simply put, there is no problem that one session queries the data that another session is updating, or that one session updates the data that another session is querying. However, sometimes this behavior needs to be changed to prevent changes to the data being queried.

It is not uncommon for an application to use the SELECT command to retrieve a set of lines, provide them to the user for intensive reading, and prompt the user for changes. Because ORACLE is a multi-user database, it is not impossible for another session to retrieve these rows. If both sessions are to be changed, there will be some strange effects.

Session1

Session2

Select * from emp

N pieces of data appear, including id=1 data

User deletes a piece of data from the emp table

Delete from emp where id=1

Update emp set name='test' where id=1

"0 rows updated" appears.

One way to solve this problem is to lock in the rows that the user is interested in:

Select * from emp for update

The FOR UPDATE clause locks all retrieved rows. No session other than the session that issued the command can change them, so the subsequent update operation will succeed. This means that a session has a consistent view of the data (it will not change), but the price is that if other sessions update locked rows, they will hang (they can query that day).

The lock set by the FOR UPDATE clause is maintained until the session issuing the command issues the COMMIT or ROLLBACK command. The lock must be released this way, even if the DML command is not executed.

5 the so-called "automatic submission"

Before concluding the discussion of submission processing, it is necessary to clarify the often mentioned "autocommit" (sometimes referred to as implicit commit). You often hear the saying that Oracle can be "auto-committed" in some cases.

Executing a DDL statement is one of the cases.

Exiting a user process (SQL*Plus) is another case.

"automatic submission" is purely non-existent. When a DDL statement is executed, the source code that implements the DDL command contains a fully regular COMMIT command. But what happens when you exit the user process?

Execute the exit command (normal)

Click directly on the upper right corner to close (exception)

Windows

Submit

Roll back

Linux

Submit

Roll back

If you use SQL*Plus on the WINDOWS terminal and execute a DML statement, followed by the exit command, the transaction is committed. This is because the exit command in SQL*Plus embeds a COMMIT statement.

However, click the upper-right corner of the SQL*Plus window at this time, close the SQL*Plus window, and if you log in to SQL*Plus again, you will find that the transaction has been rolled back. This is because the programmer who wrote SQL*Plus for Microsoft Windows embedded a ROLLBACK statement in the code that closes the SQL*Plus window.

SQL*Plusz may behave differently on other platforms, and the only way to confirm it is to test. Therefore, the ability to "autocommit" when exiting a program in different ways depends entirely on the programmer if he or she writes a user process. The Oracle server simply operates as instructed.

4 types of data submitted by oracle

1 explicit submission

A commit that is done directly with the COMMIT command is an explicit commit.

Its format is: SQL > COMMIT

2 implicit submission

The commit completed indirectly with the SQL command is an implicit submission.

These commands are: ALTER,AUDIT,COMMENT,CONNECT,CREATE,DISCONNECT,DROP,EXIT,GRANT,NOAUDIT,QUIT,REVOKE,RENAME. Oracle executes commit before executing these commands, and oracle automatically executes commit after command execution.

3 automatic submission

If AUTOCOMMIT is set to ON, the system will commit automatically after the insert, modify and delete statements are executed, which is called automatic commit. Its format is SQL > SET AUTOCOMMIT ON;, which is only an automatic commit after the execution of the DML statement, and has no effect on the normal or abnormal exit of the session.

This parameter belongs to the client sqlplus,oracle itself and does not have this parameter.

Look at the value of this parameter:

SHOW AUTOCOMMIT

Set the value of this parameter:

SET AUTO [COMMIT] {OFF | ON | IMM [EDIATE] | n}

5 ITL (transaction slot)

1 ITL description

ITL (Interested Transaction List) is an internal part of the Oracle data block and is located in the data block header (block header).

There is a transaction slot in the head of each oracle block. There is a concept of PCT_free in the oracle block, that is, the oracle reserves 10% of the block size as a buffer. When the transaction that modifies the oracle increases, the transaction slot grows downwards. When the data of the oracle block is updated, the data grows up, and the space of the PCT_free is compressed. When the PCT_free is used up, the oracle is completely full, and if there are still transactions to modify the Oracle block, you need to wait in the transaction queue for contention called transaction slots.

Itl consists of xid,uba,flag,lck and scn/fsc, which is used to record all transactions that occur in the block. An itl can be regarded as a transaction record. Of course, if the transaction has been committed, then the location of the itl can be used repeatedly, because the itl is similar to records, so it is sometimes called the itl slot. If a transaction has not been committed, then the transaction will always occupy an itl slot, and the itl records the transaction information, the entry of the rollback segment, the transaction type, and so on. If the transaction has been committed, then the itl slot also holds the SCN number when the transaction was committed.

Dump A data block can see ITL information in the transaction area.

Block header dump: 0x01001543

Object id on Block? Y

Seg/obj: 0x15c5a csc: 0x00.1ce6af itc: 3 flg: e typ: 1-DATA

Brn: 0 bdba: 0x1001540 ver: 0x01 opc: 0

Inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 Cmurmuri-0 scn 0x0000.001ce6af

0x02 0x0000.000.00000000 0x00000000.0000.00-0 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00-0 fsc 0x0000.00000000

Bdba: 0x01001543

Object id on Block? Y is a block that indicates whether it is an object or not

0x01001543 is the data file number, block number.

Seg/obj: 0x15c5a is the object id 89178, which is the hexadecimal form of the first six 64 characters AAAVxa of rowid. It can also be seen in the first four bits of dump (rowid,16). Refer to: http://blog.itpub.net/28539951/viewspace-1986647/

Csc: 0x00.1ce6af is The cleanout SCN that is used during read consistency

Itc: 3 is the number of itl slot in the block

Flg: E

E is using ASSM

O is using free list

Typ: 1-DATA 1 is the data, 2 is the index

Brn: 0

Bdba: 0x1001540 is Block relative data block address

Ver: 0x01

Opc: 0

Inc: 0

Exflg: 0

Itl is the list of related transactions on the block interested transaction list

The row-level lock in each record corresponds to the sequence number in the Itl list, that is, which transaction generates the lock on that record

Xid is a transaction id.Xid=Undo Segment Number XIDUSN+Transaction Table Slot Number XIDSLOT+ Wrap XID = usn#.slot#.warp#

Uba is the address of the rollback segment corresponding to the transaction. UBA = rollback block address (undo file number UBAFIL and block number UBABLK) + rollback sequence number UBASQN+ rollback record number UBAREC

Flag is the transaction flag bit

-the transaction is active or committed before the block is cleared

C = the transaction has been committed and row locks have been cleared

B = this undo record contains the undo for this ITL entry

U = the transaction has been committed (SCN is already the maximum), but the lock has not been cleared (quick cleanup)

T = transaction was still active at block cleanout SCN

Lck is the number of rows affected by this transaction

Scn/Fsc is scn or free space credit.

Related experiments can be referred to.

Https://blog.csdn.net/gumengkai/article/details/63684545

2 transaction slot parameters

Each block has a block header. There is a transaction table in the header of this block. Entries are created in the transaction table to describe which transactions lock which rows / elements on the block. The initial size of this transaction table is specified by the object's INITRANS setting. For tables, this value defaults to 2 (the INITRANS of the index also defaults to 2). The transaction table dynamically expands as needed, up to a maximum of MAXTRANS entries (assuming there is enough free space on the block). Each allocated transaction entry takes up 23 million 24 bytes of storage space in the header of the block. Note that for Oracle 10g maxtrans, the MAXTRANS of all segments is 255.

If the value of initrans is set to 2, the database server has at most two parallel transactions in a data block that can independently and parallelly perform transaction operations on the row data in the shared data block through its own transaction slot.

That is to say, if something locks the data of this block, the identification of the transaction will be recorded in this place. Of course, that transaction needs to see if the place is already occupied, and if so, check to see if the transaction is active. If there is no activity, such as a commit or rollback, you can overwrite this place. If active, you need to wait (the role of the latch). Therefore, if there are a large number of concurrent accesses using this block, the parameters should not be too small, otherwise resource competition will lead to a decline in system concurrency performance.

Block allocation and ITL Management when ORACLE concurrent insert transactions

When INITRANS = 1, multiple INSERT transactions (up to 5 in this test) do not wait for group congestion because of ITL contention. The strategy adopted by ORACLE is that each INSERT transaction is allocated different blocks to use, so that there is no conflict between sessions, unless there are no extra blocks in the segment.

Create a table and view the parameters of the table

Create table T1 (id int,num int)

Select TABLE_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables where table_name='T1'

Insert data

Insert into T1 values (1Pol 1)

Insert into T1 values (2jue 1)

Insert into T1 values (3pence 1)

Insert into T1 values (4jue 1)

Commit

View the distribution of data in the table

Select ID,num, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from T1

Session 1

Insert data view and find block 532 inserted into file No. 4

Insert into T1 values (5pm 1)

Select ID,num, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from T1

Session 2

Insert data view and find block 533 inserted into file No. 4

Insert into T1 values (6jue 1)

Select ID,num, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from T1

Session 2

Insert data view and find block 535 inserted into file No. 4

Insert into T1 values (9. 9)

Select ID,num, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from T1

To sum up, the INSERT transaction allocates different blocks to use, so that there is no conflict between sessions, unless there are no extra blocks in the segment

Block allocation and ITL Management when ORACLE concurrent update transactions

When INITRANS = 1, multiple UPDATE transactions (up to 7 in this test) will not cause waiting due to ITL contention. In this case, in addition to using the default ITL, the ITL required for dynamic expansion will wait only in very extreme cases.

1) there is no FREE space for the BLOCK. Please note that the setting of the FREE parameter cannot be too small.

2) the total number of ITL used by the block exceeds the maximum ITL allowed by the block min (round (block_size*0.5/24)-2255).

It is difficult to achieve such an extreme situation and the actual production situation should be less likely to occur than the deadlock of the business SQL.

3 ITL wait

The scenario in which the wait occurs:

1. Exceeds the maximum number of ITL configured by maxtrans

Insufficient 2.initrans, not enough free space to extend ITL

Solution:

Insufficient maxtrans:

This situation is caused by high concurrency: the number of transactions on the same data block has exceeded the number of ITL actually allowed. Therefore, to solve this kind of problems, we need to start from the application, reduce the concurrency of transactions; long transactions, on the premise of ensuring data integrity, increase the frequency of commit, modify to short transactions, and reduce resource occupation events. For OLAP systems (for example, there is a data entry module with high concurrency), you can consider increasing the block size.

Insufficient initrans:

The number of ITL on the data block does not reach the MAX TRANS limit, and tables that occur are usually UPDATE frequently, causing the reserved space (PCTFREE) to be filled. If we find that this kind of ITL wait has affected the system, we can increase the INITRANS or PCTFREE of the table (depending on the amount of concurrent transactions on the table, generally, if the concurrency is high, it is recommended to increase the INITRANS first, otherwise, the increase of PCTFREE is preferred).

It is important to note that if you use ALTER TABLE to modify these two parameters, it will only affect the new data block, not the existing data block-- to do this, you need to export / import the data and rebuild the table.

Concepts related to Oracle transaction

1 transaction id

Everything has its own ID, just like an ID number.

In the v$transaction data dictionary, xid is a thing. ID,xid is both a number and an address. The contents of xid are

1. The head block of which rollback segment to use; XIDUSN

2. A undo segment can have at most 47 active things at the same time, a undo segment has only one thing table, the situation of 47 things is all in the thing table, one thing occupies a row, which row of 47 things is used this time; XIDSLOT

3. The number of times the row has been overwritten. XIDSQN

Select xid,xidusn,xidslot,xidsqn,start_time,start_scnb,used_ublk,used_urec,log_io,phy_io from v$transaction

Note: after a period of time, if the used_urec field continues to increase, it means that the thing is continuing, and if the field continues to drop, it means that the thing is rolling back.

Check it again with v$session, so you can know which user's which transactions and get the sid.

Select b.siddireb.usernamerexidrea.statusreachustimecompensiontimereachusustimecompany usedaccounublkreuslegureclegleglegallyphyonomio from v$transaction a minute vandalism session b where a.ses_addr = b.saddr.

Get sid so that you can know which transactions of which user, and then you can trace to sql.

Select sql_text from v$sqlarea a minute vain session b where a.SQL_ID=b.PREV_SQL_ID and b.SID=&sid

The meaning of each column field of V$TRANSACTION

Column name data type meaning ADDR RAW (4 | 8) the rollback segment number used by Address of the transaction state object XIDUSN NUMBER, which can correspond to the slot number USN.SLOT.SQN XIDSQN NUMBER serial number in XIDSLOT NUMBER RBS TX table with v$rollstat. Number of times slot was reused TX-USNxSLOT-SQNxxxxx UBAFIL NUMBER last undo block address file number UBABLK NUMBER UBA block number UBASQN NUMBER UBA sequence number UBAREC NUMBER UBA record number STATUS VARCHAR2 (16) status of current transaction START_TIME VARCHAR2 (20) Start time (wall clock) START_SCNB NUMBER system change number START_SCNW NUMBER Start SCN wrap START_UEXT NUMBER Start extent number START_UBAFIL NUMBER Start UBA file number START_UBABLK NUMBER Start UBA block number START_UBASQN NUMBER Start UBA sequence number START_UBAREC NUMBER Start UBA record number SES_ADDR RAW (4 | 8) user session object address For the saddr column of v$session FLAG NUMBER Flag SPACE VARCHAR2 (3) YES if a space transaction RECURSIVE VARCHAR2 (3) YES if a recursive transaction NOUNDO VARCHAR2 (3) YES if a no undo transaction PTX VARCHAR2 (3) YES if parallel transaction NAME VARCHAR2 (256) Name of a named transaction PRV_XIDUSN NUMBER Previous transaction undo segment number PRV_XIDSLT NUMBER Previous transaction slot number PRV_XIDSQN NUMBER Previous transaction sequence number PTX_XIDUSN NUMBER Rollback segment number of the parent XID PTX_XIDSLT NUMBER Slot number of the parent XID PTX_XIDSQN NUMBER Sequence number of the parent XID DSCN-B NUMBER This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_BASE. DSCN-W NUMBER This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_WRAP. Number of undo blocks occupied by USED_UBLK NUMBER number of undo records used by USED_UREC NUMBER logical LOG_IO NUMBER O PHY_IO NUMBER physical I do O CR_GET NUMBER number of consistent reads CR_CHANGE NUMBER Consistent changes START_DATE DATE Start time (wall clock) DSCN_BASE NUMBER Dependent SCN base DSCN_WRAP NUMBER Dependent SCN wrap START_SCN NUMBER Start SCN DEPENDENT_SCN NUMBER Dependent SCN XID RAW (8) Transaction XID PRV_XID RAW (8) Previous transaction XID PTX_XID RAW (8) Parent transaction XID

The usn/slot/sqn can be obtained through xid as follows:

With v as (select '020001006805000' xid from dual)

Select to_number (substr (v.xidjue 3pr 2) | | substr (v.xidjue 1pr 2), 'xxxx') usn

To_number (substr (v.xidrecover7) 2) | | substr (v.xidMagne5) 2), 'xxxx') slot

To_number (substr (v.xidrecover15recover6) | | substr (v.xidrecovery13recover2) | | substr (v.xidrecover11) | | substr (v.xidrecover9), 'xxxxxxxxxx') sqn

From v

2 transaction table

The table contains 47 rows in the first data block of the undo segment of the undo table space (that is, the segment header block of the undo segment). The first thing to do at the beginning of things is to find a blank line in the list of things and write down the information about things. In other words, there are up to 47 active things in the undo segment, but oracle will try to put one thing on one segment for uniform distribution (the location of the segment header block of the undo segment can be found in the dba_segments tablespace).

Transaction management starts with and centers on the undo segment. The first block (segment header block) of the undo segment contains the following structures: extension mapping, extension control header (like other types of segment header blocks), transaction table, transaction control area (special structure). The approximate structure of the transaction table is as follows:

A block of a undo segment of dump can see the transaction table.

Index represents the slot number in the transaction table, which is just a sequence. The 11g version has 34 slots from the beginning of 0x00 to the end of 0x21.

State indicates the transaction status: 9 indicates that the transaction is inactive and 10 indicates that the transaction is active, from which we can see that the transaction on slot 0x17 in hexadecimal is active. Have you noticed that before we have a transaction, Oracle will look for chd=0x0017 in the transaction control list, to put it bluntly, from the slot of index=0x17 to store the latest transactions:

Note: the following transaction control is the transaction control that I DUMP before the transaction occurs (that is, before doing update gyj_test set name='GGGGG' where id=1;)

TRN CTL:: seq: 0x000d chd: 0x0017 ctl: 0x000b inc: 0x00000000 nfb:0x0001

Mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)

Uba: 0x0280000a.000d.2b scn: 0x0000.0028a26a

Cflags indicates the status of the transaction in use: 0x00 for inactive transaction, 0x80 for active transaction, 0x10 for dead transaction, 0x90 for rolled back dead transaction

What we usually see most is 0x00 for inactive transactions, 0x80 for active transactions, and the latter rarely occurs.

Wrap# represents the number of times the transaction slot on the transaction table is reused, which is part of XID. 0x001d indicates that the transaction slot was reused 29 times at this time.

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

Scn represents the SCN that the business starts, commits, and rollback.

Dba represents the uba: the undo block address of the first part, and this DBA is the starting point of the (rollback) rollback, that is, the address of the UNDO block where the last record of the transaction modification was recorded.

Nub indicates the number of UNDO blocks used by the current transaction.

Cmt says it is closest to the current submission timestamp, which began at midnight on January 1, 1970 (recorded in seconds). 0 indicates that the transaction is active.

What are the undo segments in oracle:

Select * from v$rollname

3 things slot

There is something in the head part of each data block, including xid, uba (undo block address), and so on.

When things happen,

The first thing is to find a slot in the header block of the relatively idle undo segment in the undo tablespace, write the thing information (xid), assign a undo block to the thing (what is written in the undo block is the data before modification), and then write the address of the undo block (that is, uba to the thing table), so now there are xid and uba in the thing list.

The second thing is to find the slot in the transaction slot in the head of the data block to be modified, write the thing information (xid), (the purpose is to find the thing table through the xid on the data block), and then modify the data on the data block to be modified, and the information before modification is written into the undo block. At the same time, write the uba address in the transaction slot and point to the rollback block. Why write information about things in two places? Here's an explanation.

When the data of the rollback block is full, the system automatically allocates a rollback block. For example, the thing modifies more data, resulting in 3 undo blocks, and 3 undo blocks are linked in sequence, but the uba in the event table only points to the latest undo block, which is convenient for rollback.

The uba in the transaction slot of the data block points to the rollback data, which facilitates the construction of CR blocks.

A thing slot, only when the thing is submitted, the slot can be covered.

Pctfree:1, when performing an update operation, may take up pctfree space. 2, when multiple things operate the data block, you need to increase the number of thing slots, which will also take up pctfree, but if there are too many things, pctfree is not enough (the previous thing slot cannot be overwritten, and new things are generated in this data block, and new thing slots are needed). This will lead to contention of thing slots, most of which occur in the case of update and delete, and insert will not occur. Because oracle will try its best to insert the data inserted by insert into multiple blocks, that is, averaging it, but update and delete are powerless, because these two operations are often performed on a row, and a particular row is in this block.

At this point, I believe you have a deeper understanding of "how to understand Oracle transactions". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.