Oracle transaction Feature Management read details

Characteristics of Oracle Transactions
ACID, an abbreviation for the four basic elements that a database transaction performs correctly. Contains: atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability). A support transaction (Transaction) database system, must have these four characteristics, otherwise in the transaction process (Transaction processing) can not guarantee the correctness of the data, the transaction process may not be able to achieve the requirements of the parties.
Atomic Sex (atomicity)

All operations in the entire transaction, either complete or incomplete, cannot be stalled in the middle of the process. An error occurs during the execution of a transaction and is rolled back (Rollback) to the state before the transaction began, as if the transaction had never been performed.

Consistency (consistency)

Database integrity constraints are not compromised until the transaction is started and after the transaction ends.

Isolation (Isolation)

The execution of two transactions is mutually exclusive, and a transaction cannot see the data at a time when other transactions run.

Persistence (Durability)

After the transaction is completed, the changes made by the firm to the database are persisted in the database and are not rolled back.

There are typically three types of reads in Oracle:

The first: misreading (uncommitted reading), reading something that has been modified but not submitted, is not read in real consistency.

The second: non-repetitive reading (read distortion) is two times before and after a thing read, the value is not equal, because it is at the interval of the two reading is modified or deleted by other things, and submitted.

The third reading: fantasy read a transaction between two read, there is another transaction insert new data, and submit, and the inserted data satisfies the conditions of the query, resulting in different reading data.

Oracle read consistency is also divided into:

1. Statement-level Read consistency (non-repeatable)

2. Transactional level consistency Read

Statement consistency read, for example, the statement obtains the current SCN number 10001 prior to execution, and the other transaction T2 may modify the row of the block, set the consistency tag of the block in which the line is located, and generate the scn=10002, which requires the undo rollback to the SCN 10001 moment. is a consistent state, and this value is unchanged during the execution of a single statement.

Transaction-level consistency reads, the state of all data is in the state of the start of the transaction, unless the data is modified by this transaction, so that no repeatable read and fantasy reads can be avoided.

Oracle is through the isolation level to achieve transactional consistency read, transaction level can be rolled back to achieve consistent read, although others may be modified, but can be used to rollback the paragraph, the others modified to roll back.

Transaction-level consistency read the word serial read the so-called serial read is that you are reading, other people modify does not affect the results of your reading, you can ignore other people's changes. Although the database is in effect, it is ignored for serial transactions.

Serial reads are suitable for use in the following three situations:

A. DML short transactions in large databases that are mostly small datasets

B. A system with very low probability of modifying the same line.

C. Long running transactions are primarily read-only systems

This block is mainly considered from the rollback segment and should be serial read by rolling back the segment.

Set Isolation LEVEL commands

The code is as follows	Copy Code
1.SET TRANSACTION Isolation Level READ committed (submission degree isolation) 2.SET TRANSACTION Isolation Level SERIALIZABLE; (Serial read isolation level) 3.SET TRANSACTION isolation levels read only; (read-only isolation level)

There is also a setting

The code is as follows	Copy Code
ALTER session ALTER session SET Isolation_level=serializable; ALTER Session SET Isolation_level=read committed

Here's an example of serial isolation, with the following code

The code is as follows

Copy Code

Sql> CREATE table t (x int); Table created Sql> INSERT INTO T values (1); 1 row inserted Sql> commit; Commit Complete Sql> SET TRANSACTION isolation level serializable; Transaction Set Sql> select * from T; X --------------------------------------- 1 Sql> Sql> Declare 2 pragma autonomous_transaction; 3 begin 4 delete from T; 5 commit; 6 end; 7/ Pl/sql procedure successfully completed Sql> select * from T; X --------------------------------------- 1 Sql> commit; Commit Complete Sql> select * from T; X

---------------------------------------

In the middle of a homemade affair, the equivalent of a restart of a transaction, through the example can be seen, although deleted, but the first transaction can still be seen, this is actually in the database has been modified, just through the rollback segment can be seen, the last submission, and then view, you will find that the real deletion.

We start experimenting with a, analog statement-level read consistency. The first session uses the display to open a cursor to simulate data reading and, while the cursor reads the data, initiates another session to change the data, and I can see that another session changes the data, and does not change to the first session read. This is the statement-level read consistency.

Start a session connection database:

The code is as follows

