Related knowledge of the transaction

A user session is just a user's connection to the database, and the user's operations on the database are done through a single transaction in the session. Managing a transaction is not just a DBA's task, and each user who publishes a transaction can manage its own transactions.

Start of transaction:
Transactions in Oracle begin with the first executable SQL statement encountered.
DDL (Data Definition Language)
DML (Data manipulation Language)

End of transaction:
• User executes a commit or ROLLBACK statement
• The user executes a DDL statement, such as Create,drop,rename,alter. If the current transaction contains a DML statement that has already been executed, Oracle commits the transaction first and then executes and commits the DDL statement as a new transaction that contains only one statement.
• User active disconnect to Oracle.
• User process aborted unexpectedly.

Work done before committing a transaction:
• Generate a fallback entry for the transaction in the fallback cache of the SGA area. The original version of the data modified by the transaction is saved in the fallback entry.
• Generate a redo record of the transaction in the Redo log cache of the SGA area. The redo record records the changes made to the data block by the transaction, and also records the modifications made to the blocks of data in the fallback section. The redo record in the cache may be written to the hard disk before the transaction is committed.
• Records the modification of transactions to the database in the SGA area. These modifications may also be written to the hard disk before the transaction is committed.

Work done when committing a transaction:
• Record that the transaction has been committed in the internal transaction table in the fallback section specified for the transaction, and generate a unique SCN record in the internal transaction table to uniquely identify the transaction.
· The LGWR backward process writes the redo records in the SGA area redo log cache to the online redo log file. The SCN of the transaction is also written to the redo log while it is being written.
· All record locks and table locks used by the Oracle service process release transactions.
· Oracle notifies the user that the transaction submission is complete.
· Oracle marks the transaction as completed.

ROLLBACK TRANSACTION Completed work:
· Oracle uses the fallback entry in the fallback section to undo changes made to the database by all SQL statements in the transaction.
· All locks used by the Oracle service process release transaction
· Oracle notification transaction fallback succeeded.
· Oracle marks the transaction as completed

Isolation level of transaction:
(servers=dedicated)
Set TRANSACTION ISOLATION LEVEL Read Committed
Set TRANSACTION ISOLATION level serializable
Set TRANSACTION Read Only
Set TRANSACTION Read Write

Alter session Set Isolation_level=serializable
Alter session set Isolation_level=read commited

Read the consistency of the working mechanism:
Set the scn:10023 of the query statement
When the query statement searches for the desired block of data, the
Read if the desired block of data is scn<=10023.
If the desired block of data is scn>10023, the query statement obtains the original version of the corresponding block from the fallback segment (the original version of the SCN is also <=10023)
Thus, by comparing the SCN of the data block, the query statement returns only the data that was submitted before it was executed, guaranteeing that no dirty blocks of data have been read to the uncommitted transaction, and that the data blocks that were submitted before the statement was executed are not read.

To define a private fallback segment
Set TRANSACTION use rollback segment XXX