ORACLE Transaction Processing

ORACLE transaction sorting: 1. Why is there a transaction? Database Access is a multi-user operation with high concurrency. For example, a multi-user updates the same data at a certain time point, this results in serious data confusion, inconsistency, and destruction of data integrity and security. The Application of Database Transaction Management was born to address the high concurrency of multiple users.

ORACLE transaction sorting: 1. Why is there a transaction? Database Access is a multi-user operation with high concurrency. For example, a multi-user updates the same data at a certain time point, this results in serious data confusion, inconsistency, and destruction of data integrity and security. The Application of Database Transaction Management was born to address the high concurrency of multiple users.

ORACLE transaction sorting:

1. Why does a transaction exist?

Database Access is a multi-user operation with high concurrency. For example, a multi-user updates the same data at a certain time point, this results in serious data confusion, inconsistency, and destruction of data integrity and security.

The database transaction management application was born to solve the serious data chaos in the high concurrency of multiple users and control the possible data confusion and inconsistency in the multi-user high concurrency environment, ensures data integrity

And security. This is really a panacea (Transaction Management ). Assume that the database does not have the transaction processing feature, which has the following major consequences:

1). Dirty read: in the case of two concurrent transactions, one transaction reads the records updated but not committed by another transaction, and another transaction is in some way considering rolling back the update operation. The first data read is

Is the data that does not exist in the database, that is, dirty data, known as dirty read (Oracle does not contain dirty reads.).

2). Phantom read: A transaction reads different data at two different times and is updated by others.

3). Repeatable read: for a transaction, T1 has a query result set, T2 queries again, and data increases, and Others insert data.

To put it bluntly, Database Transaction Processing exists to solve the above three problems. When processing the database with the transaction feature, you are not afraid of the data operated by the current session and are disturbed by another session.

2. What is a transaction?

A transaction is a logical execution block composed of one or more SQL statements. If all the SQL statements in the transaction are successfully executed, the execution fails. Some SQL statements have been successfully executed or failed. (The transaction returns the same status or

A success or failure occurs because of the transaction isolation level. See the following description ). Transactions do not affect the execution of database operations.

3. What are the features of transactions?

1) atomicity: a transaction is either completely successful or completely failed. For example, there are 100 SQL statements in the transaction, or 100 SQL statements are successfully executed. If one execution fails, the transaction will execute other successful SQL statements.

Roll back to the previous status.

2). Consistency: the transaction status must be consistent. For example, if A bank transfers RMB 100 to B's account, this is B's account + RMB 100. At the same time, A's account must be in the range of $100, which is changed from A consistent state to another consistent state.

3). Isolation: transaction execution does not affect each other. Each transaction can only intervene in internal operations, but cannot intervene in other transaction operations.

4). Permanent: Once a transaction is committed, data changes will be permanent.

4. Control the transaction command (the transaction in oracle starts implicitly, and the end commit and rollback must be displayed at the end ):

1. commit; commit transaction: -- transaction ends

2. rollback; status before rollback: -- transaction ends

5. ORACLE transaction isolation level

1 ).READ COMMITTED: Read and commit transactions. Phantom reads and repeated reads are allowed. There is no dirty read. (Dirty reading does not exist in ORACLE)

2 ).SERIALIZABLE: Serialization level; only data submitted before the start of the transaction can be seen, without phantom read, no repeated read, dirty read.

3 ).READ ONLY: A read-only transaction. It is a subset of the serialization level. Only data submitted before the start of the transaction can be seen, but DML statements cannot be performed in the transaction.

There are only three transaction levels in ORACLE, of which the default read committed level exists. There are 5 standard SQL transaction levels (For details, refer to Baidu ).

6. Set the transaction isolation level syntax:

SET transaction isolation level [read commited | serializable] -- SET the read commit transaction and serialization level

SET transaction isolation level read only; -- SET read-only transactions

7. Transaction Processing drill (taking the local classes table as an example ):

1. Read committed transactions and serialized transactions

Session 1:

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903

SQL> insert into classes values (4, '123 ');
1 row inserted

-- The preceding statement adds a new piece of data in session 1, but no transaction is committed. Run Session 2 to query the table.

Session 2

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903

-- In the query result set of Session 2, no uncommitted data is found in transaction 1, because oracle reads the committed transaction read commited by default.That is, ORACLE does not have dirty reads because dirty reads are avoided.

Session 1: Submit a transaction

SQL> commit;
Commit complete

Transaction 2 executes the query statement again:

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903
4 0904

-- Session 2 contains the data committed by transaction 1, and Phantom reads occur.

Session 1: delete a newly added record and submit the transaction

SQL> delete from classes where cid = 4;
1 row deleted

SQL> commit;
Commit complete

Session 2: Execute the query statement again

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903

-- The query result of Session 2 is inconsistent with the previous query structure and does not contain cid = 4 data. The error message returned when an unrepeatable read occurs.

Session 2: sets the transaction level for Session 2: serialization level

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903

SQL> SET transaction isolation level serializable;
Transaction set

Session 1: Add a new piece of data and submit it

SQL> insert into classes values (4, '123 ');
1 row inserted

SQL> commit;
Commit complete

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903
4 0904

Session 2: Execute the query statement again

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903

-- Execute the query statement in session 2. The result does not contain the new data in transaction 1. No modifications made by other firms are visible, avoiding phantom read. Commit (end) The current transaction and execute the query statement again to view the new data row in transaction 1.

Session 2 commit transactions:

SQL> commit;
Commit complete

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903
4 0904

Session 2: Set transaction level to serialization level again

SQL> SET transaction isolation level serializable;
Transaction set

Session 1: Modify cid = 4 data and submit

SQL> update classes set cname = '000000' where cid = 4;
1 row updated

SQL> commit;
Commit complete

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903
4 0000 -- this record has been permanently modified

Session 2: run the query SQL statement again

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903
4 0904

-- When Session 2 executes a query statement, the data changed in session 1 is not displayed in the result, and the data modified by other firms is not displayed, avoiding repeated read.

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

Use the read-only level for transaction processing:

Session 2: Set transaction 2 to read-only

SQL> SET transaction read only;
Transaction set

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903

Session 1: Add a new piece of data and submit it

SQL> insert into classes values (4, '123 ');
1 row inserted

SQL> commit;
Commit complete

Session 2: Execute the SQL query result

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903

-- Execute the query statement in session 2. The result does not contain the new data in session 1. No modifications made by other firms are visible, avoiding phantom read. Commit the current transaction and set the transaction level to read-only transactions.

SQL> commit;
Commit complete

SQL> SET transaction read only;
Transaction set

Session 1: update a record and submit

SQL> update classes set cname = '000000' where cid = 4;
1 row updated

SQL> commit;
Commit complete

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903
4 0000

Session 2: Execute the query statement

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903
4 0904

-- The data changed by session 1 cannot be viewed in the query result of Session 2, and the changes made by other firms cannot be seen. This avoids repeated reads. Commit the current transaction. Set the transaction level to read level.

Transaction 2: Set to the read level and perform the data modification operation.

SQL> select * from classes;
CID CNAME
-------------------------------------------------
1 0901
2 0902
3 0903
4 0000

SQL> SET transaction read only;
Transaction set

SQL> update classes set cname = '000000' where cid = 4;
Update classes set cname = '000000' where cid = 4
ORA-01456: cannot perform insert/delete/update operations in read only transactions

-- An error is returned because ORACLE readable transactions can only be queried and cannot be modified.