Oracle Database transactions

Oracle Database transactions

1. What is a transaction

In a database, a transaction is a logical unit of work, a transaction consists of one or more SQL statements that complete a set of related behaviors, ensuring that the operations of this set of SQL statements are executed successfully, complete the entire unit of work, or do not execute either.

such as: online transfer is typical to use transactions to handle, to ensure the consistency of data.

2. Transaction characteristics

The SQL92 standard defines four characteristics of database transactions:

• Atomicity (atomicity): All the SQL statements contained within a transaction are performed as a whole, indivisible, either done, or not.
• Consistency (consistency): At the beginning of a transaction, the data in the database is consistent, and the data of the database should be consistent at the end of the transaction.
• Isolation (Isolation): refers to the ability of a database to read and write and modify data at the same time for multiple concurrent transactions, which prevents inconsistent state of data due to the cross-execution of their operations commands when the concurrent execution of a transaction occurs.
• Persistence (Durability): When a transaction ends, its impact on the database is permanent, and the data is not lost even if the system encounters a failure.

For a set of SQL statement operations to become transactions, the database management system must guarantee the atomicity (atomicity), consistency (consistency), isolation (isolation), and persistence (durability) of this set of operations, which is the acid feature.

3. Data anomalies

The following data exception occurs because Oracle supports concurrent execution of multiple transactions.

3.1 Dirty Reads

When one transaction modifies the data, the other transaction reads the data, but the first transaction cancels the modification of the data for some reason, so that the data is returned to the original state, which is called dirty read because the second transaction reads data that is inconsistent with the data in the database.

such as: Transaction T1 modified a piece of data, but not committed, the transaction T2 just read the modified data, at this time T1 the transaction rollback, this time T2 read the data is dirty data.

3.2 Non-repeatable READ

When a transaction reads data from a database, another transaction updates the data, and when the first transaction reads the data again, it finds that the data has changed, which is non-repeatable reading. Non-repeatable reads result in a different data read two times before and after a transaction.

For example, the transaction T1 reads a row of records, and the transaction T2 modifies the record that the T1 just read, and then T1 queries again, discovering that the record is different from the first read.

3.3 Phantom Reading

If a transaction reads data based on a condition, another transaction updates the data in the same table, and when the first transaction reads the data again, a different row is returned based on the search criteria, which is the Phantom read.

For example, the transaction T1 reads a statement that specifies the where condition and returns the result set. At this point the transaction T2 inserts a new row of records that exactly satisfies the T1 's where condition. Then the T1 uses the same conditions to query again, the result set can see T2 inserted record, this new record is phantom read.

These exceptions encountered in the transaction are related to the isolation of the transaction, the more the isolation setting of the transaction, the less the exception occurs, but the lower the concurrency effect, the less the isolation setting of the transaction, the more the exception occurs, and the higher the concurrency effect.

4. Transaction ISOLATION LEVEL

The isolation level of 4 transactions is defined in the SQL92 standard for inconsistencies that may occur when reading data:

Isolation Level	Dirty Read	non-repeatable read	Phantom Reading
READ UNCOMMITTED (not submitted)	Is	Is	Is
Read Committed (Reading submitted)	Whether	Is	Is
REPEATABLE READ (Repeatable Read)	Whether	Whether	Is
Serializable (Serial Read)	Whether	Whether	Whether

The default isolation level for Oracle is read committed.

Oracle supports two of the above four isolation levels: Read committed and Serializable. In addition, the read only and read write isolation levels are defined in Oralce.

Read only: A transaction cannot have any action statement that modifies data in the database, and is a subset of the serializable.

Read write: It is the default setting, which means that there can be access statements, modify statements, but not frequently used in a transaction.

Setting the isolation Level

To set the isolation level for a transaction:

• SET TRANSACTION Isolation level READ COMMITTED;
• SET TRANSACTION Isolation Level SERIALIZABLE;
• SET TRANSACTION READ only;
• SET TRANSACTION READ WRITE;

