In-depth understanding of MySQL advanced drifting (5)

Transaction

What is a transaction

A transaction consists of one or more SQL statements as a separate unit. Each SQL statement in this unit is interdependent, and the unit as a whole is indivisible. If a statement in the unit cannot complete successfully, the entire unit will be rolled back and all affected data will be returned to the state before the transaction started. Therefore, only when all statements in the transaction are successfully executed can the transaction be said to be successfully executed.

Four characteristics of transactions:

Atomicity, each transaction must be considered an indivisible unit.

Consistency (Consistency), regardless of whether the transaction is completely successful or fails halfway, consistency exists when the transaction puts the system in a consistent state.

Isolation (Isolation), each transaction occurs in its own space, isolated from other transactions that occur in the system, and the results of the transaction can only be seen when it is completely executed.

Durability (Durability), even if the system crashes, a committed transaction is thrown in and persists.

Life cycle

In order to initialize a transaction and tell MySQL that all subsequent SQL statements need to be considered a unit, MySQL provides the start transaction command to mark the start of a transaction. You can also use the begin or begin work command to initialize a transaction. Normally, the start transaction command is followed by the SQL statements that make up the transaction.

Once the sql statement is executed, you can use the commit command to save the entire transaction on disk, or the rollback command to undo all changes. If the transaction includes changes to transaction tables and non-transaction tables, the transaction processing part of the non-transaction tables cannot be undone using the rollback command. In this case, MySQL will return an error indicating that an incomplete undo occurred.
The commit command marks the end of the transaction block.

Controlling transaction behavior

MySQL provides two variables to control transaction behavior: autocommit variables and transaction isolation level variables.
Automatic submission. By default, once MySQL's sql query is executed, the results will be automatically submitted to the database. This default behavior can be modified through the specific autocommit variable. With set autocommit=0, subsequent table updates will not be saved until a commit command is explicitly issued.

Transaction isolation level, MySQL defaults to repeatable read isolation level, which can be modified using set.

Transactions and Performance

Because databases that support transactions are more difficult than non-transactional databases to keep different users isolated from each other, this naturally reflects the performance of the system.
We need to do something to ensure that transactions do not add undue burden to the system.

Use small transactions, two common strategies:
1: Ensure that all required user input is feasible before issuing the start transaction command
2: Try to break large transactions into smaller ones Transactions are then executed separately.

Choose the appropriate isolation level. The higher the isolation level, the lower the performance. Therefore, choosing the appropriate isolation level helps to optimize performance.

Avoid deadlock. In a transaction environment, when Deadlock occurs when two or more clients in different sequences want to update the same data at the same time. We should avoid deadlock.

The above is the detailed content of In-depth understanding of MySQL advanced drifting (5). For more information, please follow other related articles on the PHP Chinese website!