Transaction Processing and concurrency control, transaction processing Concurrency Control

Transaction Processing and concurrency control, transaction processing Concurrency Control

What is a transaction?

Is a mechanism, an operation sequence, it contains a group of database operation commands, and all the commands as a whole, together to submit or cancel the operation request to the system. It consists of one or more SQL statements that complete a related behavior. Is an inseparable unit of work logic.

In transaction processing, once an operation has an exception, the entire transaction will start again, the database will return to the status before the transaction starts, and all operations performed on the database in the transaction will be canceled. If the transaction succeeds, all operations in the transaction will be executed.

Transaction control statement: COMMIT a transaction, that is, to permanently Save the changes to the database in the transaction.

ROLLBACK: rolls back the transaction to cancel any modification made to the database.

Features of transactions:1. Atomicity: a transaction is a complete operation. Each step of a transaction cannot be divided (atomic), either executed or not executed. 2. Consistency: data must be consistent before and after transaction operations. 3. Isolation: all concurrent transactions that modify data are isolated from each other, which indicates that the transaction must be independent and should not depend on or affect other transactions in any way. 4. Durability: after the transaction is completed, its modifications to the data are permanently maintained.

Example: ACCOUNT_BALANCE indicates the user balance ACCOUTN_ID indicates the user ID.

BEGIN
  UPDATE ACCOUNT SETACCOUNT_BALANCE = ACCOUNT_BALANCE-4000;
        WHERE ACCOUNT_ID = ’1001’;
  UPDATE ACCOUNT SETACCOUNT_BALANCE = ACCOUNT_BALANCE + 4000;
        WHERE ACCOUNT_ID = ’1002’;
COMMIT;
EXCEPTION
        WHERE OTHERS THEN ROLLBACK;
DBMS_OUTPUT.PUT_LINE (‘Transfer exception, stop transferring!’);
END;

Transaction consistency requirements: Before the start of transaction processing, all data in the database meets the business rule constraints. After the transaction processing is complete, all data in the database still meets the business rule constraints.

Example:

DECLARE
       account_a ACCOUNT.ACCOUNT_BALANCE% TYPE;
       account_b ACCOUNT.ACCOUNT_BALANCE% TYPE;
BEGIN
       SELECT ACCOUNT_BALANCE INTO account_aFROM ACCOUNT WHERE ACCOUNT_ID = ’1001’;
       SELECT ACCOUNT_BALANCE INTO account_bFROM ACCOUNT WHERE ACCOUNT_ID = ’1002’;
       dbms_output.put_line (‘A amount before transfer:’);
       dbms_output.put_line (account_a);
       dbms_output.put_line (‘Amount before transfer:’);
       dbms_output.put_line (account_b);
       dbms_output.put_line (‘Total amount before transfer:’);
       dbms_output.put_line (account_a + account_b);
       UPDATE ACCOUNT SETACCOUNT_BALANCE = ACCOUNT_BALANCE-2000
WHERE ACCOUNT_ID = ’1001’;
       UPDATE ACCOUNT SETACCOUNT_BALANCE = ACCOUNT_BALANCE + 2000
WHERE ACCOUNT_ID = ’1002’;
       COMMIT;
       dbms_output.put_line (‘Successful transfer!’);
       SELECT ACCOUNT_BALANCE INTO account_aFROM ACCOUNT WHERE ACCOUNT_ID = ’1001’;
       SELECT ACCOUNT_BALANCE INTO account_bFROM ACCOUNT WHERE ACCOUNT_ID = ’1002’;
       dbms_output.put_line (‘A amount after transfer:’);
       dbms_output.put_line (account_a);
       dbms_output.put_line (‘Amount after transfer:’);
       dbms_output.put_line (account_b);
       dbms_output.put_line (‘Total amount after transfer:’);
       dbms_output.put_line (account_a + account_b);
       EXCEPTION WHEN OTHERS THEN ROLLBACK;
       dbms_output.put_line (‘Transfer is abnormal, stop transferring!’);
       SELECT ACCOUNT_BALANCE INTO account_aFROM ACCOUNT WHERE ACCOUNT_ID = ’1001’;
       SELECT ACCOUNT_BALANCE INTO account_bFROM ACCOUNT WHERE ACCOUNT_ID = ’1002’;
       dbms_output.put_line (‘A amount after stopping the transfer:’);
       dbms_output.put_line (account_a);
       dbms_output.put_line (‘B amount after stopping the transfer:’);
       dbms_output.put_line (account_b);
       dbms_output.put_line (‘Total amount after stopping the transfer:’);
       dbms_output.put_line (account_a + account_b);
