Oracle 11g R2 Control of Things

Control transactions

Bank transfer

John Doe to Zhang San remittance

ACID

A: atomicity: All the steps in the whole transaction are indivisible, the atomic principle stipulates that each step of a transaction must be completed, or the entire transaction will not be completed. That is, to ensure that all operations in a transaction can be completed or not completed, and that if any errors occur before the transaction completes, the database itself must be guaranteed to automatically roll back any part of the transaction (and must be automated)

C: Consistency: The data is always in a consistent state, both before and after the transaction. For example, John Doe to Zhang San 10000, then you need to subtract 10000 from the John Doe account and add 10000 to the Zhang San account. Oracle uses revocation segments to ensure data consistency.

I: Isolation: The principle of isolation requires that unfinished transactions must not be visible. During a transaction, only the session that executes the transaction can see the changes, and all other sessions see data that is unchanged (not the new value after the change). The logical implication of this provision is that, first, because the entire transaction may not be fully completed, other users are not allowed to see changes that might be rolled back, and secondly, during a transaction, the data is incoherent, John Doe's account is lost by 10000, but Zhang San's account has not been increased by 10000. The isolation of transactions requires that the database must hide the transactions that are in progress against other users, who can only see data that has not been updated, and that they will see all changes only when the transaction is complete. Oracle uses revocation segments to ensure transaction isolation.

Create a lab environment: Create an AA table, insert 3 records, and commit things.

View the records of the AA table, insert a record, and automatically start a thing when we execute a DML statement

At this point the Insert record transaction is not committed, there is no real completion of the transaction without committing, there is also a chance of rollback.

Scott users can see the inserted record when they view the AA table in the current session

Open a new Sqlplus session and look at the AA table and there are no newly inserted records: This is the isolation of things

Commit a thing in the first sqlplus session

The fourth record inserted in the second session cannot be seen after 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 a transaction, the principle of isolation requires that no user other than the user involved in the specified session be able to view the current changes. However, once the transaction is complete, all users must be able to see the changes immediately, and the database must ensure that these changes are never lost. Oracle uses log files to meet this requirement. The log file has two

Type: Online redo log file, archive redo log file.

A properly configured Oracle database is not likely to lose data. Of course, user errors (including inappropriate DML or deleted objects) can also cause loss of data

DDL statements have autocommit capabilities (create, DROP, truncate, alter)

Create a table AA, insert a record

Fallback things

Insert a record in a table

Normal exit in Sqlplus

Looking at the records in the AA table in another sqlplus, you will find a record of the newly inserted tom1. If you change the data using the Sqlplus tool, Oracle automatically submits things when you exit Sqlplus normally.

There are currently only TOM1 records in AA tables

Emulation instance Restart

Using the Scott user connection to view the contents of the AA table, it was found that the record of the insert Tom2 was automatically rolled back because the instance was restarted.

Using autocommit to implement automatic submission of things

Even if you perform a rollback, the query results still contain the newly inserted data, and you can use set Autocommit off when you turn off autocommit

A summary of things:

1. Note that Commit: just to confirm that the data has been formally modified, not necessarily to write to the hard disk, dbwn do nothing. The LGWR process writes the contents of the log buffer to disk when all physical operations occur while executing the commit command. The DBWN process is not performing any action at all. The DBWN process has nothing to do with committing things, but eventually the DBWN process writes the changed chunks to disk.

2. The commit and rollback statements should only be in DML statements, and we cannot roll back the DDL statements. Once the DDL statement is executed, it will immediately have a persistent state.

3. Autocommit and Implicit commit: Oracle can be autocommit in some cases: executing a DDL statement is a case where exiting a user process is also an automatic commit.

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

Oracle 11g R2 Control of Things