Transaction Processing (learning notes), transaction processing learning notes

Transaction Processing (learning notes), transaction processing learning notes

Transaction:

A mechanism, an operation sequence, is the logical unit of database work

One or more SQL statements that complete a set of related behaviors

An inseparable logical unit of work

Concept of transactions:

An inseparable logical unit of work,

Ensure database integrity

Transaction Features: ACID properties

Atomicity: (Atomicity ):A transaction is a complete operation. The operations in each step of the transaction cannot be divided (atomic), either executed or not executed.

 

Consistency ):Before and after a transaction operation, the data must be in the consistent state.

 

Isolation (Isolation ):All concurrent transactions that modify data are isolated from each other. This indicates that the transaction must be independent and should not depend on or affect other transactions in any way.

 

Durability ):After the transaction is completed, it is permanently maintained for the database.

Create a table:

Create table acc (id number (19), NAME VARCHAR2 (20) not null, bal NUMBER (19,3) not null, CONSTRAINT pd_id primary key (ID ), CONSTRAINT ck_bal CHECK (bal> = 0); SELECT * FROM acc; insert into acc VALUES (1001, 'zhang san', 3000); insert into acc VALUES (1002, 'zhang san', 1 );

 

 

1) Atomicity(Atomicity)

A transaction is a complete operation. The operations in each step of the transaction cannot be divided (atomic), either executed or not executed.

Simulate bank transfers

Begin update acc SET bal = bal-4000 where id = 1001; -- Transfer 4000, but the balance is only 3000; UPDATE acc SET bal = bal-4000 where id = 1001; -- Transfer 4000 COMMIT; -- commit transaction EXCEPTION -- 1001 an EXCEPTION occurs WHEN the account is transferred out. when others then -- capture dbms_output.put_line ('account EXCEPTION, transfer failed! '); -- Prompt message ROLLBACK; -- roll back the data to the END before the transfer; -- query to find that the data has returned to the SELECT * FROM acc before the transfer;

2) Consistency ):

Before and after a transaction operation, the data must be in the consistent state.

Simulate bank transfers

DECLARE v_a acc. bal % TYPE; -- balance TYPE v_ B acc. bal % TYPE; v_sal acc. bal % TYPE; -- calculate the total amount begin select bal INTO v_a FROM acc where id = 1001; -- check the balance of account A to SELECT bal INTO v_ B FROM acc WHERE ID = 1002; -- check the balance of Account B dbms_output.put_line ('balance of A before transfer: '| v_a); dbms_output.put_line ('balance of B before transfer:' | v_ B ); dbms_output.put_line ('total balance of A and B before transfer: '| (v_a + v_ B); -- start transfer UPDATE acc SET bal = bal-2000 WHERE ID = 1001; UPDATE acc SET bal = bal + 2000 where id = 1002; COMMIT; -- submit SELECT bal INTO v_a FROM acc where id = 1001; -- SELECT bal INTO v_ B FROM acc where id = 1002 for account A; -- check dbms_output.put_line for account B's balance ('balance of account A after transfer: '| v_a ); dbms_output.put_line ('balance of B after transfer: '| v_ B); dbms_output.put_line ('total balance of A and B after transfer:' | (v_a + v_ B )); exception when others then dbms_output.put_line ('account EXCEPTION, transfer failed'); -- roll back to the ROLLBACK before transfer; -- SELECT bal INTO v_a FROM acc where id = 1001 WHEN the transfer fails; -- SELECT bal INTO v_ B FROM acc where id = 1002 for account A; -- check dbms_output.put_line for account B's balance ('balance of account A upon failure: '| v_a ); dbms_output.put_line ('balance of B upon failure: '| v_ B); dbms_output.put_line ('total balance of A and B upon failure:' | (v_a + v_ B); END;

3) Isolation ):

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

Simulate bank transfers

-- First transfer but do not submit DECLAREBEGIN -- start transfer UPDATE acc SET bal = bal-2000 where id = 1001; UPDATE acc SET bal = bal + 2000 where id = 1002; END; -- at this time, the second user starts to UPDATE acc SET bal = bal + 1001 where id = 1000 for 1001 account deposits; -- found that the account remains in the waiting state, because the transfer is not completed first, A transaction is not processed at the same time. When multiple users simultaneously process the same transaction, either the previous transaction is obtained, either get the status after processing -- Here return the transfer and submit DECLAREBEGIN -- start transfer UPDATE acc SET bal = bal-2000 where id = 1001; UPDATE acc SET bal = bal + 2000 where id = 1002; COMMIT; END; -- return to the second person's deposit window and find that it has been completed. You can submit UPDATE acc SET bal = bal + 1000 where id = 1001; COMMIT;

