Oracle Database isolation level learning

Oracle transaction isolation level

Situations caused by different transactions:
Dirty read (Dirty reads)
A dirty read occurs when one transaction reads the changes that have not been committed by another transaction.
Many databases allow dirty reads to avoid competition from the exclusive locks.
Nonrepeatable reads)
The same query is performed multiple times in the same transaction. Because of modifications or deletions made by other commit firms, different result sets are returned each time, and non-repeated reads occur.
Phantom reads)
The same query is performed multiple times in the same transaction. Because of the insert operations performed by other commit firms, different result sets are returned each time, and phantom reading occurs.

Database Operation isolation level

Read uncommitted)
Read committed)
Repeatable read)
Serializable)
Oracle default isolation level read committed (statement level serialization)
Each statement obtains a data snapshot at the beginning of the statement.
A transaction has multiple statements. If there are other completed transactions between the statements, this may cause unsustainable read and phantom read.

Create a new test table books:

Name, code, and price Fields

Add two test data items

Simulate concurrency using pl/SQL and java programs
Dirty read tests are not allowed:
The program section first queries the price of the book whose code is qqq

Copy codeThe Code is as follows:
// Get the connection omitted
Pstat = conn. prepareStatement ("select price from books where code = 'qqq '");
Rs = pstat.exe cuteQuery ();
While (rs. next ()){
System. out. println ("price:" + rs. getDouble (1 ));
}
Close ();

Output result: price: 15.0
Then pl/SQL executes updates

Copy codeThe Code is as follows:
Update books set price = 18 where code = 'qqq ';

! Pl/SQL is set to manual update, not automatically updated
Execute the preceding java query code
The output is still price: 15.0, indicating that the uncommitted execution results in pl/SQL cannot be read, that is, dirty reading is not allowed.
Pl/SQL Execution
Commit;

Perform a java query:
Output result: price: 18.0

There will be a phenomenon of unrepeatable reading and phantom reading, so we don't need to test it,
These two phenomena are caused by the read of the submitted transaction. The read commited isolation level allows
.

Isolation level: repeatable read)
Dirty reads are not allowed and repeated reads are not allowed, but Phantom reads may occur.
This oracle does not support and cannot be tested.
If there are other things being updated in the content of a query statement
The query is in the waiting state. This query will not be executed until the previous transaction is submitted for update. That is
There will also be a lock when querying, and you need to wait for concurrent transactions to release the lock. Then, the lock is obtained and executed.
Yourself. In this way, the query is also locked, and the concurrency is lower.
Select... for update. This prevents repeated reads.

Isolation level: serializable
This is more strict, and things are executed one by one. A statement in a transaction shares the same data snapshot (data that exists at the beginning of the transaction ).
It is at the transaction level, dirty reading, and non-repeated reading. Phantom reading has no chance at all.
Similar to read committed, the preceding statements are statement-level.
For example
Read committed A transaction a has three statements: A (select), B (select), and c (update ).

When things A and B are executed, it is possible that things B are updated.
Because a and B are unlocked

Example:

Copy codeThe Code is as follows:
// Get and close connection Code omitted
// Not automatically submitted
Conn. setAutoCommit (false );
/**
* A Query
*/
Pstat = conn. prepareStatement ("select price from books where code = 'qqq '");
Rs = pstat.exe cuteQuery ();
While (rs. next ()){
// Output price: 25.0
System. out. println ("price:" + rs. getDouble (1 ));
}
Close ();

/**
* Pause for a moment and run the B transaction with pl/SQL
* Update books set price = 15 where code = 'qqq ';
* Commit;
*/
Thread. sleep (10000 );

/**
* If you execute the query again, it is different from the first query result because there is a commit update for transaction B in the middle.
* Modification, which is also not readable
*/

// Update B
Pstat = conn. prepareStatement ("update books set price = price + 10 where code = 'qqq '");
Pstat.exe cuteUpdate ();
Close ();
// C Query
Pstat = conn. prepareStatement ("select price from books where code = 'qqq '");
Rs = pstat.exe cuteQuery ();
While (rs. next ()){
// The output is still price: 25.0 because of the intervention of transaction B.
System. out. println ("price:" + rs. getDouble (1 ));
}
Close ();
// Submit the transaction
Conn. commit ();
If (conn! = Null ){
Conn. close ();
}

In the execution order above, transaction B is executed in the execution process of.

The above introduces the oracle database isolation level through examples, hoping to help you.

Below are some supplements:

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

This isolation level means that a transaction can only read committed data, but supports Repeatable read and fantasy read. This 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. REPEATABLE READ
This isolation level does not support dirty reads, repeatable reads, and fantasy reads. It is mainly used to obtain consistent answers and prevent missing updates.

A. Get consistent answers
In oracle, this is implemented through the Multi-version mechanism, but other databases need to be controlled through the locking mechanism. In the above 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 Transfers 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. Update loss
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

Dirty reads, repeated reads, and fantasy reads are not allowed, 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.