Database Isolation Level

First, meaning

In a database management system, transactions need to meet acid four features.

Atomicity, or atomicity, indicates that all operations within a transaction are either done entirely or not.

Consistency, which is consistent, indicates that a transaction can only cause a database to jump from one consistent state to another consistent state, and cannot break a constraint such as a primary key unique or all the values of a column are fixed values (the transfer cannot affect the total deposit).

Isolation, or isolation, means that two transactions do not affect each other, just as the two transactions are serial in general.

Durability, or persistence, means that if a transaction succeeds, its impact is persistent and not lost. Persistence is guaranteed by persisting data to disk, of course, if the disk is broken down and the data is lost, multiple replicas can greatly avoid data loss and enable reliability to reach more than 9 levels.

A database often requires concurrent processing of multiple transactions concurrently, allowing all related transactions to be serially integrated to ensure acid performance, but this will be relatively low and the database system can be said to be basically unusable. Therefore, in order to improve concurrency, we need to reduce the requirement of isolation. Depending on your business needs, we need to trade off between the concurrency and isolation levels, and a common DBMS will typically provide multiple isolation levels for users to choose from.

Ii. Abnormal Phenomena 2.1 dirty write (Dirty Write)

There are two transactions T1 and T2,T1 have changed a column x, before T1 commit, T2 also changed x, this is dirty write, because T1 has not committed, T2 changed the middle state of T1. If now T2 submitted, T1 to rollback, if the rollback to T1 before the state, the T2 has been committed to all lost, if not rolled back to the state before the beginning of the T1, already roll the impact of the T1 is still in the database. The database that allows this behavior is basically unavailable because it can't finish the transaction roll back.

2.2 Dirty Reads (dirty read)

There are two transaction T1 and T2,T1 changes to change column x from 0 to 5, before T1 commit, T2 read X to read, read to T1 in the middle state x = 5, assuming that the final T1 rollback, and T2 in accordance with the T1 of the middle state x = 5 Do some operations, and eventually there will be some unreasonable results.

2.3 Non-repeatable read (fuzzy read)

There are two transactions T1 and T2,T1 read x First, then T2 changed x, then commit, then if T1 read X again, then the result of two reads in a transaction is different.

2.4 Phantom Read (Phantom Read)

There are two transactions T1 and T2,T1 according to a condition from the table test select satisfies the condition of the row, and then T2 to table test insert satisfies the condition row or update does not meet the criteria to satisfy the condition and then commit, At this point, if T1 again through the condition Select, then there will be a transaction within two times the result of a condition select is not the same.

2.5 Missing updates (loss of update)

There are two transactions T1 and T2,T1 read x First, read x = 0, then T2 read x, read x = 0, then T1 x plus 3, then commit, then T1 x plus 4, then commit. Thus, the value of x is eventually lost for 4,t1, and if T1 and T2 are serial, the final result will be 7.

2.6 reading Skew (read skew)

If you need to satisfy the constraint x + y = 10, initial x = 5, y = 5, the transaction T1 first read to x = 5, then transaction T2 change X to 4,y to 6, then commit, transaction T1 then read to Y = 6, at which point the transaction T1 see X + y = 11 does not satisfy the constraint x + y = 10.

2.7 Writing skew (write skew)

If you need to satisfy the constraint x + y >= 0, the initial x = -3, y = 5, the transaction T1 read X and y, then the transaction T2 read X and y, then the transaction T2 change Y to 3, then commit, then the transaction T1 change X to-5, and then commit. Finally, x = -5,y = 3 does not satisfy the constraint x + y >= 0.

Third, lock-based isolation level

Locks are categorized by range, mode, duration. The predicate lock, which can be divided into a predicate lock (predicate) and a row lock (item), locks all rows that satisfy a condition (including non-existent rows that satisfy the condition), and the row lock locks only a particular row. According to the pattern can be divided into reading lock (read) and write lock (write), read lock and read lock can coexist, read lock and write lock mutex, write lock and write lock mutex (read lock and write lock priority, write lock can preempt read lock). According to the duration can be divided into short lock (shorter) and long lock (long), short lock means when the data to access the lock, read and write immediately after the unlock, long lock means when the data to access the lock, until the end of the transaction unlocked. A two-phase lock (Tow-phase Lock) indicates that the required read and write locks are added at the beginning of the transaction, and all locks added at the end of the transaction are freed once. A two-phase lock enables serializable isolation levels. The lock-based isolation level is shown in the following table:

• Degree 0 allows dirty write
• READ UNCOMMITTED allow dirty read
• Read commited circumvented the dirty read/write, allowing the fuzzy read, loss of update, read skew, and write skew
• Cursor stability circumvented loss of update
• REPEATABLE Read circumvents dirty read/write, fuzzy Read, loss of update, read skew, and write skew, but there is a phantom read
• Serailizable avoided all anomalies.

Iv. Snapshot Isolation

MVCC maintains a number of versions of the data, the transaction will be opened when the transaction is given a version number Starttimestamp, the transaction read transactions will read the version number is not starttimestamp data, the read operation will not block, the advantage is that read and write non-impact can be executed concurrently. When a transaction is to be committed, the database assigns a committimestamp to the transaction, if there are other transactions tother the Committimestampother in the range [Starttimestamp, Committimestamp] And the Tother transaction modifies the same row of data as the transaction to be committed, the transaction is dropped, which is called the First-commiter-win principle (that is, optimistic locking). The First-commiter-win principle guarantees that dirty write and loss of update will not appear, and that the data read within a transaction is the same version of data, guaranteeing that there will be no such phenomena as Fuzzy Read, Phantom reading, and read skew. But it allows write skew. Can see snapshot isolation and repeatable read strict degree is unable to compare.

Resources:

"A Critique of ANSI SQL isolation Levels"

　　

Database Isolation Level