Database things Isolation Level Popular understanding

Transferred from: http://www.oschina.net/question/258230_134502

In general, there are two kinds of database things: Reading things (select), modifying Things (Update,insert). When there is no isolation control, multiple things at the same time on the same (the operation of the data may affect the results of the final expected, there are usually four cases

(1) Two updates when you modify a piece of data at the same time, it is obvious that this situation is the most serious, in any case, the program can not occur, because it will cause the loss of updates! Popular speaking, I update, you AH also update this does not have a problem, 艹, no!

(2) when one update thing updates one piece of data, another reads the update that has not yet been committed, in which case the dirty data is read. Popular speaking is, ya, let you in my update when reading, I have not submitted your AH study, deserved it, I submitted before the revised data, you ya read dirty data, deserved!

(3) when a read object reads one piece of data and another update changes the data, a non-reproducible read occurs. Popular speaking, paralysis, I read the data, who let you modified, 艹, harm I read the different data.

(4) One read thing reads, another insert thing (note here when inserted, update the same number in three when you experience) inserted a new data, so it is possible to read more than one data, a phantom read. Pop, dude, when I read the data, you plugged in the new data, right? No wonder I read one more and thought it was a winner! fuck!

The above four kinds of situation described, I believe we also found that the law, the first three is the same data on the concurrent operation, the results of the program may have a fatal impact, especially in real-time financial, such as the high accuracy of the system, do not allow the emergence of the three, compared to the fourth situation will not affect the authenticity of the data, In many cases is allowed, such as social forums and other real-time requirements of the system is not high!

In a total of four cases, we can understand roughly this simple (the free combination of the two things originally said 2*2=4):

A) modifications are allowed (missing updates)

B) allow read when modified (dirty read)

C) allow modification on read (non-repeatable read)

D) allow insertion during read (phantom Read)

From top to bottom, the problem is getting less serious, but the performance overhead required is greater. Because different systems allow different levels of the situation, there is a transaction to isolate such a thing, to allow us to set the database concurrency behavior.

which

1) READ UNCOMMITTED, this level is a thing updated when not allowed to update, but allow reading, said to not lose the update, the single will appear dirty read. The isolation level is minimal.

2) read the submitted data, is a thing update when not allowed to read, must wait until the update thing submitted to read, no dirty read, but may appear non-repeatable degree. The isolation level is lower.

The above two levels are limited to updating things, and there is no limit when reading things. So there will be non-repeatable reads (when read, there are updates), and Phantom reads (when reading, inserting).

3) reproducible reads, which are not allowed to be updated when a thing is read, but allow insertion. Non-repeatable reads do not occur, but phantom reads occur.

4) serialization (big strokes), you hemp, as long as there are things to carry out, other things must be his mother waiting for me to finish the execution. Domineering side leaks, so there is no concurrency problem.

The above two things will limit the reading of things, so the isolation level is higher, but the performance cost is not negligible.

Database things Isolation Level Popular understanding