Transaction read exception:

1,Dirty read:One Transaction reads the uncommitted data of another transaction.

2,Non-repeated read: When a transaction reads data that has previously been read again, it finds that the data has been modified by another committed transaction.

3,Phantom read: A transaction re-executes the query based on the same query conditions. The returned records contain rows different from the records returned by the previous query.

Transaction isolation level:

Transaction level defined in ANSI SQL-92 standards:

1,Read Uncommitted: The lowest level of transaction isolation, which only ensures that no illegal data is obtained during the read process.

2,Read Comitted: Transaction isolation at this level ensures that one transaction does not read the data modified but not committed by another parallel transaction. This level of transaction does not avoid "dirty reads".

3,Repeatable Read: Transaction isolation at this level avoids the occurrence of "Dirty read" and "non-repeated read" exceptions. One transaction cannot update the data that has been read by another transaction but has not been committed (rolled back.

4,Serializable: The highest level of isolation, provides the highest level of isolation mechanism, can avoid three kinds of exceptions. The transaction is executed in serial mode.

 

Isolation level	Dirty read	Non-repeated read	Phantom read
Read Uncommitted	Possible	Possible	Possible
Read Comitted	Impossible	Possible	Possible
Repeatable Read	Impossible	Impossible	Possible
Serializable	Impossible	Impossible	Impossible

 

 

 

Isolation level provided in Oracle:

1,Read Comitted:Oracle's default isolation level. This level of transactions ensures that one transaction does not read data that has been modified but not committed by another parallel transaction. That is to say, this level of transactions avoid "dirty reads ".

2,Serializable:The highest isolation level provides the highest isolation mechanism, which can be avoided in three abnormal situations.

3,Read Only: Read Only is a subset of Serializable. It indicates that no statement (DML) can be used to modify data in the database or a DDL Statement (DDL) to modify the data structure ). Read-only is not allowed.

Transaction:

No special statements are required in Oracle to start the transaction. The implicit transaction starts at the first statement to modify the data.

End transaction:

Transaction control statement:

1. COMMIT: COMMIT transactions and save database modifications.

2. ROLLBACK: roll back the transaction and cancel the modification to the database.

3. SAVEPOINT: Create a storage point in the transaction.

4. rollback to <SAVEPOINT>: rolls back the transaction TO the storage point.

5. set transaction: SET the TRANSACTION attributes.

Transaction control statement:

Set autocommit = OFF	Cancel automatic submission
Set autocommit = ON	Enable automatic submission,
COMMIT	Commit transactions
ROLLBACK	Roll back the transaction and cancel the modification to the database.
Name of the SAVEPOINT transaction retention point	SET transaction retention point
ROLLBACK () TO [ROLLBACK point]	Rollback
SET TRANSACTION	SET transaction attributes

COMMITAndROLLBACEKIt can be written as follows:Commit work, ROLLBACK WORK

SAVEPOINTCreate a storage point in a transaction

Syntax:SAVEPOINT [SAVEPOINT_NAME]The name can be left empty.

Rollback to <SAVEPOINT_NAME>Rolls back a transaction to a specified storage point.

Transaction control statement:

Set transaction:

• The set transaction statement must be the first statement of the TRANSACTION.
• Specify the transaction isolation level
• Specifies the storage space used by the transaction to roll back the transaction

Set the transaction level:

Set storage points

-- Simulate bank transfer: SELECT * FROM acc; declarebegin update acc set bal = BAL + 2000 where id = 1001; -- first deposit 2000, but do not submit SAVEPOINT ACC_ADD; -- SET the storage point update acc set bal = BAL-6000 where id = 1001; -- Transfer 6000, if the balance is insufficient, the Director update acc set bal = BAL + 6000 where id = 1002; -- transfer another account to 6000 COMMIT; -- submit exception when others then DBMS_OUTPUT.PUT_LINE ('data EXCEPTION, transfer failed! '); Rollback to ACC_ADD; -- roll back TO the storage point END set when 2000 is saved;

-- Bank transfers are in the process

 

SELECT * FROM acc; -- create or replace procedure proc_tran (fromacc in number, toacc in number, sal in number) isbegin dbms_output.put_line ('transfer start '); UPDATE acc SET bal = bal-sal WHERE id = fromacc; UPDATE acc SET bal = bal + sal WHERE id = toacc; dbms_output.put_line ('transfer successful '); COMMIT; exception when others then dbms_output.put_line ('account EXCEPTION, transfer failed'); ROLLBACK; end proc_tran; -- call process DECLAREBEGIN proc_tran (1001,1002, 3000); END;