END;

Read exception: 1. Dirty read: one transaction reads the uncommitted data of another transaction. 2. It cannot be read repeatedly. When a transaction reads data previously, 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 defined in ANSISQL-92 standards:

ReadUncommitted is the lowest transaction isolation. It only ensures that no illegal data is read during the read process,

ReadCommitted: this level of bookstore isolation ensures that one transaction will not read the data modified but not committed by another parallel transaction, that is, this level of transaction level avoids "Dirty read ".

RepeatableRead: transaction isolation at this level prevents "Dirty read" and "non-repeated read" exceptions. This also means that it is impossible for a transaction to update data that has been read but not committed by another transaction. It may cause phantom read.

Serializable, the highest level of isolation, provides the highest level of isolation mechanism, can avoid three kinds of exceptions.

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

Oracle transaction isolation level: 1. ReadCommitted. 2. Serializable;

3. Read Only is a subset of serialable, but there cannot be any statement (DML) for modifying data in the database and statements (DDL) for modifying the database structure in the transaction );

 

 

 

No special statements are required in Oracle to start transactions. Implicitly, the transaction starts at the first fishing tackle where the data is modified.

End transaction: 1. The COMMIT statement explicitly terminates a transaction. When a COMMIT statement is executed, all changes to the data in the transaction are saved to the database. 2. When a ROLLBACK statement is executed, any modifications made to the database in the transaction will be canceled. 3. Execute a DDL statement. If a DML statement exists before the DDL statement, Oracle submits the preceding DML statement as a transaction. 4. the user disconnects from Oracle and the user's current transaction is automatically submitted. 5. the user process is unexpectedly terminated, and the user's current transaction is rolled back.

Transaction control statement:1. COMMIT: COMMIT a transaction, that is, to permanently Save the changes to the database in the transaction. 2. ROLLBACK: rolls back the transaction to cancel any modification made to the database. 3. SAVEPOINT: Create a storage point in the transaction. 4. ROLLBACKTO <SAVEPOINT_NAME>: rolls back the transaction to the storage point. 5. set transaction: SET the TRANSACTION attributes.

SAVEPOINT: Creates a storage point in a transaction. Syntax: SAVEPOINT [SavePoint_Name];

ROLLBACKTO <SavePoint_Name>: rolls back the transaction to the storage point. Syntax: rollback to [SavePoint_Name];

Example:

BEGIN
UPDATE ACCOUNT SETACCOUNT_BALANCE = ACCOUNT_BALANCE + 2000 WHEN ACCOUNT_ID = ’1001’;
SAVEPOINT Add_Account_A;
UPDATE ACCOUNT SET ACCOUNT_BALANCE = ACCOUNT_BALANCE-6000 WHEN ACCOUNT_ID = ’1001’;
UPDATE ACCOUNT SETACCOUNT_BALANCE = ACCOUNT_BALANCE + 6000 WHEN ACCOUNT_ID = ’1002’;
COMMIT;
EXCEPTION
WHENOTHERS THEN dbms_output.put_line (‘Transfer exception!’);
ROLLBACK TO Add_Account_A;
END;

The SETTRANSACTION statement must be the first statement of the transaction. It can specify the isolation level of the transaction, specify the bucket used for transaction rollback, and name the transaction.

Set transaction read only; set transaction isolation level readcommited; set tranasction isolation level serializable;

Concurrency Control:It refers to implementing concurrent transaction operations in the correct way to avoid data inconsistency.

Concurrency Control issues: 1. Loss of updates. When one transaction modifies a row of data, the other transaction modifies the row of data at the same time, causing the first transaction to lose the modification of the data. 2. Dirty reading. 3. Do not read repeatedly. 4. Phantom read.

Basic concepts of lock:Lock, a mechanism used to share resources to control concurrent access. The lock is automatically managed by Oracle. The lock duration is equal to the time when the transaction is committed for processing.

Lock type: 1. Shared lock (the data object using the shared lock can be read by other transactions but cannot be modified), also known as the s lock. 2. exclusive lock, also known as x lock. Lock-protected content classification: DML locks are used to protect data integrity and consistency; DDL locks are used to protect the definition of data object structures; internal locks and latches are used to protect the internal data structures of databases.

