mySQL transaction and its characteristic analysis

Transactions

The core of a transaction is its atomicity. In computers, we make indivisible things atomic, and transactions are indivisible.

We often encounter the following situations:

A transfers $100 to B

The corresponding B will receive 100 yuan from A

These two things must either fail or complete. They are inseparable. If there is a sudden problem with the server after the transfer is successful, B does not receive 100 yuan, which will cause a big problem.The transaction provides a roll back operation for this situation, so that the data is returned before the transfer, which ensures the "prosperity of the data, and the loss of the data".

In addition to its core atomicity, transactions have three other properties:

1. Persistence: that is, all changes made by the transaction will be stored in the hard disk and will not be changed due to server shutdown or restart
2. Consistency: that is, when a transaction changes from one consistent state to another consistent state, "consistent" means that the data can be correct
3. Isolation: In parallel development, transactions can be isolated and not interfere with each other.

Persistence is no longer described in detail.

Consistency

It is still the previous example of transferring money. If A transfers successfully but B does not receive the money, this is a transition from a consistent state to an inconsistent state.We can find that atomicity and consistency are closely linked.

Isolation

Although the definition of isolation is simple, it can be further studied according to the level of isolation.

1. Dirty read problem

   A and B both process an account at the same time, if A sees that the account has 100, and then B adds 100 to the account, then dirty happensRead problem, that is, A read wrong data.In order to avoid this situation, the database software provides a lock mechanism. As long as B is modified to prohibit A from reading the data, the dirty read problem is avoided, that is, the write operation is locked, which reduces the concurrency, but slightly improves the accuracy of the result.Sex

2. The problem of non-repeatable reading

   A and B are the same. If A is reading data, B suddenly modifies the data, which will cause A to read it again after reading it.The data was found to have changed, causing non-repeatability issues.For this problem, we can lock the read operation, that is, prohibit writing data when reading data.Doing this in turn reduces concurrency and further improves data accuracy.

3. The problem of phantom reading

   It is still A and B. When A is reading data, B wants to add data, but what B wants to modify or add at this time is another file, which will not affectA is reading the data.In this way, although A reads the data multiple times and has no effect, there will be a situation where one more file is found while reading, which seems to have an illusion, but in most cases, this situation has no effect.To avoid the phantom read problem, parallel transactions must be serialized, that is, B cannot do it while A is doing it, and vice versa.This approach maximizes data accuracy while minimizing parallelism.

Database isolation mechanism

For different problems, mySQL provides four isolation levels:

1. read uncommitted allows reading uncommitted data, that is, it is completely released, A can do whatever he wants, B can do whatever he wants, the highest degree of parallelism, and the lowest data accuracy
2. read committed can only read the data after the commit, that is, lock the write operation to avoid the problem of dirty reading
3. repeatable read locks read and write operations to avoid dirty and non-repeatable reads
4. serializable is serialization, which maximizes data accuracy and minimizes parallelization, while avoiding dirty reads, non-repeatable reads, and phantom reads.

For different scenarios, we can choose different levels according to our needs. For example, the B-site like function does not need such high accuracy, and the lowest level restriction can be used, but for the transfer problem, a higher level of isolation means must be used..mySQL is the third level by default. If you need to modify it, you need to modify it in the configuration file.