Oracle Transaction Features and oracle transaction details

Oracle Transaction Features and oracle transaction details
Atomicity

A transaction is a complete operation. The operations in each step of a transaction are inseparable (atomic); they are either executed or not executed.

-- Create table account_money (id number (4) not null, name varchar2 (4) not null, money number (5, 2) not null ); -- add an inspection constraint alter table account_money add constraint CK_money check (money> = 0 );

 

-- Add data insert into ACCOUNT_MONEY (ID, NAME, MONEY) values (1001, 'zhang san', 500.00) to account Zhang San; insert into ACCOUNT_MONEY (ID, NAME, MONEY) values (1002, 'zhang san', 1.00 );

The added table is as follows:

ID NAME MONEY
1 1001 Zhang San 500.00
2 1002 Zhang San 1.00

 

Oracle Transaction Processing

BEGIN -- transfers data from Michael Jacob's 1001 account to Michael Jacob's 1002 Account UPDATE account_money a SET. money =. money-600 WHERE. id = '000000'; UPDATE account_money a SET. money =. money + 600 WHERE. id = '000000'; COMMIT; -- COMMIT transaction EXCEPTION -- EXCEPTION Handling when others then rollback; -- roll back Dbms_Output.Put_Line ('transfer EXCEPTION, transfer failed') if an EXCEPTION occurs ');

In the above Code, because the account is configured with check constraints, when the account is less than 0, an exception will occur. If no transaction exception is processed, the second update statement will be executed. After a transaction exception is processed, it will be rolled back when an exception occurs.

Consistency

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

The preceding table is also used for description:

DECLARE account_a account_money.Money % TYPE; account_ B account_money.Money % TYPE; BEGIN SELECT. money INTO account_a FROM account_money a WHERE. id = '000000'; SELECT. money INTO account_ B FROM account_money a WHERE. id = '000000'; Dbms_Output.Put_Line ('account balance before transfer: '| account_a); Dbms_Output.Put_Line ('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 = '2016 '; UPDATE account_money SET money = money + 100 where id = '000000'; COMMIT; SELECT. money INTO account_a FROM account_money a WHERE. id = '000000'; SELECT. money INTO account_ B FROM account_money a WHERE. id = '000000'; Dbms_Output.Put_Line ('account balance after transfer: '| account_a); Dbms_Output.Put_Line ('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 cancel'); SELECT. money INTO account_a FROM account_money a WHERE. id = '000000'; SELECT. money INTO account_ B FROM account_money a WHERE. id = '000000'; Dbms_Output.Put_Line ('account balance after the transfer is stopped: '| account_a); Dbms_Output.Put_Line ('account balance after the transfer is stopped:' | account_ B ); dbms_Output.Put_Line ('total balance after the transfer is stopped: '| (account_a + account_ B); END;

 

 

Execute the code above,

Execute the first time:

Balance of account A before transfer: 500
Balance of Account B before transfer: 1
Total balance before transfer: 501
Balance of account A after transfer: 400
Balance of Account B after transfer: 101
Total balance after transfer: 501

Run the second time:

Balance of account A before transfer: 400
Balance of Account B before transfer: 101
Total balance before transfer: 501
Balance of account A after transfer: 300
Balance of Account B after transfer: 201
Total balance after transfer: 501

Perform the third time:

Balance of account A before transfer: 300
Balance of Account B before transfer: 201
Total balance before transfer: 501
Balance of account A after transfer: 200
Balance of Account B after transfer: 301
Total balance after transfer: 501

......

When the execution is performed 5th times: the balance of Account A is 0. What if the execution is performed again?

Balance of account A before transfer: 100
Balance of Account B before transfer: 401
Total balance before transfer: 501
Balance of account A after transfer: 0
Balance of Account B after transfer: 501
Total balance after transfer: 501

Execute 6th times, 7th times ............ :

Balance of account A before transfer: 0
Balance of Account B before transfer: 501
Total balance before transfer: 501
Transfer failed and service canceled
Balance of account A after the transfer is stopped: 0
B account balance after the transfer is stopped: 501
Total balance after the transfer is stopped: 501

 

We will find that the total amount will not change after the transaction processing, and will not be executed (or rolled back) When an exception occurs )!

 

Isolation

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

Each transaction is independent. We create two SQL windows in PL/SQL to process two transactions.

We still use the above table. The table content is as follows:

ID NAME MONEY
1 1001 Zhang San 0.00
2 1002 Zhang San 501.00

The first SQL window:

 

The second SQL window:

Input: SELECT * FROM account_money; the query statement shows that the data in the first SQL window is not executed.

What if we use update to update data in the second SQL window?

UPDATE account_money SET money = money + 300 where id = '201312 ';
SELECT * FROM account_money;

He will wait for the first SQL window to submit the transaction before updating the result!

At this time, we commit the transaction in the first SQL window. We will see that the results of both windows have changed.

 

We will commit the transaction in the second SQL window (the transaction in the second window cannot be committed before the transaction in the first SQL window is committed), and the result is the same!

 

Durability

After the transaction is completed, its changes to the database are permanently saved.