Oracle Transactional features detailed

Atomic Nature

A transaction is a complete operation. The steps of a transaction are non-divided (atomic), either executed or not executed.

--Create TABLE Account_money (  ID number    (4) Not NULL,  name  varchar2 (4) isn't null,  money number ( 5,2) not NULL);--Add a CHECK constraint ALTER TABLE Account_money  add constraint Ck_money  check (money>=0);

--Add data to Zhang San this account insert into Account_money (ID, name, money) VALUES (1001, ' Zhang San ', 500.00); INSERT into Account_money (ID, name, M Oney) VALUES (1002, ' Zhang San ', 1.00);

The following table is added:

ID NAME Money
1 1001 Sheets of three 500.00
2 1002 sheets of three 1.00

The following is Oracle transaction processing

BEGIN  -Transfer from Zhang San's 1001 account to Zhang San's 1002 account  UPDATE Account_money a SET a.money=a.money-600 WHERE a.id= ' 1001 ';  UPDATE Account_money a SET a.money=a.money+600 WHERE a.id= ' 1002 ';  commit;--COMMIT TRANSACTION exception--Exception handling  when OTHERS then rollback;--exception is rolled back  dbms_output.put_line (' Transfer exception, transfer failed ');  

In the above code, because the account is set to check constraints, when the account is less than 0 o'clock, an exception will occur, if the transaction exception processing, then the second UPDATE statement will be executed. When a transaction exception is handled, the exception is rolled back.

Consistency

Before and after a transaction operation, the data must be in a consistent state. is a category of business rule constraints.

Also use the table above to illustrate:

DECLARE account_a Account_money.  Money%type; Account_b Account_money. Money%type;   BEGIN SELECT A.money to account_a from Account_money a WHERE a.id= ' 1001 ';   SELECT A.money to Account_b from Account_money a WHERE a.id= ' 1002 '; Dbms_output.put_line (' A account balance before transfer: ' | |  ACCOUNT_A); Dbms_output.put_line (' B account balance before transfer: ' | |  ACCOUNT_B); Dbms_output.put_line (' Total balance before transfer: ' | |  (Account_a+account_b));  UPDATE Account_money SET money=money-100 WHERE id= ' 1001 ';  UPDATE Account_money SET money=money+100 WHERE id= ' 1002 ';  COMMIT;   SELECT A.money to Account_a from Account_money a WHERE a.id= ' 1001 ';   SELECT A.money to Account_b from Account_money a WHERE a.id= ' 1002 '; Dbms_output.put_line (' A account balance after transfer: ' | |  ACCOUNT_A); Dbms_output.put_line (' B account balance after transfer: ' | |  ACCOUNT_B); Dbms_output.put_line (' Total balance after transfer: ' | |  (Account_a+account_b));  EXCEPTION when OTHERS then Dbms_output.put_line (' Transfer failed, business canceled ');   SELECT A.money to Account_a from Account_money a WHERE a.id= ' 1001 '; SELECT A.money to Account_b from Account_money a WHere a.id= ' 1002 '; Dbms_output.put_line (' account balance after stop transfer: ' | |  ACCOUNT_A); Dbms_output.put_line (' B account balance after stop transfer: ' | |  ACCOUNT_B); Dbms_output.put_line (' Total balance after stop transfer: ' | | (Account_a+account_b)); END;

Executes the previous segment of code,

Perform the first pass:

Pre-Transfer a account balance: 500
Pre-Transfer B account balance: 1
Total Balance before transfer: 501
After transfer a account balance: 400
B account balance after transfer: 101
Total Balance after transfer: 501

Perform the second pass:

Pre-Transfer a account balance: 400
Pre-Transfer B account balance: 101
Total Balance before transfer: 501
After transfer a account balance: 300
B account balance after transfer: 201
Total Balance after transfer: 501

Perform the third pass:

Pre-Transfer a account balance: 300
Pre-Transfer B account balance: 201
Total Balance before transfer: 501
After transfer a account balance: 200
B account balance after transfer: 301
Total Balance after transfer: 501

。。。。。。

When executing the 5th pass: the balance of the A account is 0, if the execution will be a phenomenon?

Pre-Transfer a account balance: 100
Pre-Transfer B account balance: 401
Total Balance before transfer: 501
After transfer a account balance: 0
B account balance after transfer: 501
Total Balance after transfer: 501

Perform the 6th and 7th times ...... ：

Pre-Transfer a account balance: 0
Pre-Transfer B account balance: 501
Total Balance before transfer: 501
Transfer failed, business canceled
After stop transfer a account balance: 0
B account balance after stop transfer: 501
Total balance after stop transfer: 501

We will find that when we do the transaction, the total amount will not change and will not be executed (or rolled back) when there is an exception!

Isolation of

All concurrent transactions that modify the data are isolated from each other, which indicates that the transaction must be independent and that it does not in any way depend on or affect other transactions.

Each transaction is independent, and we can do two transactions in PL/SQL by creating a new two windows.

We still use the table above, the table content is as follows:

ID NAME Money
1 1001 Sheets of three 0.00
2 1002 sheets of three 501.00

First SQL window:

A second SQL window:

Input: SELECT * from Account_money; This query statement, we will find that the first SQL window did not execute the previous data.

What happens if we update the data in the second SQL window using an update?

UPDATE Account_money SET money=money+300 WHERE id= ' 1001 ';
SELECT * from Account_money;

He will wait for the first SQL window to commit the transaction before it has the update result!

When we submit the transaction for the first SQL window, we will see that the results of the two windows are changed.

We then commit the transaction of the second SQL window (the transaction of the second window cannot be committed until the first SQL window transaction is committed), and the result is the same!

Durability

After the transaction is complete, its modifications to the database are permanently preserved.

Oracle Transactional features detailed