Oracle database Isolation Level learning _oracle

Oracle Transaction ISOLATION LEVEL

Status caused by different transactions:
Dirty Read (Dirty reads)
A transaction reads a dirty read when another transaction has not committed a change
Many databases allow dirty reads to avoid the contention of exclusive locking.
Non-repeatable read (nonrepeatable reads)
The same query takes place more than once in the same transaction, and because of changes or deletions made by other submitting transactions, a different result set is returned each time, and a non duplicate read occurs.
Phantom Reading (Phantom reads)
The same query takes place more than once in the same transaction, and a phantom read occurs each time a different result set is returned due to an insert operation done by another submission transaction.

Isolation level for database operations

Uncommitted read (READ UNCOMMITTED)
Submit read (Read Committed)
Repeat read (Repeatable read)
Serialization (Serializable)
Oracle Default Isolation Levels Read Committed (statement level serialization)
Each statement, at the beginning of the statement, gets a snapshot of the data at the moment.
A transaction has multiple statements, which can cause unsustainable read and Phantom reads if there are other completed transactions between statements.

Create a new test table books:

Name,code,price three fields

Add two test data

Using Pl/sql and Java programs to simulate concurrency
Dirty read test not allowed:
The program section first queries the price of the book Code is QQQ

Copy Code code as follows:

Get connection Ellipsis
PStat = Conn.preparestatement ("Select Price" where code= ' QQQ ');
rs = Pstat.executequery ();
while (Rs.next ()) {
System.out.println ("Price:" +rs.getdouble (1));
}
Close ();

Output Result: price:15.0
Then pl/sql to perform the update

Copy Code code as follows:

Update books set price=18 where code= ' QQQ ';

！ Pl/sql set to Manual update, not automatically updated
In the execution of the Java query code above
Output is still price:15.0, indicating that no uncommitted execution results are read in Pl/sql, that is, dirty reads are not allowed
Pl/sql execution
Commit

In executing a Java query:
Output Result: price:18.0

There will be no repetition to read what the phenomenon of phantom reading occurs without testing it,
Both of these phenomena are caused by the reading of things after submission, read Commited isolation level is allowed to submit after
Things to read.

Isolation level: Repeat read (repeatable read)
This does not allow dirty reads and cannot be read over and over again, but there is a phenomenon of phantom reading.
This Oracle does not support, bad test.
The thing to understand is that if something else is being updated on the contents of a query, this
The query is in a waiting state until the previous thing commits an update before the query is executed. Is
The query will also have locks, need to wait for concurrent things to release the lock. Then get to the lock yourself, execute
Own things. This query is also locked and has a lower concurrency
Select ... for update this is the way to avoid the occurrence of non repeatable reads

Isolation level: Serializable
This is more stringent, the implementation of things is one. Statements in one transaction share the same data snapshot (data that exists at the start of the transaction).
Is the level of things, dirty read, do not repeat read, there is no chance of phantom reading happen.
The front like Read committed are statement-level, with statements as cells.
Like what
Read Committed one thing A has a (select), B (select), C (UPDATE) three statements

When a thing executes a,b, it is possible to have B thing perform the update operation.
Because A,b is not locked.

Example:

Copy Code code as follows:

Get connection and close connection code omitted
Do not submit automatically
Conn.setautocommit (FALSE);
/**
* A query
*/
PStat = Conn.preparestatement ("Select Price" where code= ' QQQ ');
rs = Pstat.executequery ();
while (Rs.next ()) {
Output price:25.0
System.out.println ("Price:" +rs.getdouble (1));
}
Close ();

/**
* Suspend for a while, execute B transaction with Pl/sql
* Update books set price=15 where code= ' QQQ ';
* COMMIT;
*/
Thread.Sleep (10000);

/**
* If a query is executed here, the results of the first query will be different, because there is a commit update of B transaction in the middle
* Modified, this is also not repeatable read
*/

