Oracle Database isolation level, features, Problems and Solutions

"…… A veteran will not follow the instructions at all, and he will make trade-offs, so he must concentrate on his work, even if he does not deliberately do this, there is a natural sense of harmony between his actions and machines. He does not need to follow any written instructions, because the machines in his hands give him the feeling to decide his thoughts and actions, and also affect his work. Therefore, the machine and his thoughts are constantly changing until they are done well, and his heart is truly peaceful ."
"It sounds like art ."
　　　　　　　　　　　　　　　　　　-- Robert M. Bosse Zen and motorcycle repair Art

If no database isolation policy exists, the following problems may occur during multi-user (Multi-transaction) concurrency:
-Lost update): When two transactions update the same data at the same time, the update will be lost.
For example, if user A reads A student whose student ID is 107 (student ID = 107, name = "James", age = 28)
=> User B reads 107 student (student ID = 107, name = "James", age = 28)
=> User A changed his name to "Wang Xiaoming" (student ID = 107, name = "Wang Xiaoming", age = 28)
=> User B changed the age to 33 (student ID = 107, name = "Xiao Ming", age = 33)
=> Submitted by user A (student ID = 107, name = "Wang Xiaoming", age = 28)
=> Submitted by user B (student ID = 107, name = "Xiao Ming", age = 33)
User A's student name update is lost.
-Dirty read): Dirty reads are generated when one transaction reads the changes that have not been committed by another transaction.
For example, if user A reads A student whose student ID is 107 (student ID = 107, name = "James", age = 28)
=> User A changed his name to "Wang Xiaoming" (student ID = 107, name = "Wang Xiaoming", age = 28)
=> User B reads 107 student (student ID = 107, name = "Wang Xiaoming", age = 28)
=> User A cancels the change and rolls back the transaction (student ID = 107, name = "James", age = 28)
In this way, user B reads an existing data "Wang Xiaoming ". If the amount is involved, the problem is even more serious, because after user B reads an amount, it is likely to accumulate the amount with other amounts, and then save the result to the summary data, in this way, when user A rolls back the transaction at the end of the month, there will be no operation records in the database, in this way, when and where user B reads the error data cannot be found.
　　-Nonrepeatable read): The same query is performed multiple times in the same transaction. During this period, because other transactions commit data modification or deletion, different results are returned each time.
For example, assume that there are only two records in the student table: "Xiao Ming" and "Xiao Li" (Xiao Ming. Age = 20, Xiao Li. Age = 30 ). If user A changes "Xiao Ming. Age" to 40, "Xiao Li. Age" to 50, and submits the transaction. Before user A modifies and submits the data, the average age of the student is (20 + 30)/2 = 25. After user A modifies the data and submits the data, the average age of students is (40 + 50)/2 = 45.
Now, when user A updates the age of two students, user B executes A transaction that calculates the average age:

As mentioned above, before user A modifies and submits data, the average age of the student is (20 + 30)/2 = 25. After user A modifies and submits the data, the average age of students is (40 + 50)/2 = 45. The average age calculated by user B is 25 or 45, which is acceptable. However, in the preceding example, the average age 35 calculated by user B is an error value that has never occurred in the system.
-Phantom read): The same query is performed multiple times in the same transaction. Although the query conditions are the same for the insert operations performed by other commit firms, the returned result sets are different each time.

Transaction B uses the same conditions for Two Queries/filtering, one for inserting summary data into the expense statement table, and one for determining the update range of the expense statement list. Between the two filters, transaction A commits A new expense detail data, resulting in inconsistent results of the two filters.

Isolation level

To avoid the above concurrency problems, ANSI/ISO SQL92 standards define some isolation levels:

-Read uncommitted data (read uncommitted)

-Read committed data (read committed)

-Repeatable read)

-Serializable)

By specifying different isolation levels, you can avoid one or more of the preceding concurrency problems. See.

Careful readers may have noticed that "lost update" is not included, because the "lost update" issue needs to be solved using optimistic or pessimistic locks, which is beyond the scope of this article, do not detail it first.

