Understanding database Transaction Isolation levels and dirty reads, non-repeatable reads, Phantom reads

Transferred from: http://blog.csdn.net/sunxing007/article/details/6427290

4 Features of database transactions:
Atomicity (atomic): both succeed or fail;
Consistency (consistency): After the transaction operation, the database is in the same state and the business rules, such as a, B account mutual transfer, the total amount of the same;
Isolation (Isolation): transactions in the operation do not affect each other;
Persistence (Durability): After a transaction commits, it is persisted to the database.

Dirty read, non-repeatable read, Phantom read
Phantom Read Phantom Read: transaction 1 reads a record when transaction 2 adds a record and commits, transaction 1 can see the new record of transaction 2 when read again;
non-repeatable reads unrepeatable read: transaction 1 reads a record, transaction 2 updates the record and commits, transaction 1 can see the modified record of transaction 2 when read again;
dirty reads dirty read: transaction 1 updated the record, but no commit, transaction 2 read the updated row, and then the transaction T1 rolled back, and now T2 read is invalid.

Transaction ISOLATION LEVEL Description:
READ UNCOMMITTED: Phantom reading, non-repeatable reading and dirty reading are allowed;
Read COMMITTED: Allow Phantom Read and non-repeatable read, but do not allow dirty reading;
REPEATABLE READ: Allows Phantom reads, but does not allow non-repeatable reads and dirty reads;
SERIALIZABLE: Phantom Reading, non-repeatable reading and dirty reading are not allowed;
The default for Oracle is READ COMMITTED.

How does perceptual understanding of these "esoteric" terms? Personally, as a Java programmer, these words are more bluffing.

First, the isolation level describes the degree to which transactions are quarantined. It is easy to imagine that a person for some reason (such as national security) needs to be quarantined, the higher the level of confidentiality involved in security, the higher the level of isolation required, the less communication he has with the outside world.

For a database, each transaction consumes resources, such as the permissions on the table/data, and the isolation level of the transaction describes how the transaction is consumed by the resource.

If the isolation level of the database is reae_uncommitted, other threads can see uncommitted data, so dirty reads occur;

If the database isolation level is set to read_committed, that is, the data that is not committed is invisible to others, it avoids dirty reads, but the data being read only obtains a read lock and is unlocked after reading, regardless of whether the current transaction has ended or not. This allows other transactions to modify the data that the transaction is reading. Causes non-repeatable reads.

repeatable Read can avoid non-repeatable reading because it locks the data being manipulated and only releases the lock until the end of the transaction;
repeatable READ only guarantees that the data being manipulated by this transaction is not modified by other transactions, but there is no guarantee that other transactions will commit new data. It is possible that thread 1 will still be able to submit new data to table T1 when the T1 of the Operation table (especially for statistical transactions), which can result in inconsistent results for 12 statistics of the thread, as in the case of hallucinations.

Serializable because a range lock is obtained, and the transaction is executed serially one after the other, it guarantees that no phantom reads occur.
Thus, the higher the isolation level, the less interference with other things, the worse the concurrency performance.

Set syntax

Oracle:

SET TRANSACTION Isolation Level serializable| READ committed| READ uncommitted| Repeatable READ;

Sybase:

SQL Opreation at Isolation Read Committed

Understanding database Transaction Isolation levels and dirty reads, non-repeatable reads, Phantom reads (GO)