Learn more about Oracle transactions

The importance of Oracle transactions for Oracle databases is self-evident. The following describes the knowledge of Oracle transactions for your reference.

1. Oracle transaction isolation

① Changes made by an uncommitted firm cannot be viewed by other firms. Only when the transaction is committed successfully can other transactions executed after the transaction be able to see the changes made by the transaction.
② A part of the transaction has been executed before the transaction is committed, and the remaining part of the transaction after the transaction is committed cannot see the changes made by the transaction. This is another Oracle feature: read consistency and snapshot.

③ The changes made by the transaction cannot be seen in other transactions committed at the same time.

2. Statement-level rollback

① When an SQL statement is interrupted due to any errors during execution, a "Statement-level rollback" is generated ". The rollback result is the same as that of the SQL statement.

② Note that rollback occurs in the "execution" phase. If it is in the SQL statement parsing phase, it cannot be executed due to syntax issues. Because there is no impact, there will be no "rollback ".

3. Division of Oracle transaction control there is no obvious transaction start in oracle, And the ending statement block will automatically form a transaction block. )

If a transaction contains DML statements and DDL statements, the transaction is divided into multiple transactions. First, all DML statements before DDL are committed as a transaction at a time. Then Oracle will create a new, single-statement transaction for this DDL. If the DDL statement execution fails, the DML transaction is still successful. Each DDL statement is a single transaction, that is, the DDL statement cannot be rolled back.

4. Oracle transaction control in different situations

Note: The consequences of user disconnection from the Oracle server and user process forced interruption are different.

① A typical example of the former is that the Oracle server is shut down or restarted. At this time, all uncommitted transactions will be committed.

② In the latter typical example, the user uses the Process Manager to forcibly kill the process. At this time, all operations not submitted by the firm are rolled back.

5. What to do before the transaction is committed

Before submitting a transaction, Oracle must do the following:

① Remember what the data looks like before it is modified

② Remember what data will be changed soon

③ Remember the association between redo log and undo log

④ Modify the data read to memory in SGA to a new value (which is not actually saved to the data file)

The first step is to record the undo log in the undo tablespace. Step 2 and Step 3 are completed by writing records to the redo log buffer in SGA. In this way, when the transaction is rolled back, the corresponding undo log can be found from the redo log to retrieve the previous data.

★Note that the DBWn process may not be triggered at this time.

★Note that the change in transaction operations recorded in step 1 is performed in the memory and has not been written to the redo log file on the disk.

6. What is done when the transaction is committed

① Record SCN value (System Change Number)

② Store online redo log records (in the SGA redo log buffer) to the redo log file

③ Oracle releases resources and locks

④ Oracle marks the transaction as ended

Note: Only after the commit command is sent Will the content of the redo log file in the SGA re-executed log buffer be refreshed to the disk. Before the LGWR process is executed, the redo log is stored in the memory, also known as the online redo log.

7. savepoint rollback and the rollback of the entire transaction

Note that when Oracle transactions roll back to a savepoint, all subsequent savepoints after the savepoint will become invalid. However, the Transaction is still active and can be continued. This is different from the rollback of the entire transaction.

8. Autonomous transactions

Traditionally, a transaction can only start the next transaction after the complete execution is successful or rolled back. Autonomous Transactions allow you to call and run another transaction in one transaction. After the called transaction is completed, the called transaction continues to run the unfinished operations until the transaction ends.

After an autonomous transaction is called, it is completely independent from the peripheral transaction. No resources or locks are shared with each other. uncommitted changes of the peripheral firm are invisible to autonomous transactions. After the autonomous transaction is committed, the peripheral transaction will be able to see the change.

Delete unused services in oracle service items

Three methods for disabling Oracle databases

How to manage Oracle services in Windows

Implementation of Oracle redo log

How to modify the size of Oracle archive logs