Oracle Transaction Features

Oracle transaction features a transaction refers to a complete logical process composed of a series of database operations. For example, the bank transfers, deducts the amount from the original account, and adds the amount to the target account. The sum of the two database operations constitutes a complete logical process and cannot be split. This process is called a transaction and has ACID properties. 1: Atomicity (or severability) 2: Consistency 3: Isolation (Isolation, also called Independence) 4: Durability (Durability) Atomicity: all operations in a transaction are either completed or not completed. When a transaction encounters an error during execution, it will be rolled back to the state before the start of the transaction, just as this transaction has never been executed. Consistency: the integrity of the database is not damaged before and after the transaction starts. This indicates that the written data must fully comply with all the default rules. This includes the data accuracy, concatenation, and the subsequent database can automatically implement the pre-defined work isolation: the relationship between two or more concurrent transactions when accessing the same data of the database (the access here refers to the query and modification operations. Transaction isolation is divided into different levels, including Read uncommitted, read committed, repeatable read, and Serializable ). Durability: after the transaction is completed, the changes made to the database by the transaction will be permanently stored in the database and will be complete. If several transactions need to access the same data items, in order to ensure Database Consistency, these transactions can be made: ① serial execution: that is, one transaction is executed one by one; ② concurrent execution: that is to say, multiple transactions are executed at the same time, but the interaction between different transactions is solved through the concurrency control mechanism. Transaction Commit and rollback: Commit (Commit) notifies the Transaction Manager that a logical unit of work has been completed, the update operation can be committed or permanently retained, indicating that the transaction successfully ends and the execution of the Validity Check RollBack notifies the transaction manager that the transaction fails to be completed normally, and the database may be in an inconsistent state, the undo of all update operations performed by the current transaction indicates that the transaction is not successfully completed. Currently, there are two main methods to achieve ACID: Write ahead logging, or log-like. The second type is Shadow paging. The recovery steps of system logs are as follows: 1. Reverse scanning of file logs (that is, scanning the log files forward at the end) to find the update operation of the transaction. 2. Perform the inverse operation on the update operation of the transaction. Write the "pre-update value" log into the database. In this way, if the record is an insert operation, it is equivalent to a delete operation; if the record is a delete operation, it is an insert operation; if it is a modification operation, it is equivalent to replacing the modified value with the modified value. 3. Continue to reverse scan the log file, find other updates to the transaction, and perform the same processing as 2. 4. After such processing, the fault recovery of the transaction will be completed until the start mark of the transaction is read. Concurrency problem: there are two ticket sites, and the balance of the ticket in the database of a certain train ticket is X. Two tickets are sold at the same time, and the balance is changed to X-1 and written back to the database. This causes the actual sale of two tickets, but only one record is missing in the database. This is because two transactions read the same data and modify the data at the same time. The result of one transaction commit destroys the result of another transaction commit, resulting in the loss of data modification, this damages the isolation of things. Concurrency Control should solve these problems. First, check the lock concept: blocking is a technology used for multiple users to access the database at the same time, and is an important means to achieve concurrency control. It can prevent data loss and damage when multiple users rewrite the database. When a user operates on the data in the database, the data is locked before reading the data, so that other users cannot access and modify the data, it is not until the data is modified and written back to the database. Solution: Pessimistic Concurrency Control (also known as "Pessimistic lock", Pessimistic Concurrency Control, abbreviated as "PCC") is a method of Concurrency Control. It can prevent a transaction from modifying data in a way that affects other users. If all the operations performed by a transaction apply a lock to a row of data, only when the transaction releases the lock can other transactions execute operations that conflict with the lock. Pessimistic concurrency control is mainly used in environments where data contention is fierce, and when a concurrency conflict occurs, the cost of using locks to protect data is lower than the cost of rollback transactions. Concurrency Control is actually a Conservative policy of "getting locks first and then accessing", which guarantees data processing security. However, in terms of efficiency, the locking mechanism can cause additional costs for the database and increase the chance of deadlock. In addition, the read-only transactions do not conflict with each other, there is no need to use locks. In this way, only the system load can be increased. In addition, the concurrency will be reduced. If a transaction locks a row of data, other transactions can process the data only after the transaction is completed. Optimistic Concurrency Control (also known as "Optimistic lock", Optimistic Concurrency Control, abbreviated as "OCC") is a method of Concurrency Control. It assumes that multi-user concurrent transactions do not affect each other during processing, and each transaction can process the data that affects each other without a lock. Before submitting data updates, each transaction first checks whether other transactions have modified the data after the transaction reads the data. If other transactions are updated, the transaction being committed will be rolled back. Optimistic Concurrency Control is mostly used in environments with little competition for data and less conflicts. In such environments, the occasional cost of rollback transactions is lower than the cost of locking data when reading data, therefore, we can obtain a higher throughput than other concurrency control methods. Transactions with optimistic concurrency control include the following phases: Read: when a transaction reads data into the cache, the system assigns a timestamp to the transaction. Verification: after the transaction is executed, it is committed. In this case, all transactions are verified synchronously. If the data read by the transaction is modified by another transaction after the transaction is read, a conflict occurs and the transaction is interrupted and rolled back. Write: after the verification stage is passed, the updated data is written to the database. Advantages and disadvantages: Optimistic Concurrency Control believes that the probability of data race between things is relatively small. Therefore, do it as directly as possible and do not lock it until it is submitted, therefore, no locks or deadlocks are generated. However, if you simply do this, you may still encounter unexpected results. For example, if both transactions read a row of the database and write it back to the database after modification, at this time, I encountered a problem. I am familiar with the concept of Timestamp and the precision of Timestamp: Timestamp (English: Timestamp) refers to the addition of recognized text, such as time or date, to a series of materials, the update sequence of local data is consistent with that of remote data. An example of the timestamp is as follows: T 02:16:57 UTC2007-11-09 t utc Sat Jul 23 2005UTC world coordination: Calculated using international atomic time: the error of International Atomic Time is several nanoseconds per day