This article describes the MySQL transaction management operation. Share with you for your reference, the details are as follows:

In this article:

• What is transaction management
• Transaction management operations
• Rollback Point
• Default transaction management

Release date: 2018-04-18

What is transaction management:

• A series of operations to be performed can be called a transaction, and transaction management is to manage these operations to be either fully executed or not executed at all (a classic example is: A wants to transfer money to B, first A's money is reduced, but suddenly the database loses power, resulting in the inability to add money to B, and then due to data loss, B does not acknowledge receiving A's money; here the transaction is to ensure that both adding money and subtracting money are fully executed or not executed at all. If adding money fails, then subtracting money will not occur).
• The significance of transaction management: ensuring the integrity of data operations.
• Not all data engines in MySQL support transaction management, only InnoDB supports transaction management.

Transaction management features:

• Atomicity: The entire operation of a transaction is a whole and cannot be divided. Either all of it succeeds or all of it fails.
• Consistency: The data in the data table does not change before and after the transaction operation.
• Isolation: Transaction operations are isolated from each other and are not affected.
• Persistence: Once the data is submitted, it cannot be changed, and the data table data is permanently changed.

Transaction management operations:

• Enable transaction management: After enabling it, the following SQL statements will not be executed immediately and the results will not be written to the table, but will be written to the transaction log.
  • start transaction;
• Rollback operation: Rollback will clear the content written to the transaction log after the transaction management is started, that is, restore to the state before the transaction management is started.
  • Syntax: rollback;
  • Note: The rollback operation only rolls back the "written" content, and cannot roll back the ordinary table read select statement.
• Transaction commit: write the results of the SQL statement to the data table.
  • Syntax: commit:

Experimental table:

create table bankaccount(id int primary key auto_increment,name varchar(15),money int);
insert into bankaccount(name,money) values("Jobs",2000);
insert into bankaccount(name,money) values("Bill",3000); 

Replenish:

• When a commit or rollback statement is executed, the transaction is automatically closed (future changes are implicitly committed).
• Lock mechanism: When a transaction operates a table, if an index is used to retrieve a value, the corresponding row will be locked; if an index is not used to retrieve a value, the entire table will be locked. After locking, other connections cannot operate on the specified row or table.

Rollback point:

• The rollback point can specify the rollback location. For example, if you have typed 100 commands and found that the 81st command was typed incorrectly, you can save a lot of time if you roll back to a point before command 81 instead of rolling back to before the transaction was started. 】
• grammar:
  • Create a rollback point: savepoint rollback point name;
  • Rollback to the rollback point: rollback to rollback point name;

Replenish:

• The rollback point becomes invalid after the transaction management is closed (after rollback or commit). Do not use the rollback point outside of the transaction.

Default transaction management:

• By default, MySQL transaction management is turned off (automatic transaction), and the results of the statement are written to the data table immediately.
  • You can use show variable like 'autocommit' to check whether automatic transactions are enabled. A value of 1 means that automatic transactions are enabled, and a value of 0 means that they are disabled.
• Turn off automatic transactions: set autocommit = 0; [After turning it off, commit is required to execute each statement, which is equivalent to starting transaction management]
  • However, please note that set autocommit is for session variables, so this setting is only effective in this session connection.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL transaction operation skills", "Summary of MySQL index operation skills", "Summary of MySQL commonly used functions", "Summary of MySQL log operation skills", "Summary of MySQL stored procedure skills" and "Summary of MySQL database lock-related skills".

I hope this article will be helpful to everyone's MySQL database design.