The impact of database isolation and isolation levels and isolation levels

Isolation: Personal understanding is to ensure that two simultaneous transactions do not feel the other side of the existence of each other, do not affect each other. And the other three principles (atomicity, consistency, persistence) are the principles that need to be noted for data manipulation. In order to meet the isolation, the auxiliary proposed 4 isolation levels, of course, the more restrictive the efficiency of the lower.

Isolation level:

1) READ UNCOMMITTED reads uncommitted data, that is, a transaction can see data that has been added and reduced but not committed by the other transaction.

A transaction can read the data that the B transaction has been added to, but if the failure occurs after the B transaction the rollback a transaction reads the dirty data that is rolled back so it is often called "dirty reading" on the internet.

2) Read Committed reads the submitted data, that is, a transaction can only see data submitted by other transactions.

A transaction does not read data that has been added to the B transaction but not committed by the transaction, so that even if B rolls back a does not read dirty data, the data is at least right.

However, before the B transaction is submitted before a, a transaction needs to be read repeatedly two times, the first read to the data before transaction B is not submitted, the second time read the data submitted by B, resulting in two read data inconsistency, which may affect the business of a.

3) REPEATABLE Read repeatable reading

A transaction reads data after the B transaction adds and subtract the data and commits the transaction, a transaction reads again or reads the value before the B transaction is not added, that is, the a transaction ignores the effect of the B transaction on the data before it is committed, and only when the a transaction commits can you see the data modification of the B transaction.

4) Serializable serializable

Forcing a sort transaction does not allow the transaction to cross-update the same piece of data.

is to use shared locks on the line to prevent dirty reads, repeated reads, and Phantom reads, which in extreme cases can cause a lot of timeouts and lock-up competitions.

Ask yourself a self-answer:

1) What is the difference between repeated reading and phantom reading?

Repeated reading refers to the fact that the data submitted by B has been read and read to B uncommitted data inconsistency.

Repeatable read level in order to resolve the data inconsistency caused by repeated reads above, and forge the consistency, resulting in the last operation of appending the same piece of data will cause problems.

After practice: found that if the B transaction inserts a primary key 8 data and commits, a query still does not see 8 (if a after the B transaction is submitted before the query) a insert data will fail, the query or not see. A go directly with the new 8 will be updated successfully, and 8 since has been found a re-query can be seen.

The data inserted on a B is inserted before B is committed and the same data is suspended.

2) What is the problem with phantom reading?

A data inconsistency may occur during an append operation because an update to another transaction could not be read.

3) What is a shared lock for a database?

Shared locks are also called write locks.

Answer tomorrow.

Summary from: https://www.cnblogs.com/snsdzjlz320/p/5761387.html Few of the more clearly written posts

The impact of database isolation and isolation levels and isolation levels