On Oracle Business

　　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 a tableCREATE TABLEBank (CustomerNameCHAR(Ten),--Customer NameCurrentmoney Number(Ten)--Current Balance);---------Add a constraint that the balance cannot be less than 1ALTER TABLEBankADD CONSTRAINTCk_currentmoneyCHECK(Currentmoney>= 1);---------inserting test dataINSERT  intoBank (CustomerName, Currentmoney)VALUES('Zhang San', +);INSERT  intoBank (CustomerName, Currentmoney)VALUES('John Doe',1);Commit;Select *  fromBank;---------do not use transactions to complete the transfer operation (total amount exception)    UpdateBankSetCurrentmoney=Currentmoney- + whereCustomerName='Zhang San'; UpdateBankSetCurrentmoney=Currentmoney+ + whereCustomerName='John Doe';Commit;---------Erase the data,DeleteBank;Select *  fromBank;---------The transaction is complete without a total amount exceptionSetServeroutput on;DeclareLi_money Number(Ten):=0;begin  UpdateBankSetCurrentmoney=Currentmoney+ + whereCustomerName='John Doe'; SelectCurrentmoney intoLi_money fromBankwhereCustomerName='John Doe'; Dbms_output.put_line ('John Doe The current balance is:'||Li_money); UpdateBankSetCurrentmoney=Currentmoney- + whereCustomerName='Zhang San'; exception whenOthers ThenDbms_output.put_line ('Revoke a submission'); rollback;End;--Check the results after the transfer, John Doe's money was rolled backSelect *  fromBank

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

DeclareNumint;beginsavepoint A;--Create save point a  UpdateBankSetCurrentmoney=Currentmoney- - whereCustomerName='Zhang San'; SavePoint b; --Create save point B  UpdateBankSetCurrentmoney=Currentmoney+ - whereCustomerName='John Doe'; SavePoint C; --Create save point CNum:=&Num--receives a data from the prompt box that is used to roll back to which point.   Insert  intoBankValues('Harry', -); SavePoint D; --Create save point D  ifNum=1  Then     rollback  toA--when you enter 1 o'clock in the prompt box, roll back to savepoint A, which is all rolled back, equivalent to rollback;elsif num=2  Then     rollback  tob--when you enter 2 o'clock in the prompt box, roll back to save point Belsif num=3  Then     rollback  toC--When you enter 3 o'clock in the prompt box, roll back to save point C  Else     rollback  toD--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