Deadlock: Two transactions (sessions) are in a stopped state when they wait for each other to lock resources.

Solve the deadlock: "sacrifice" one session, roll back one session transaction, and continue the transaction of the other session.

When a deadlock occurs, the Oracle database creates a trace file on the server to record the deadlock.

Note: do not provide artificial conditions during development to cause a deadlock in Oracle.

Blocking: If a session holds a lock on a resource, and another session requests this resource, it will cause blocking.

Lock Mechanism problems: 1. pessimistic locks refer to locking related resources immediately after reading data. Syntax: SELECT ............ FORUPDATE [OF column_list] [WAIT n | NOWAIT] OF clause is used to specify the columns to be updated, that is, to lock specific columns on the row. The WAIT clause specifies the number OF seconds to WAIT for other users to release, NOWAIT indicates no waiting.

Example:

SELECT * FROM ACCOUNT WHEREACCOUNT .ID=’1001’ FOR UPDATE;
UPDATE ACCOUNT SETBALANCE=BALANCE-500 WHERE ID=’1001’;

Optimistic lock: delays all locks until the update is to be executed.

Syntax:

UPDATE Table_Name SETColumn_Name1=NewValue1,Column_Name2=NewValue2……
WHERE Column_Name1=OldValue1 ANDColumn_Name2=OldValue2……

Example:

DECLARE 
 account_a ACCOUNT.BALANCE%TYPE;
BEGIN
       SELECT balance INTO account_a FROMACCOUNT WHERE ID=’1001’;
       UPDATE ACCOUNT SET balance=balance-500WHERE ID=’1001’ AND BALANCE=account_a;
END;

Lock category:The DML lock is used to ensure that only one user can modify a row at a time. When a table is being processed, no one else can delete the table.

DML locks mainly include TX locks and tmlocks, where TX locks are transaction locks or row-level locks, and tmlocks become table-level locks.

TX lock: The TX lock is automatically obtained when the transaction initiates the first statement to modify data, and the lock is always held to know whether the transaction is committed or rolled back. The TX lock is used as a queuing mechanism so that other sessions can wait for the transaction to run. Each row modified in the transaction or selected through pessimistic locking points to a related TX lock of the transaction.

Tmlock: used to ensure that the table structure does not change when the table is modified. When a session starts to update a table, the tmlock of the table is automatically obtained. This prevents, in addition, execute the DROP or ALTER Statement on the table to delete the table or change the structure of the table.

DDL lock: used to protect the definition of data object structures. DDL operations automatically add DDL locks to database objects.

Note: in Oracle, DDL statements are encapsulated in implicit commit (rollback) to perform operations.

Is it better to use Lock for concurrent processing and transaction processing? (C #)

Transaction Processing

Concurrent Control of database transactions. Let me talk about transaction processing by the way.

In six cases, the order of submission is as follows:
Case 1: T1 T2 T3 x = 3
Case 2: T1 T3 T2 x = 6
Case 3: T2 T1 T3 x = 3
Case 4: T2 T3 T1 x = 4
Case 5: T3 T1 T2 x = 8
Case 6: T3 T2 T1 x = 7

Transaction processing knowledge points:
1. a transaction is the smallest unit of work. Whether it is successful or not, it works as a whole.
2. transactions have ACID attributes, namely, Atomicity, Consistency, Isolation, and Durabilily ).
The atomic attribute is used to identify whether the transaction is completely completed. Any update of a transaction must be completely completed on the system. If an error occurs for some reason, the transaction cannot complete all its tasks, the system returns the status before the start of the transaction.
Transaction consistency is implemented in system integrity, which is achieved by ensuring that any transaction in the system is in a valid state at last. If the transaction is successfully completed, all changes in the system will be correctly applied and the system is in a valid state. If an error occurs in the transaction, all changes in the system are automatically rolled back, and the system returns to the original state. Because the system is in the consistent state at the beginning of the transaction, the system is still in the consistent state.
Execute transactions in the isolation status, making them as if they are the only operations performed by the system within a given time. If two transactions run at the same time and perform the same functions, the isolation of the transaction will ensure that each transaction is considered to be only the transaction in the system.
Durability means that once the transaction is successfully executed, all changes generated in the system will be permanent. Check Points should exist to prevent information loss when the system fails. Even if the hardware itself fails, the system status can still be re-built by recording the tasks completed by the transaction in the log. The concept of durability allows developers to consider that the completed transactions are permanent parts of the system no matter what changes have taken place in the future.