Oracle isolation level

The isolation level defined by SQL92 is theoretically perfect, But Oracle obviously believes that the actual implementation should not completely copy the SQL92 model.
-Oracle does not support the "read uncommitted data (read uncommitted)" isolation level in the SQL92 standard. It is impossible to read dirty data.
-Oracle supports the "read committed data" isolation level in the SQL92 standard (which is also the default Oracle isolation level ).
-Oracle does not support the "repeatable read" isolation level in the SQL92 standard. To avoid "nonrepeatable read", you can directly use "serializable) "isolation level.
-Oracle supports the "serializable" isolation level in the SQL92 standard, but it does not really block the execution of transactions (this is further described later ).
-Oracle also adds a non-SQL92 standard read-only isolation level.

Serializable isolation level of Oracle

Serialization, as its name implies, makes concurrent transactions feel like serial execution. The reason is "feeling" is that when two transactions are concurrently executed, Oracle will not block one of the transactions to wait for another transaction to be executed and then execute, but will still allow two transactions to run concurrently, so how can we "feel" that it is serial? Please refer to the experiment.

Because user B's transactions specify the serializable isolation level, although user A submitted changes to the billing schedule before querying the billing schedule, however, because the changes committed by user A are committed only after user B's transaction starts, this change is invisible to user B's transaction. That is to say, after user B's transaction starts, changes committed by other transactions will no longer affect the query results in the transaction, in this way, user A's transaction seems to be executed after user B's transaction is completed. This is a very good feature, greatly improving the concurrency, but it will also cause problems.

　　Problem 1: Oracle's "pseudo-serial" will cause chaos in Time-Dependent programs.

　　Take a look at this example and make a slight change to the billing example.

The programmer's intention is to count the total fee for running the program from to February 3. If he thinks that the Oracle serializable will block other transactions like the lock of C #, he will be surprised at the result: From to on February 3, 4 ~ At a.m. on February 23, 100, there are actually three expense details. The total amount is 20 + 30 + 150 =, rather than the 50 calculated by user B's transaction statistics.

　　Problem 2: ORA-08177 Can't serialize access for this transaction (unable to serialize access) error.

If you use the serialize isolation level, your customers may often complain about this random error. You're not alone, bro!
There are two reasons for this error:
(1) Two transactions update the same data at the same time. You can reproduce this error as follows: Transaction B starts (using the serialize isolation level) => transaction A starts, update table 1. rowA but not submitted => transaction B updates table 1. rowA, blocked due to row locking => transaction A commit => transaction B reports A ORA-08177 error.
(2) The initrans parameter of the updated table is too small. According to the official Oracle documentation, if the serialize isolation level is used, the minimum value of the initrans parameter of the table must be set to 3 (the default value is 1 ).

Alter table expense detail table initrans 3;

Original article: "Oracle Database stores control information in each data block to manage access by concurrent transactions. therefore, if you set the transaction isolation level to SERIALIZABLE, then you must use the alter table command to set INITRANS to at least 3. this parameter causes Oracle Database to allocate sufficient storage in each block to record the history of recent transactions that accessed the block. higher values shocould be used for tables that will undergo transactions updating the same blocks."
Note that what people say is "The minimum is 3 ". The result of my 32-bit Oracle10g test in my laptop is that setting it to 3 also frequently reports ORA-08177 errors. Later it was changed to 5 and 10. If it is changed to 50, no error is reported. However, it is said that this error is random, and sometimes 3 is okay-Conversely, setting it to 50 is not necessarily safe. Ah! Really pitfall !! This is like the recipe's "adding a proper amount of oil ......", How much is his meow "moderate ?!!!
If you are interested, you can test the statements.

My suggestion is to try not to use the serialize isolation level. The user will not understand what is "unable to serialize access, he will only think that your "XX function will be randomly unavailable" is true. Later, we will briefly discuss how to avoid phantom read without the serialize isolation level. Now let's take a look at the three cases recommended by the official Oracle documents that are applicable to the serialize isolation level.