Copy Code

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as CBO Sql> set serveroutput on; Sql> Sql> CREATE TABLE Test (ID number,name varchar2 (10)); Table created sql> INSERT INTO test values (1, ' a '); 1 row inserted sql> INSERT INTO test values (2, ' B '); 1 row inserted Sql> commit; Commit Complete Sql> Sql> Declare 2 cursor cur is select * from test; 3 begin 4 for rec in cur 5 loop 6 Dbms_output.put_line (Rec.name); 7 Dbms_lock.sleep (10);--wait for another session in the middle to start and perform the update data operation 8 End Loop; 9 End; 10/ A B Pl/sql procedure successfully completed Sql>

Start another process while performing the cursor printout, and perform the update data operation:

The code is as follows

Copy Code

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as CBO Sql> set serveroutput on; Sql> Sql> CREATE TABLE Test (ID number,name varchar2 (10)); Table created sql> INSERT INTO test values (1, ' a '); 1 row inserted sql> INSERT INTO test values (2, ' B '); 1 row inserted Sql> commit; Commit Complete Sql> Sql> Declare 2 cursor cur is select * from test; 3 begin 4 for rec in cur 5 loop 6 Dbms_output.put_line (Rec.name); 7 Dbms_lock.sleep (10); 8 End Loop; 9 End; 10/ A B Pl/sql procedure successfully completed Sql>

Let's start experimenting with two to simulate transactional-level read consistency.
Start a session first and read the data once:

The code is as follows	Copy Code
Sql> SET TRANSACTION READ only; Transaction Set Sql> select * from test; ID NAME ---------- ---------- 1 A 2 BBBB

Next we start another session and perform the update data operation:

The code is as follows	Copy Code
sql> Update test set name= ' 123456 '; 2 rows Updated Sql> commit; Commit Complete

Finally we go back to the first session view the data again:

The code is as follows	Copy Code
Sql> select * from test; ID NAME ---------- ---------- 1 A 2 BBBB

We'll find that the data we read doesn't change. So after setting the set TRANSACTION read only, the data read before and after a transaction does not change because of other seesion updates to the data.

Oracle Transaction Management

A transaction contains one or more SQL statements, which are logical managed units of work (atomic cells).
A transaction begins at the first executed SQL statement, ending with a commit or Rollback or DDL statement.
Note: The commit, rollback, is the display of the commit transaction, while the DDL statement is implicitly committing the transaction. The operation of a DDL statement is

There is no way to roll back.
##########################

The code is as follows

Copy Code

eg Sql> CREATE Table A (i int); Table has been created. Sql> INSERT into a values (1); 1 lines have been created. Sql> CREATE table B (i int); Table has been created. sql> rollback; Fallback is complete. Sql> select * from A;          i                                                                          ----------                                                                                   1

#############################
The transaction was committed when the CREATE table B was executed.
Where the end of the transaction is:
1>. Execute commit, Rollback, no use of savepoint.
2>. Performing DDL operations such as: Create, DROP, rename, alter
3>. Disconnect from Oracle and the transaction will be committed automatically.
4>. The user process terminates abnormally and the current transaction is rolled back.

Note: When an application is connected to an Oracle, the commit (commit) or rollback (Rollback) must be displayed before the application terminates.

The commit operation Oracle did:
1>. The internal transaction table associated with the undo table space records that the transaction has been committed, resulting in a unique system transaction number (SCN) saved to the table

In
2>. The LGWR process writes the redo log in the SGA to the Redo log file and, of course, the SCN to the Redo log file.
3>. Oracle frees rows in the locked table.
4>. Oracle sets the transaction to complete.

Note: The change data (stored in the SGA) before the commit operation is not immediately written to the data file. The purpose of this is also to data

Libraries are more efficient. This is also true from a developer's perspective, which can reduce the number of write disks for many small transactions.

Transaction-related in Oracle 10.2:

The code is as follows	Copy Code
Commit work write immediate wait; --is the default setting for Oracle. alter system set Commit_write = nowait; --Change the way the system submits Alter session Set commit_write = nowait; --Change the way the session is submitted

##############################
Submit a transaction eg:

The code is as follows	Copy Code
Sql> commit work;

Submit completed.

The code is as follows	Copy Code
Sql> show autocommit; Autocommit off Sql> CREATE TABLE T0 (testcol number);

Table has been created.

The code is as follows	Copy Code
sql> insert into T0 values (1);

1 lines have been created.

Sql> commit;

Submit completed.

The code is as follows	Copy Code
Sql> select * from t0; Testcol ---------- 1 sql> insert into T0 values (2); 1 lines have been created. Sql> commit work;

Submit completed.

The code is as follows	Copy Code
Sql> select * from t0; Testcol ---------- 1 2 #################################### Commit comment eg: