On Oracle Business "reprint Bamboo-Lek Pinellia"

On Oracle Business "reprint Bamboo-Lek Pinellia"

The so-called transaction, he is an operation sequence, these operations are either executed, or not executed, is an inseparable unit of work. The popular explanation is that the business is to do a lot of things as one thing, that is, everyone in a boat, die together, to live together.

Why are transactions introduced? Transaction processing ensures that data-oriented resources are not permanently updated unless all operations within the transactional unit are completed successfully. By combining a set of related actions into a single unit that either succeeds or all fails, you can simplify error recovery and make your application more reliable. The consistency of database data can be ensured after the transaction is over. For example, bank transfer, an account deduction of money, an account increase, either executed, either do not execute, can not only execute one, or there will be a bill error. So that we can appreciate the importance of the matter. In fact, in our life, a lot of things are to be composed of affairs to carry out. Since it's so important, let's take a closer look at what a business is.

I. Four characteristics of a transaction acid

The four characteristics of a transaction are important, and only those characteristics are met to ensure that the transaction executes only one result: either success or failure.

(1) atomicity (atomicity): The statement that makes up the transaction forms a logical unit and cannot execute only part of it.

(2) Consistency (CONSISTEMCY): Before and after transaction processing, the data is in a consistent state, ensuring the lossless data.

(3) Isolation (Isolation): Multiple transactions that modify data are isolated from each other and are not mutually affected.

(4) Persistence (durability): After the transaction is complete, its effect on the system is permanent, and the modification will persist even if a system failure occurs, and the database is actually modified.

Example One

---------CREATE TABLE Bank (CustomerName CHAR (10),-Customer name Currentmoney number (10)-current balance);------ ---Add the balance cannot be less than 1 of the constraint Alter TABLE bank add CONSTRAINT Ck_currentmoney CHECK (Currentmoney >= 1);---------inserting test data insert INTO Bank (CustomerName, Currentmoney) VALUES (' Zhang San ', +), INSERT into bank (customerName, Currentmoney) VALUES (' John Doe ', 1); Commit;select * from bank;---------do not use transactions to complete the transfer operation (total amount exception) update bank set currentmoney=currentmoney-1000 where CustomerName    = ' Zhang San '; Update bank set currentmoney=currentmoney+1000 where customername= ' John Doe '; commit;---------erase data, delete Bank;select * from Bank;---------use transaction to complete the transfer operation does not appear the total amount exception set Serveroutput on;declare Li_money number: =0;begin Update bank set Currentmoney  =currentmoney+1000 where customername= ' John Doe ';   Select Currentmoney into Li_money from bank where Customername= ' John Doe '; Dbms_output.put_line (' John Doe ' current balance is: ' | |  Li_money); Update bank set currentmoney=currentmoney-1000 where Customername= ' Zhang San '; exception when others then DBMS_OUTPUT.PUt_line (' Revocation of submissions '); rollback;end;--after the transfer to view the results, John Doe's money was rolled back to the select * from bank;

Two. Rollback to save point

(1) Rollback: Roll back to the original state, you can refer to example one.

(2) Rollback to retention point: roll back to the retention point.

Example Two

DECLARE  num int;begin  savepoint A;--create save point a  update bank set currentmoney=currentmoney-100 where Customername= ' Zhang San ';  SavePoint b; --Create Save point B  Update Bank set currentmoney=currentmoney+100 where  customername= ' John Doe ';  SavePoint C; --Create a savepoint C  num:=&num;--Receives a data from the cue box, which is used to scroll back to the point.  INSERT into bank values (' Harry ', +);  SavePoint D; --Create SavePoint D  if Num=1 then     rollback to A;--When you enter 1 o'clock in the prompt box, rollback to savepoint A, which is all rollback, equivalent to rollback;  elsif num=2 then     rollback to B;--When you enter 2 o'clock in the prompt box, roll back to save point B  elsif num=3 then     rollback to C;--When you enter 3 o'clock in the prompt, roll back to the savepoint c< C19/>else     rollback to D;--in other cases, roll back to save point D  end if;end;

Three. Isolation level of a transaction

From the example above, we can see the great benefits that the transaction brings to us, which guarantees the consistency of the data, but is there a guarantee that the data will be consistent before and after it is only guaranteed with transaction execution? In practice, transactions are performed concurrently, and it is inevitable that multiple transactions will operate on the same data at the same time, resulting in a variety of concurrency problems.

Concurrency problem: (1) Dirty read: Transaction T1, T2,T1 read the T2 has changed but not committed data, after T2 rollback, T1 read the data is temporarily invalid dirty data.

(2) Non-repeatable READ: Transaction T1, T2,t1 reads a field, and T2 updates the field, and when T1 reads the field again, the value is different.

(3) Magic read: Transaction T1, T2,t1 read a field, T2 in the table and insert some fields, when the T1 read again, there will be a few more lines.

To solve these problems, the isolation level is introduced, and it is clear that the isolation level refers to the degree to which a transaction is isolated from other transactions, and what isolation levels are, as outlined below.

Isolation level	Describe
READ UNCOMMITTED (READ UNCOMMITTED data)	Allows transactions to read changes that have not been committed by other transactions, and the dirty read, non-repeatable read, and Phantom read problems will occur.
Read Committed (reading submitted data)	Only allow transactions to read changes that have been committed by other transactions, avoid dirty reads, but non-repeatable read and Phantom read problems persist.
Repeatable committed (repeatable reading)	Ensure that a transaction can read from one field to the same value multiple times, avoiding dirty reads and non-repeatable reads, but the Phantom read problem persists.
Serializble (serialization)	During the execution of this transaction, other tables are prevented from inserting, deleting, modifying, and all concurrency problems can be avoided, but performance is poor.

2 Kinds of transaction isolation levels supported by Oracle: READ commited, SERIALIZABLE. The default transaction isolation level for Oracle is: READ commited.　　Oracle also provides an isolation level of Read only. Although the isolation level avoids concurrency problems, it is necessary to use it as appropriate, after all, the higher the isolation level, the weaker the concurrency, and the slower the performance of the software. Four. Summary(1) A transaction has only one execution result, either success or failure.　　(2) The four characteristics of a transaction: atomicity, consistency, isolation and continuity.　　(3) When a problem occurs during the execution of a transaction, it can be rolled back directly to the state of the data before it was originally executed, or it can be savepoint to a specific savepoint location by setting the save point. (4) Concurrency problems can occur when multiple transactions access the same data at the same time, in order to avoid these problems, the isolation level is introduced. However, the higher the isolation level, the better the consistency of data, but the weaker the concurrency. So use it as appropriate.

On Oracle Business "reprint Bamboo-Lek Pinellia"