Summary of the isolation level of transactions in the database

Overview

This article summarizes the content of the following three posts:

The http://baike.baidu.com/view/2943500.htm's description of update loss is not in place and the instant shared read lock and shared read lock are incorrectly used in solution ".

The http://blog.csdn.net/w_l_j/article/details/7354530 did not mention "Update loss" and did not mention it in read uncommitted to ensure that there is no update loss. Translation at the isolation level is more intuitive than traditional translation

Http://en.wikipedia.org/wiki/Isolation_ (database_systems) English wiki, always accurate and reliable. The Chinese version of isolation levels vs locks is incorrect. I tried to change it.

Http://docs.jboss.org/jbosscache/1.4.0/TreeCache/en/html/transactions.html 5.1.2.1. Isolation levels write is simple, intuitive, in place. Severe recommendation.

Issues to be resolved:

For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not adopted, various concurrency problems will occur:

• Update loss(Lost update): For two transactions that concurrently update the same record T1, T2, T1 are updated first, T2 is then updated, but then rolled back for some reason, the data is returned to the status before t1 and t2 operations. Because no exclusive write lock is introduced, the content written in T1 is lost.
• Dirty read(Dirty reads): For two things T1, T2, T1 read fields that have been updated by T2 but have not been submitted. if T2 is rolled back, the content read by T1 is temporary and invalid.
• Non-repeated read(Non-repeatable reads ):

1. For the two things T1, T2, T1, a field is read, and then T2 updates the field. After T1 reads the same field again, the value is different.
2. Phantom read(Phantom reads): For two things T1, T2, T1 read a field from a table, and T2 inserts some new rows in the table. then, if T1 reads the same table again, there will be several more rows.

Solution:

For the above four situations, four isolation levels are designed:

Isolation level	Update loss	Dirty read	Non-repeated read	Phantom read
Read uncommitted	-	Possible	Possible	Possible
Read committed	-	-	Possible	Possible
Repeatable read	-	-	-	Possible
Anomaly serializable	-	-	-	-

Read uncommitted (allow read of uncommitted data): allows the transaction to read changes not committed by other transactions, dirty read, non-repeated read, and phantom read problems will occur.
Read commited (only read committed data): only allows the transaction to read the changes that have been committed by other transactions, which can avoid dirty reads, but the unrepeatable read and phantom read problems still occur.
Repeatable read (the same data reread in the transaction remains unchanged): ensure that the transaction can read the same value from a field multiple times, during this transaction duration, other transactions are prohibited from updating this field to avoid dirty reads and repeated reads. However, phantom read problems still exist.
Serializable: ensures that the transaction can read the same row from a table. During this transaction, other transactions are prohibited from performing insert, update, and delete operations on the table, all concurrency problems can be avoided, but the performance is very low. Implementation of each isolation level:

Isolation level	Write lock	Read lock	Range lock
Read uncommitted	V	-	-
Read commited	V	S	-
Repeatable read	V	V	-
Serializable	V	V	V

"V" exclusive lock. The lock continues until the transaction ends.

"S" shared lock. Write locks are usually exclusive. Otherwise, the lowest transaction isolation level cannot be guaranteed, and the loss of update (lost update) may occur. There are two types of read locks: Shared read locks and exclusive read locks. Both of them can be obtained only after the write locks in the corresponding range are released to avoid reading dirty data. The difference is that the former is released only after the read is complete, you do not need to wait until the transaction ends, and the transaction can be held by multiple read Operations (that is, shared) at the same time ). Obviously, the locking granularity of the former is smaller, so the performance is better.

Oracle and MySQL support transaction isolation level

Two transaction isolation levels supported by Oracle: Read commited and serializable. The default transaction isolation level of Oracle is read commited.
MySQL supports the transaction isolation level in MySQL 4. The default transaction isolation level of MySQL is Repeatable read.