b Update
PStat = conn.preparestatement ("Update books set price=price+10 where code= ' QQQ '");
Pstat.executeupdate ();
Close ();
C Query
PStat = Conn.preparestatement ("Select Price" where code= ' QQQ ');
rs = Pstat.executequery ();
while (Rs.next ()) {
Output or price:25.0, because the intervention of the B transaction
System.out.println ("Price:" +rs.getdouble (1));
}
Close ();
Commit a transaction
Conn.commit ();
IF (conn!= null) {
Conn.close ();
}

The order in which transaction B is executed during the execution of a.

The above example introduces the Oracle database isolation level related content, hope to be helpful to everyone.

Here are some additions:

Transaction isolation levels define how "sensitive" a transaction is to the modification of another transaction, the basic role of transactions in the database is to transform the database from a consistent state to another consistent state. That is, different isolation levels define the degree to which transactions affect each other, and below are several separate levels of isolation.

1. READ UNCOMMITTED

In fact, Oracle does not support this isolation level. This isolation level allows dirty reads (that is, data that is not committed by the user), and databases that support this isolation level are intended primarily to support non-blocking reads, but Oracle supports non-blocking reads by default, so this isolation level is not supported in Oracle. Here's an example:

As shown in the figure above, suppose a bank has to count all the amounts in total. Transaction A is responsible for statistics, and transaction a reads from the first line. If read to 100 lines, transaction b from account 123 to 400 yuan to account 987 (transaction B has not yet committed), support dirty read database when transaction a reads to 342023 lines, it will get 500 yuan, thereby adding 400 yuan.

2. READ committed

This isolation level means that transactions can only read data that has already been committed (but support for repeatable read and fantasy reads) is the default isolation mode for the Oracle database. In fact, this isolation level in other databases may still be "degraded" like dirty reading. Just look at the previous example and assume that transaction B locks up the line early and changes the amount from 100 to 500 before transaction a reads 342023 rows. When transaction a reads to this line, it finds that it has been locked by another transaction and waits until transaction B commits. But when transaction B is committed, transaction a reads the 500 error message, which is the same as dirty reading, and lets the user wait for the wrong answer.

3. Repeatable READ
This isolation level does not support dirty reads, does not support repeatable reads, and supports fantasy reading. The main purpose is to get consistent answers and prevent lost updates.

A. Getting consistent answers
In Oracle this is implemented through the multiple versioning mechanism, but in other databases need to be controlled by the locking mechanism, as an example of the above example, how to calculate the correct total amount, transaction A in reading each row, with each row with shared read lock, This is when transaction B executes from account 123 to 400 yuan to account 987. First line of operation the account 123 is modified from 500 to 100, but the first row is locked by transaction A and waits so that transaction a can read the correct data. However, if transaction B is performed from account 987 to 50 yuan to account 123, transaction B first operation Line No. 342023, found not locked, so the lock will amount from 100 to 50, then the first line of operation, found locked and then wait. When transaction a reads to line 342023, it finds that the line has been locked by transaction B and waits, thus falling into deadlock.

B. Missing updates
In a database with shared read locks, this isolation level prevents the loss of updates, such as transaction 1, which reads first A and then modifies the C column of the row (the other columns are modified only to the same value as before, because the programmer is an entire row of updates). At this point, transaction 2 would like to also want to modify the line a will be blocked, prevent the update of transaction 1 is overwritten.

4. seaializable

Dirty reads, repeated reads, and fantasy reads are not allowed, the highest level of isolation. This isolation level indicates that transaction a operates on the database as if only transaction a is operating, and no other transactions are operating in the database.
This is how Oracle implements SERIALIZABLE transactions: Read consistency, which is usually obtained at the statement level, can now be extended to the transaction level. That is, at the moment of the execution of the transaction, take a picture of the data that the transaction will operate on.

From the above example, we can see that the other databases using shared read locks to solve the total statistical amount problem is not the Oracle multi-version mechanism is flexible, which seriously affected the concurrency of the program, read blocking write. The second may cause a deadlock.