(1) With large databases and short transactions that update only a fewrows (large databases, short transactions that only update a few pieces of data)

(2) Where the chance that two concurrent transactions will modify thesame rows is relatively low (two concurrent transactions have little chance of updating the same data)

(3) Where relatively long-running transactions are primarily read only (a transaction with a relatively long running time is mainly used to read data)

How to avoid phantom read problems by using the default read committed isolation level

With the default read committed isolation level, how can I write a program to avoid phantom read problems? First, whether it is "nonrepeatable read" or "phantom read", it is because the program reads data repeatedly. So the first thing to do is to ensure that only data is read once in a transaction. It is best to use C # Instead of stored procedures to implement business logic, so that it is easy to read only once, and then store the results in IList or IDictionary. It is difficult to update data. Let's review the previous phantom read example.

Transaction B uses the same conditions for Two Queries/filtering, one for inserting summary data into the expense statement table, and one for determining the update range of the expense statement list. Between the two filters, transaction A commits A new expense detail data, resulting in inconsistent results of the two filters. To avoid this problem, we still need to implement the "read only once" principle, or, in a broader sense, "only determine the filtering range once ". There are roughly two methods.
　　<Method 1>You can read the qualified expense details and save them to a list. Statistics and updates are limited to the data in this list. The following C # code and functions are the same, but there is no phantom read problem.

// Start the transaction of user B

IList <expense details> chargeList = expense details Repository. Get the unsettled list ();
Balance = new settlement
{
Total amount = chargeList. Sum (t => t. amount ),
Settlement No. = "J122"
};
Billing Repository. Save (balance );

// At this time, user A submitted A new fee details, but it does not matter.

Foreach (charge details charge in chargeList)
{
Charge. Whether settled = 1;
Charge. Settlement No. = "J122 ";
Expense details Repository. Update (charge );
}

The disadvantage of this method is to Update each entity in chargeList once. If the data volume is large, it may cause performance problems. At this time, you can use <method 2>.
NoteThis article uses a mix of Chinese and English code for ease of expression. Do not do this in actual programming.
　　<Method 2>Use the unique method of transaction B to identify the range of operation data.

NoteAlthough it is demonstrated using SQL statements, this method can also be used using C # (entity + ORM.

Time-Dependent programs

Strictly speaking, this is not A problem caused by Phantom reads-transaction A has not yet been committed. This design is very dangerous. No matter whether read committed or serializable isolation level is used, it is not enough to avoid inconsistency caused by concurrency. Such design should be avoided as much as possible. The dependency time is dangerous because the system time may be changed by the system administrator at any time, not to mention that some countries and regions will adopt the daylight time system. After transaction B is committed, the system time was recalled for 1 hour!
However, the world is often unsatisfactory, and you may unfortunately encounter such a legacy system, or when a user has many other businesses or systems that interact with you strictly rely on time to force you to do so, what should I do?
　　<Method 1>At the business logic level, the two methods of user B and user A can be serialized using the thread synchronization technology provided by C #-theoretically upstream, but there are so many methods for detailed object operation fees, it is easy to miss something.
　　<Method 2>At the Repository level, you can set a token for the fee details object and set whether to enter the token mode. In the token mode, all persistent operations in the expense details Repository must obtain the token to perform the operation. If the token is not obtained, an exception is thrown. Business Operations usually work in non-Token mode. When user B wants to perform the settlement operation, set the transaction to the token mode immediately after the transaction starts, and then obtain the token. This ensures that only user B can perform the billing statement at this time. Although this method has poor concurrency, it is simple and safe. In addition, most settlement operations are performed only once a month (or a day), and The concurrency is tolerable. Note the following.

Although the probability of occurrence is not high, the token method is completely invalid. Considering the possibility that the system time will be changed by the Administrator, it is not enough to exclusively occupy the token in the settlement transaction, and the expense details must be Repository. verify the fee details in the Save () method. the creation time must be later than the last settlement time.