High Performance Mysql_ The first chapter-mysql architecture and History

Transactions: A set of atomic SQL queries. If the database can successfully apply all the statements of the group query to the database, then the reorganization query is executed, otherwise none of the statements will be executed.

ACID: atomicity (atomocity), consistency (consistency), isolation (isolation), persistence (durability).

Atomicity: A transaction must be considered an indivisible minimum execution unit. The operation of the whole thing either commits successfully or all fails back, and there is no way to perform only part of the operation.

Consistency: The database is always moved from one consistent state to another consistent state. Even if there is a problem in the middle, because the transaction is not committed, the changes caused by the transaction are not saved to the database.

Isolation: Changes made by an office are not visible to other transactions until they are finally submitted. For example, the transfer from one account to another, from another transaction point of view is an instantaneous operation, there will be no short-term total amount mismatch problem.

Persistence: Once a transaction commits, its modifications are permanently saved in the database.

Four levels of isolation:

READ UNCOMMITTED: Uncommitted reads

Changes in the transaction, even if not committed, are visible to other transactions.

Dirty reads: Transactions can read uncommitted data.

Read COMMITTED: Submit read

The default isolation level for most database systems (MySQL is not), when a transaction starts, you can only "see" the changes that have been made to the transaction that was committed. can also be called non-repeatable read (nonrepeatable read), because the same query can be executed twice, the data may be different.

REPEATABLE READ: Repeatable reading

MySQL default isolation level. Resolves the problem of dirty reads, while ensuring that the same record results are read multiple times in the same transaction. However, it may result in a phantom read (Phantom read), when a transaction reads a range of records, another transaction inserts a new record within that range, and a magic line is generated when the previous transaction reads the record for that range again.

SERIALIZABLE: Serializable

The highest isolation level. Forced transaction serial execution avoids the occurrence of phantom reading. The equivalent of locking on every row of data leads to a large number of timeouts and lock contention issues that are rarely used in real-world development.

Deadlock: Two or more transactions compete against each other on the same resource, and request a lock on each other's resources, leading to a vicious cycle.

Example:

Transaction 1:

Start transaction;

Update StockPrice set ... where id = 3;

Update StockPrice set ... where id = 4;

Commit

Transaction 2:

Start transaction;

Update StockPrice set ... where id = 4;

Update StockPrice set ... where id = 3;

Commit

Resolve deadlock: Deadlock detection, deadlock timeout mechanism.

High Performance Mysql_ The first chapter-mysql architecture and History