Note: These statements are mutually exclusive and cannot be set to two or more than two options at the same time.

To set the isolation level for a single session:

• ALTER SESSION SET TRANSACTION isolation level READ COMMITTED;
• ALTER SESSION SET TRANSACTION isolation SERIALIZABLE;

5. Transaction control commands

5.1 Committing a transaction

A COMMIT transaction can be committed when executing a USE statement, and when a commit statement is executed, the transaction is confirmed to be changed, the transaction is ended, the savepoint is deleted, and the lock is released. When a commit statement is used to end a transaction, other sessions will be able to view new data after the transaction has changed.

5.2 Rolling back transactions

SavePoint (savepoint): A point in a transaction that cancels a partial transaction and automatically deletes all the savepoint-defined save points when the transaction is ended. When rollback is executed, the specified savepoint can be rolled back to the specified point.

To set the save point:

SQL> savepoint A;

To delete a save point:

SQL> Release savepoint A;

To roll back a part of a transaction:

SQL>Rollback to A;

Roll Back all transactions:

SQL>Rollback;

6. Database lock

A database is a shared resource used by multiple users. When multiple users access data concurrently, in the database, multiple transactions are generated concurrently to access the same data. If the concurrency operation is not controlled, it is possible to read and store incorrect data and compromise the consistency of the database.

There are two basic types of locks in the database: Exclusive lock (Exclusive Locks, X Lock) and shared lock (Share Locks, S lock). When a data object is added to an exclusive lock, other transactions cannot read and modify it; A data object with a shared lock can be read by another transaction, but cannot be modified.

6.1 Lock classification

Depending on the protection object, the Oracle database lock can be divided into:

• DML Lock (data locks): Used to protect the integrity of data.
• DDL Lock (dictionary locks, Dictionary Lock): The structure used to protect database objects (such as the structure definition of tables, views, indexes).
• Internal Locks and latches (internal lock and latch): Protects the internal database structure.
• Distributed locks (distributed lock): used in OPS (parallel server).
• PCM Locks (parallel cache management Lock): Used in OPS (parallel server).

The most important lock in Oracle is a DML lock, which is designed to guarantee data integrity in the case of concurrency. In Oracle databases, DML locks mainly include TM and TX locks, where TM locks are called table-level locks, and TX locks are called transaction or row-level locks.

Locks appear on data sharing occasions to ensure data consistency. When multiple sessions modify a table at the same time, the data needs to be locked accordingly.

The lock has a "shared lock", "exclusive lock", "shared exclusive lock" and many other types, and each type has a "row-level lock" (lock one record at a time), "page-level lock" (lock one page at a time, that is, the database stores the smallest assignable unit of the record), "table-level lock" (lock the entire table).

6.2 Shared Lock (S lock)

The S lock can be added via the lock table in share mode command. In this lockdown mode, no user is allowed to update the table. But allow other users to issue a select ... from the FOR UPDATE command to add RS locks to the table.

6.3 Exclusive Lock (x Lock)

The x lock can be added via the lock table in exclusive mode command. In this lockdown mode, other users cannot perform any DML and DDL operations on the table, which can only be queried.

6.4 Row-Level shared lock (RS lock)

This is usually done through the SELECT ... from UPDATE statement, which is also the primary method we use to manually lock some records. For example, when we query some records, we do not want other users to update the records of the query, you can issue such a statement. When the data is used, the direct issue of the rollback command unlocks the lock. When RS lock is added to the table, no other transactions are allowed to add exclusive locks to the same table, but other transactions are allowed to lock other rows of data in the same tables through the DML statement or the lock command.

6.5 Row-level exclusive lock (RX Lock)

