Oracle transaction feature management read details

This article summarizes Oracle transactions, introduces transaction features, transaction management, transaction reads, and provides examples. For more information, see this article.

This article summarizes Oracle transactions, introduces transaction features, transaction management, transaction reads, and provides examples. For more information, see this article.

This article summarizes Oracle transactions, introduces transaction features, transaction management, transaction reads, and provides examples. For more information, see this article.

Oracle Transaction Features
ACID refers to the abbreviation of the four basic elements for the correct execution of a transaction, including: Atomicity, Consistency, Isolation, and Durability ). A database system that supports transactions must have these four features. Otherwise, data correctness cannot be guaranteed during the Transaction process (Transaction processing, the transaction process cannot meet the requirements of the transaction party.
ATOMICITY)

All the operations in the entire transaction are either completed or not completed, and it is impossible to stop at a stage in the middle. When a transaction encounters an error during execution, it will be rolled back to the state before the start of the transaction, just as this transaction has never been executed.

CONSISTENCY)

The integrity constraints of the database are not damaged before and after the transaction starts.

ISOLATION)

The execution of two transactions does not interfere with each other. One transaction cannot see the data at a certain time in the middle of another transaction.

DURABILITY)

After the transaction is completed, the changes made by the firm to the database are permanently stored in the database and will not be rolled back.

In oracle, there are usually three types of reads:

First, the error reading (uncommitted read) means reading the data modified but not submitted by another transaction, and there is no real consistent read.

The second type: Non-repeated reads (read distortion) are two reads before and after a transaction. The read values are not equal, because it was modified or deleted by other things at the interval of the two reads and submitted.

The third type of read: fantasizing that a transaction is between two reads, another transaction inserts new data and commits it, And the inserted data meets the query conditions, the data read is different.

Oracle read consistency is divided:

1. Statement-level read consistency (repeated read is not allowed)

2. Transaction-level consistent read

Statement-based consistent read. For example, if the statement obtains the current scn number 10001 before execution, and the other transaction T2 may modify the row of the block and set the consistent mark of the block where the row is located, the generated SCN = 10002. In this case, you need to apply the undo rollback to the time when the scn is 10001, that is, the consistency state. This value will not change during the execution of a single statement.

Transaction-level consistent read. The state of all data is at the beginning of the transaction, unless the data modified by this transaction, this can avoid repeated read and fantasy read.

Oracle implements consistent read for transactions at the isolation level. Transaction-level consistent read can be achieved through rollback segments. Although others may have modified it, rollback segments can be used, roll back the modified ones.

Transaction-level consistent reading means that serial reading means that if you read data by yourself, others' modifications do not affect your reading results. You can ignore others' modifications. Although it takes effect for the database, it is ignored for serial transactions.

Serial reading is applicable to the following three scenarios:

A. Most large databases use DML short transactions for small datasets.

B. Systems with low chances of modifying the same row.

C. long-running transactions are mainly read-only systems.

This is mainly from the rollback segment. It should be achieved through the rollback segment for serial reading.

Set isolation-level commands

The Code is as follows:
1. set transaction isolation level read committed; (submission isolation level) 2. set transaction isolation level serializable; (Serial read isolation level) 3. set transaction isolation level read only; (READ-only isolation level)

Another setting

The Code is as follows:
ALTER SESSION Alter session set ISOLATION_LEVEL = SERIALIZABLE; Alter session set ISOLATION_LEVEL = READ COMMITTED

The following is an example of serial isolation:

The Code is as follows:

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> * 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

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

A self-made transaction is used in the middle, which is equivalent to restarting a transaction. The example shows that although the transaction is deleted, the first transaction can still be seen that the transaction has been modified in the database, you can only view the rollback segments. The last commit and then view the segments, and you will find that the segments are actually deleted.

We started Experiment 1 to simulate statement-level read consistency. The first session uses the display to open a cursor to simulate Data Reading. When the cursor reads data, another session is started to change the data. I can see the data changes of another session, it does not change to the read of the first session. This is statement-level read consistency.

Start a session to connect to the database:

The Code is as follows:

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 to start and update data. 8 end loop; 9 end; 10/ A B PL/SQL procedure successfully completed SQL>

When executing the cursor print output, start another process and update the data:

The Code is as follows:

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>

Next we will start Experiment 2 to simulate transaction-level read consistency.
Start a SESSION and read the data once:

The Code is as follows:
SQL> SET TRANSACTION READ ONLY; Transaction set SQL> select * from test; ID NAME -------------------- 1 2 bbbb

Next we start another session to update the data:

The Code is as follows:
SQL> update test set name = '20140901 '; 2 rows updated SQL> commit; Commit complete

Finally, we return to the first session to view the data again:

The Code is as follows:
SQL> select * from test; ID NAME -------------------- 1 2 bbbb

We will find that the read data has not changed. Therefore, after set transaction read only is SET, the data READ by the statement before and after a TRANSACTION will not change because other seesion updates the data.

Oracle Transaction Management

A transaction contains one or more SQL statements. It is a logical unit of work (atomic unit ).
The SQL statement that starts the first execution of a transaction and ends with a Commit, Rollback, or DDL statement.
Note: Here, Commit and Rollback are displayed Commit transactions, while DDL statements are implicitly committed transactions. The DDL statement operation is

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

The Code is as follows:
Eg: SQL> create table a (I int ); The table has been created. SQL> insert into a values (1 ); One row has been created. SQL> create table B (I int ); The table has been created. SQL> rollback; Rollback completed. SQL> select * from; I ---------- 1

#############################
The transaction is committed when you execute create table B.
Where the transaction ends:
1>. Execute Commit and Rollback. savepoint is not used.
2>. Execute DDL operations such as create, drop, rename, and alter.
3>. Disconnect from Oracle and the transaction will be automatically committed.
4>. The user process terminates abnormally and the current transaction is rolled back.

Note: The submission or Rollback information that must be displayed before the application is terminated ).

Commit has performed the following operations on Oracle:
1>. The internal transaction table associated with the UNDO tablespace records that the transaction has been committed, and a unique system transaction number (SCN) is generated and saved to the table.

.
2>. The LGWR process writes the redo log in SGA to the redo log file. Of course, it also needs to write the SCN to the redo log file.
3>. Oracle releases the rows in the locked table.
4>. Oracle sets the transaction to complete.

Note: The changed data (saved in SGA) before the Commit operation is not immediately written to the data file. This is also intended for Data

Library is more efficient. This is also the case from the developer's perspective, which can reduce the number of writes to the disk for many small transactions.

Transaction-related Oracle 10.2:

The Code is as follows:
Commit work write immediate wait; -- it is the default setting of Oracle. Alter system set commit_write = nowait; -- change the system submission method Alter session set commit_write = nowait; -- change the session submission method.

##############################
Commit a transaction. For example:

The Code is as follows:
SQL> commit work;

Submitted.

The Code is as follows:
SQL> show autocommit; Autocommit OFF SQL> create table t0 (testcol number );

The table has been created.

The Code is as follows:
SQL> insert into t0 values (1 );

One row has been created.

SQL> commit;

Submitted.

The Code is as follows:
SQL> select * from t0; TESTCOL ---------- 1 SQL> insert into t0 values (2 ); One row has been created. SQL> commit work;

Submitted.

The Code is as follows:
SQL> select * from t0; TESTCOL ---------- 1 2 #################################### Commit comment eg: