MySQL database optimization (2)—MySQL transactions

1. ACID
Atomicity
Atomicity: A transaction is regarded as an indivisible minimum unit of work. The entire transaction is either Execute all, or roll back if all fail.
Consistency
Consistency: The database always transitions from one consistent state to another consistent state. For example, even if the money withdrawal operation fails, because the transaction has not been committed, the modifications made by the transaction will not be saved in the database, and the data will still be in the state before the transaction was executed. If the transaction is executed successfully, the data will remain unchanged after execution.
Isolation
Isolation: Modifications made by one transaction are not visible to other transactions before they are submitted.
Durability
Durability: Once a transaction is committed, its modifications will be permanently saved in the database.

Note

Just as the lock granularity upgrade will increase system overhead, the transaction processing process also requires the database system to do more additional work. A DB that supports ACID usually requires higher CPU processing power, memory and disk space than a database that does not need to support ACID. Therefore, users can choose MySQL's various database engines through different application scenarios and be flexible.

2. Isolation Level
There are 4 isolation levels defined in the SQL standard. Each level of transaction isolation corresponds to the visibility of modifications made by the transaction within and between transactions. The lower the isolation level, the stronger the concurrency and the lower the system overhead.
Read Uncommitted
Uncommitted read: For transactions at this isolation level, during the data modification process, other transactions can read the data even if it is not committed. Transactions that can read uncommitted data are also called dirty reads. Since dirty reads can cause many problems in actual applications, this isolation level is generally rarely used.
Read Committed
Read Committed: (General database default transaction level) Only after the current transaction is executed and the data is submitted, other transactions can read the data. It is also called non-repeatable reading, because two different queries executed by other transactions may result in two different results (read once before the transaction is executed and once after the transaction is executed).
Repeatable Read
Repeatable Read: (Mysql’s default isolation level) solves the problem of dirty reads. This level ensures that the results of multiple reads are consistent in the same transaction. . But it still cannot solve the phantom reading problem. Phantom read: When transaction A reads data in a certain range, transaction B performs insert and other update operations on the range of data. When transaction A reads records in the range again, a phantom read will occur.
Serializable

Serializable: When reading each row of data, a lock is added to force the transaction to be executed serially to avoid phantom reading problems. But it is prone to timeout and lock competition problems. There are also relatively few applications, and this level is only used when data consistency and no concurrency are particularly needed.

3. Mysql sets transaction isolation level

set transaction isolation level read committed;
New isolation The level will take effect when the next transaction starts executing. If you need to modify the isolation level of the current transaction, you can modify the configuration file. In addition, in the previous blog, two storage engines that support transactions provided by mysql were introduced: InnoDB and NDB Cluster.

The above is the content of MySQL database optimization (2) - MySQL transactions. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!