The RX lock is automatically added to the updated table when the DML operation is performed, or you can explicitly add the RX lock on the table by executing the lock command. In this lock mode, other transactions are allowed to modify other rows of data in the same table through DML statements, or the lock command adds RX locks to the same tables, but does not allow other transactions to add exclusive locks (x locks) to the same table.

6.6 Shared row-level exclusive locks (SRX locks)

Add the SRX lock via the lock table in share row exclusive mode command. This locking mode is higher than the level of row-level exclusive and shared locks, and you cannot perform DML operations on the same table or add shared locks.

RS Lock is the least restrictive lock in the above several lock modes, and the X lock is the most restrictive lock. Their compatibility relationship is as follows:

Basically all locks can be created and freed automatically by Oracle internally, but DDL and DML locks can be managed through commands, with command syntax:

inch     [row Share] [Row exclusive][share][share row Exclusive] [exclusive][NOWAIT] ;

Lists the SQL statements that generate the lock mode:

When a program commits (commits) or rolls back (Rollback) The changes made, the locked resources are freed, allowing other users to operate. A deadlock occurs if two transactions lock part of the data, while waiting for the other to release the lock to complete the transaction operation.

7. Database transaction Implementation Mechanism

In almost all database management systems, the mechanism of transaction management is implemented by using log files, let us briefly explain how the log works.

When a user executes a DML statement that modifies a database, the DBMS automatically writes a record in the log file showing two copies of each record affected by the statement. One copy shows the record before the change, and the other copy shows the changed record. After the log is written, the DBMS actually modifies the records on the disk.

If the user then executes the commit statement, the transaction end is also logged in the transaction log. If the user executes the rollback statement, the DBMS checks the log to find out what the record "before" has been modified since the start of the transaction, and then uses that information to restore their previous state and effectively revoke the changes made to the database during the transaction.

If a system error occurs, the system operator typically recovers the database by running a special recovery program provided by the DBMS. The recovery program checks to the end of the transaction log to find transactions that were not committed before the failure. The recovery program rolls back transactions that are not fully completed so that only committed transactions are reflected in the database, and the transactions that are processed in the fault are rolled back.

The use of transaction logs significantly increases the cost of updating the database. In practice, the log technology used by mainstream commercial DBMS products is more complex than the scenario described above to reduce this overhead. In addition, transaction logs are typically stored in high-speed disk drives, unlike disks that store databases, to reduce disk access contention. Some personal computer DBMS products allow transaction log performance to be turned off to improve the performance of the DBMS.

8. Example

Examples of bank transfers are the most classic examples of transactions:

The user transfers money from one bank account to another bank account, which needs to be withdrawn from one bank account and then deposited into another bank account. Ideally, these two operations should be successful. However, if an error occurs, then two operations should fail, otherwise the amount in one account after the operation will be wrong, the entire operation should be atomic, and two operations are part of an atomic transaction operation.

Example:

--Account transfer from accountDECLAREV_money Number(8,2);--Transfer AmountV_balance account.balance%TYPE;--account BalanceBEGINV_money:= &The amount of the transfer;--Enter the transfer amount  --from account one minus money  UPDATEAccountSETBalance=Balance-V_moneyWHEREId=&transfer out account returning balance intov_balance; IFSql%NOTFOUND ThenRaise_application_error (-20001,'no such account:'|| &transfer out of account); END IF; IFV_balance< 0  ThenRaise_application_error (-20002,'Insufficient account balance'); END IF; --add money to account two  UPDATEAccountSETBalance=Balance+V_moneyWHEREId=&transfer to account; IFSql%NOTFOUND ThenRaise_application_error (-20001,'no such account:'|| &transfer to account); END IF; --If there is no exception, commit the transaction  COMMIT; Dbms_output. Put_Line ('Successful Transfer'); EXCEPTION whenOTHERS Then       ROLLBACK;--Rollback Transaction If exception occursDbms_output. Put_Line ('Transfer failure:'); Dbms_output. Put_Line (SQLERRM);END;

Oracle Database transactions