Popular Understanding Database Isolation mechanism

===========================================

original link: Popular understanding of database isolation mechanism reproduced please specify the source!

===========================================

In understanding the database isolation mechanism, found that many articles on the web are stereotyped, explaining the language is too standard written, the description of the obscure, causal relationship blurred. Describe your understanding of the isolation mechanism here, and strive to be able to describe it in plain language.

The database isolation mechanism is for multi-threaded simultaneous operations of the database. There is no so-called isolation for a single-threaded operational database, and no isolation is required. It is important to note here that the " Operational Database " Here is to distinguish whether the operation is to read or modify existing data, or to insert and delete new data.

The database isolation mechanism four kinds:

1. Non-submitted reading (READ UNCOMMITTED)

Custom name : Modify Level Isolation (as long as the data is modified, other transactions are visible)

It can be understood that a transaction can read the modified result of a transaction as long as it modifies the data, regardless of the commit.

Potential problem " modify Operation for the same record": A transaction is not committed after modification, B transaction is read to the modified data, then the operation is modified and submitted on this basis. Then a commit fails, the data is rolled back, and the data is abnormal. This is called dirty reading .

Solution: A transaction after commit b transaction can read to a modified data, do not let read modified but uncommitted data

2. Submit Read (Committed)

Custom Name: Commit-level isolation (only other transactions will be visible if the transaction is committed successfully)

　　It can be understood that after a transaction succeeds, other transactions are able to read the latest data after a COMMIT transaction, otherwise only the original data before the A commit can be read. This solves the problem of dirty reading.

Potential problem "modify operation for the same record": A transaction opens the first time the data is read, then the B transaction turns on the data has been modified and committed successfully, at this time a transaction is not finished, and a read operation, and then found in a transaction two reads inconsistent data. This is called non-repeatable reading. the data that is read two times in the same transaction is different. "Here's a question I don't understand, why do I have to read the same record two or more times in the same transaction?" I read it once and store it in the cache. What happens when you need to read two times? What are the application scenarios? Have the understanding of friends also hope to inform! "

Solution: When a transaction operates on that row of data, other transactions are not allowed to be modified. Can be understood as adding a row-level lock

3. Repeatable READ (REPEATABLE Read)

Custom name : transaction level Isolation (the data in a transaction is not affected by the modification operation in the B transaction, even if the data queried by a transaction does not change after the B transaction commits)

It can be understood that a set of related data is replicated in its own transaction and that the data is private and unaffected by other transactions. Therefore, the problem of non-repeatable reading can be solved. This solves the problem of non-stress, but does not solve the problem of data coverage, that is, the data of the B operation is the most original data (and a unmodified before the same data), but not the latest (a modified data) data. This isolation mechanism does not resolve this issue and requires row-level locks to be used for processing.

Potential problem " insert or delete the same table ": A transaction inserts/deletes a record, but the B transaction does not query the inserted new record/still can find the deleted record. This is called Phantom reading . Phantom reads are for the number of record bars, not for the data of a particular record. You need to pay attention here!

Solution: Do not allow other transactions to manipulate the table while a transaction is operating on the table. Add table-level locks.

4. Serial Read (Serializable)

Custom name : table-level isolation (the table is not allowed to operate on multiple transactions at the same time)

This data table can be understood as: All transactions are serially manipulated. This will solve the phantom read operation.

Potential problem : The data is safe but the operational efficiency is reduced.

Solutions: .......

The four isolation mechanisms are such that the potential problems are: dirty reading, non-repeatable reading, phantom reading and inefficiency. The problems of dirty reading, phantom reading and inefficiency are all well understood, But why is it a problem to avoid repetition? Why does it take two to read the same piece of data in the same transaction? This is not quite clear ...

Do not look at the so-called "READ UNCOMMITTED", "read-commit", "repeatable read" and "serial read", which only describes a reading method, is not considered an isolation mechanism (personal understanding).

------End

Popular Understanding Database Isolation mechanism