I. Knowledge structure brain map

Below is a mental map of my total knowledge of a transaction, the main content of a transaction, its characteristics, implementation, and isolation level

The basic properties of transactions (ACID)

Definition:

  1. Atomicity: After a transaction starts, all operations are either done or not done. It is impossible to stop in the middle. If an error occurs during transaction execution, it will be rolled back to the state before the transaction began, and all operations will appear as if they did not happen. In other words, the transaction is an indivisible whole, just like the atom in chemistry, which is the basic unit of matter.

  2. Consistency: The integrity constraints of the database are not broken before and after a transaction. Including consistent read and consistent write. For example, if A transfers money to B, it is impossible for A to withhold money and B does not receive it.

  3. Isolation: Only one transaction is allowed to request the same data at a time, and different transactions do not interfere with each other. For example, if A is withdrawing money from A bank card, B cannot transfer money to the card until the withdrawal process is complete.

  4. Durability: After a transaction completes, all updates made by a transaction to the database are saved to the database and cannot be rolled back.

Objective:

  • Provides a way for the database to recover from a failure to a normal state, while providing a way for the database to remain consistent in an abnormal state

  • When multiple applications concurrently access the database, they can be provided with a means of isolation between these applications to prevent their operations from interfering with each other

In case of success:

  • The ability to change data from one state to another and persist

Under abnormal circumstances:

  • Data can be restored to the normal state
  • Be able to maintain consistency, including consistency of data and consistency of constraints

Concurrent case:

  • Concurrent operations do not affect each other

Conclusion: The purpose of a transaction is to provide three methods: failure recovery method, consistency method, and operation isolation method

Third, the concurrency of transactions

  1. Dirty read: Transaction A reads the data updated by transaction B, and then TRANSACTION B rolls back the data
  2. Non-repeatable read: When transaction A reads the same data for many times, transaction B updates and commits the data during the process of reading the same data for many times. As A result, when transaction A reads the same data for many times, the results are inconsistent.
  3. Phantom reads: system administrators, A database of all the grades of the students from the specific scores to ABCDE level, but the system administrator B at this time by inserting A specific score record, when A system administrator A change after the found there is no change to come over, A record like the illusion, this is called magic to read.

The difference between unrepeatable and phantom reads

Many people tend to confuse unrepeatable reading with phantom reading, and there are similarities. But non-repeatable reads focus on UPDATE and DELETE, while phantom reads focus on INSERT. If these two isolation levels are implemented using a locking mechanism, in repeatable reads, the SQL locks the data after the first read, and the data cannot be modified by other transactions to achieve repeatable reads. However, this method does not lock the insert data, so if transaction A has previously read the data, or modified the whole data, transaction B can still insert the data commit, then transaction A will find that there is no reason for the extra data, this is A phantom read, cannot be avoided by row locking. Serializable isolation level is required, read with read lock, write with write lock, read lock and write lock mutually exclusive, this can effectively avoid magic read, unrepeatable read, dirty read and other problems, but will greatly reduce the concurrency of the database.

MySQL transaction isolation level

Problems addressed by different isolation levels:

Transaction isolation level Dirty read Unrepeatable read Phantom read
Read Uncommitted (read-uncommitted) Not solved Not solved Not solved
Read Committed (read-committed) To solve Not solved Not solved
Repeatable-Read To solve To solve Not solved
Serializable To solve To solve To solve
The lower the isolation level, the fewer locks a transaction request has or the shorter the time the lock is held. This is why the isolation level of most databases is READ COMMITTED. The default isolation level of MySQL is Repeatable- READ.
MySQL8.0 transaction isolation level is REPEATABLE-READ

【 reference 】 【 1 】 www.cnblogs.com/huanongying… [2] MySQL Technology Insider InnoDB Storage Engine