Transaction Processing and 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 = '000000'; update account setaccount_balance = account_balance + 1001; where account_id = '000000'; commit; Exception Where others then rollback; dbms_output.put_line ('transfer exception, stop transfer! '); 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 = '000000'; select account_balance into account_bfrom account where account_id = '000000'; dbms_output.put_line ('amount before transfer :'); dbms_output.put_line (account_a); dbms_output.put_line ('amount of B before transfer: '); dbms_output.put_line (account_ B); dbms_output.put_line ('before Transfer Total amount: '); dbms_output.put_line (account_a + account_ B); update account setaccount_balance = ACCOUNT_BALANCE-2000WHERE account_id = '000000'; update account setaccount_balance = account_balance + 1001 where account_id = '2016'; commit; dbms_output.put_line ('transfer successful! '); Select account_balance into account_afrom account where account_id = '000000'; select account_balance into account_bfrom account where account_id = '000000'; dbms_output.put_line ('amount A after transfer :'); aggregate (account_a); Aggregate ('B 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; DB Ms_output.put_line ('transfer exception, stop transfer! '); Select account_balance into account_afrom account where account_id = '000000'; select account_balance into account_bfrom account where account_id = '000000'; dbms_output.put_line ('amount A after the transfer is stopped :'); round (account_a); Round ('B amount after the transfer is stopped:'); Round (account_ B); dbms_output.put_line ('total amount after the transfer is stopped: '); 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:

Beginupdate account setaccount_balance = account_balance + 2000 when account_id = '000000'; savepoint add_account_a; update account set account_balance = ACCOUNT_BALANCE-6000 when account_id = '000000 '; update account setaccount_balance = account_balance + 6000 when account_id = '000000'; 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.