Oracle transaction isolation level

The basic function of transactions in the database is to switch the database from the consistent state to another consistent state, the transaction isolation level defines how sensitive a transaction is to the modifications made by another transaction ". That is, different isolation levels define the degree to which transactions affect each other. The following describes several different isolation levels. 1. read uncommitted in fact, oracle does not support this isolation level. This isolation level allows dirty reads (that is, data not submitted by the user can be read). databases with this isolation level are mainly supported to support non-blocking reads, however, oracle supports non-blocking read by default, so oracle does not support this isolation level. The following is an example:
As shown in, assume that a bank wants to count the total amount of all accounts. Transaction A is responsible for statistics, and transaction A reads from the first row. Assume that transaction B is transferred from account 100 to account 123 when Row 3 is read (transaction B has not yet submitted ), when transaction A reads 342023 rows, the database that supports dirty reading will get 500 yuan, thus adding 400 yuan. 2. read committed refers to that a transaction can only read committed data (but supports Repeatable READ and fantasy READ), which is the default Isolation Mode for oracle databases. In fact, such isolation levels may be "degraded" like dirty reads in other databases. Let's take A look at the previous example. Assume that transaction A locks this row in advance before reading row 342023, and changes the amount from 100 to 500. When transaction A reads this row, it finds that it has been locked by other transactions, so it waits until transaction B commits. However, after transaction B is committed, transaction A still reads the error message 500, which is the same as dirty read and allows the user to wait for the answer to the error. 3. The repeatable read isolation level does not support dirty reading, REPEATABLE reading, and fantasy reading. It is mainly used to obtain consistent answers and prevent missing updates. Www.2cto.com. the consistent answer is implemented through the Multi-version mechanism in oracle. However, in other databases, the lock mechanism must be used for control. In the preceding example, how can we calculate the correct total amount? When transaction A reads each row, it adds A shared read lock to each row, in this way, when transaction B is executed from account 123 to account 400. First, the amount of account 123 in the first line is changed from 500 to 100, but the first line has been locked by transaction A, so wait, so that transaction A can read the correct data. However, if transaction B is performing operations from account 987 to account 123, transaction B first operates on Row 342,023rd and finds that the transaction is not locked, therefore, the lock changes the amount from 100 to 50, and then operates the first row, and finds that the lock is waiting. When transaction A reads 342023 rows, it finds that this row has been locked by transaction B and thus waits, so it is in A deadlock. B. lost updates are stored in databases with shared read locks. This isolation level can prevent the loss of updates, for example, transaction 1 first reads Row A and then modifies column C of this row (other columns also modify the value, but it is still the same as before, because the programmer is updating the whole row ). At this time, when transaction 2 wants to modify Row A, it will be blocked to prevent the update of transaction 1 from being overwritten. 4. SEAIALIZABLE does not allow dirty reads, repeated reads and fantasy reads, with the highest isolation level. This isolation level indicates that when transaction A operates the database, it seems that only transaction A is operating, and no other transaction is operating the database. In Oracle, SERIALIZABLE transactions are implemented in this way: the read consistency originally obtained at the statement level can now be extended to the transaction level. That is, when the transaction is executed, a photo of the data to be operated by the transaction is taken. From the above example, we can see that other databases use shared read locks to solve the problem of total statistical amount. The oracle multi-version mechanism is not flexible, which seriously affects the concurrency of the program and the read blocking of writing. Second, it may cause deadlocks. Author liwenshui322