Four types of transaction isolation levels in the database

The four levels of database isolation are as follows:

1.read-uncommitted (Read UNCOMMITTED content)

Because changes made at this isolation level even if the transaction does not commit will have an impact on other transactions. Therefore, this level will occur when the data is dirty read. Dirty reads : One transaction reads data that is written by another uncommitted parallel transaction.

Example: The boss pays the employee, the boss opens the business, then wants the employee's account to transfer the money 10000 yuan. At this point the employee opens the transaction, checking the account balance to find more than 10000 dollars, and then commits the transaction. But the boss found a lot more, then changed to 5000 yuan, then commit the transaction. The final employee account is $5000 instead of $10000, so the 10000 dollars previously seen by employees are dirty data.

2. read-committed (read submit content)

A transaction that is not committed at this isolation level will not have an impact on other transactions, and only committed transactions will affect other transactions. So it avoids reading dirty data. However, this level will occur two times when the database data is inconsistent (non-repeatable read).

non-repeatable read : A transaction re-reads the previously read data and finds that two read data is different (the data has been modified by another committed transaction).

Example: The boss gives the employee a salary. The boss opens the business, then transfers 10,000 yuan to the employee's account, and does not commit the transaction at this stage. The employee opens the transaction at this time, check the account balance, found that the account balance is 0 yuan, that is, the boss has not transferred money. Immediately after the boss commits the transaction. Then the employee felt something wrong and looked at it again (the employee's transaction has not been submitted), found that the current balance is 10,000 yuan. That is, employees under the same transaction two times the same view operation results are inconsistent.

3. Repeatable-read (Repeatable Read) at this isolation level, two transactions are performed concurrently, and one transaction modification data does not affect another transaction, even if the modified transaction has been committed and does not affect the other transaction. Therefore, the isolation level does not appear to be non-repeatable to read. However, Phantom reads appear at this level.

Phantom reads : A phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table that involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred.

For Example: at present, the score is more than 90 students have 15 people, transaction a open, and read all scores of more than 90 of the number of students, found that there are 15 people, not to submit business. At this point, transaction B opens and inserts a student record with a score of 99, and then commits the transaction. At this point, transaction a again read more than 90 students, found that the record is 16 people, more than one person. This creates a phantom read.

4.SERIERLIZED (Serializable) The transaction isolation level is the most severe, and when a query is made, a shared lock is added to the table or row, and the other transaction is read-only to the table and cannot be written.

Four types of transaction isolation levels in the database