Shulou(Shulou.com)06/01 Report--

Control transaction

Bank transfer

Li Si sent remittance to Zhang San.

ACID

A: atomicity: all the steps in the whole transaction are inseparable, and the atomicity principle stipulates that each step of a transaction must be completed, otherwise the whole transaction will not be completed. That is, to ensure that all operations in a transaction can or cannot be completed, if any errors occur before the transaction is completed, then the database itself must ensure that any part of the transaction that has been done is automatically rolled back (and must be done automatically).

C: consistency: whether before, during or after a transaction, the data is always in a consistent state. For example, if Li Si remits 10000 to Zhang San, then he needs to subtract 10000 from his account and add 10000 to his account. Oracle uses undo segments to ensure data consistency.

I: isolation: the principle of isolation states that unfinished transactions must be invisible. During a transaction, only the session executing the transaction can see the change, while all other sessions see the same data (not the new value after the change). The logical meaning of this rule is: first, since the whole transaction may not be completed, other users are not allowed to see changes that may be rolled back; second, during a transaction, the data is incoherent. Li Si's account has lost 10000, but Zhang San's account has not increased by 10000. The isolation of transactions requires that the database must hide ongoing transactions from other users who can only see data that has not been updated and can see all changes only when the transaction is completed. Oracle uses undo segments to ensure transaction isolation.

Create an exercise environment: create an aa table, insert 3 records, and submit things.

Look at the record of the aa table, insert a record, and when we execute a DML statement, we start a thing automatically

At this point, the transaction recorded by insert is not committed, and if the transaction is not committed, it is not really completed, and there is still an opportunity for rollback.

Scott users can see the inserted records when they view the aa table in the current session

Open a new sqlplus session and look at the aa table and you will find that there are no newly inserted records: this is the isolation of things.

Submit things in the first sqlplus session

The fourth record inserted in the second session can only be seen after the submission.

D: persistence: once you use the commit command to end a transaction, you must ensure that the database does not lose the transaction. During the course of a transaction, the principle of isolation requires that no user other than the user involved in the specified session can view the current changes. However, once the transaction is completed, all users must be able to see the changes immediately, and the database must ensure that the changes are never lost. Oracle meets this requirement by using log files. Log files have two

Various forms: online redo log files, archived redo log files.

It is impossible for a properly configured oracle database to lose data. Of course, user errors (including inappropriate DML or deletion of objects) will also result in data loss.

DDL statements have auto-commit functions (create, drop, truncate, alter)

Create table AA and insert a record

Fall back on things

Insert a record into the table

Exit normally in sqlplus

If you look at the records in the aa table in another sqlplus, you will find the records of the newly inserted tom1. If you use the sqlplus tool to change the data, oracle will automatically submit things when you normally exit sqlplus.

Currently, only tom1 is recorded in the aa table.

Simulation instance restart

Using the scott user connection, look at the contents of the aa table and find that the record of the insert tom2 is automatically rolled back due to the instance restart.

Using autocommit to realize the automatic submission of things

Even if the rollback is performed, the query result still contains the newly inserted data, and set autocommit off can be used when autocommit is turned off

A summary of things:

It's important to note that Commit: just to confirm that this data has been formally modified, it doesn't have to be written to the hard drive, and DBWn doesn't do anything. The LGWR process writes 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. The DBWN process has nothing to do with commit transaction processing, but eventually the DBWN process writes the changed blocks to disk.

Commit and rollback statements should only be in DML statements, and we cannot roll back DDL statements. The DDL statement has a persistent state as soon as it is executed.

3. Auto-commit and implicit commit: oracle can auto-commit in some cases: executing a DDL statement is a case, and exiting a user process is also an auto-commit.

If you are interested, please scan the QR code below for more details for free

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.