MySQL: transaction processing, mysql Transaction Processing

MySQL: transaction processing, mysql Transaction Processing

[Transaction] A group of SQL statement operation units. All SQL statements in the group complete a business.

If the entire group is successful, all operations in the group are successful;

Otherwise, if any of the statements fails, the entire operation fails.

Operation Failure means that the entire process is meaningless and the database should be returned to the initial status before the operation.

[Point] 1. After the failure, you can return to the start position;

2. Before the operation succeeds, other users (process and planning) cannot see the modifications in the operation.

[Idea] design a mark (backup point) before a group of operations ).

[Implementation] use the "transaction log" function of the innodb Storage engine.

The execution is divided into two phases:

1. Execution phase;

2. The stage of submitting the execution result to the database;

The transaction log stores the results of the execution phase. If you select to submit, the execution results are submitted to the database.

(Default execution method: automatic submission)

You need to disable the automatic submission function (a system variable exists, and autocommit can configure automatic submission)

 

1 set autocommit = 0; // 0 indicates that the automatic submission function is disabled; 1 indicates that the automatic submission function is enabled.

 

If yes, execute commit to submit the results;

If it fails, execute rollback and return to the starting position.

--------------------------------------------------------------------------

[Common transaction commands]

1. start transaction to enable the transaction (function: Disable Automatic commit ).

Feature: if the transaction ends, the commit mechanism will be applied whether it is successful or not, and the status will return to the autocommit state (originally autocommit, it will return to the autocommit State ).

2. commit

3. rollback

[Small exercise]

1. Commands

2. Why is there a transaction?

Automatic commit and transaction logs.

3. What are the features of transactions? (ACID)

1) atomicity: for the outside world, the SQL statement in the transaction is a whole;

2) Consistency: data is not affected by other operations during transaction execution;

3) isolation: transactions do not affect each other.

4) durability: Once a transaction is committed, it means it has an impact on the database and cannot be rolled back.