Detailed explanation of the syntax and process of executing MySQL transactions

Abstract: MySQL provides a variety of storage engines to support transactions.

MySQL provides a variety of storage engines to support transactions. The storage engines that support transactions are InnoDB and BDB. InnoDB storage engine transactions are mainly implemented through UNDO logs and REDO logs, while MyISAM storage engine does not support transactions.

Extension: Any database will have various logs to record the database operation status, daily operations, error messages, etc. MySQL is no exception. For example, when user root logs in to the MySQL server, the user's login time, execution operations, etc. will be recorded in the log file.

In order to maintain the MySQL server, it is often necessary to perform log operations in the MySQL database:

UNDO log: copies the data before the transaction is executed, and is used to roll back the data when an exception occurs in the transaction. REDO log: records each operation that updates data during transaction execution. When the transaction is committed, the content will be flushed to disk.

By default, each SQL statement is a transaction, which means it is automatically committed after executing the SQL statement. In order to achieve the purpose of treating several operations as a whole, you need to use BEGIN or START TRANSACTION to start a transaction, or disable automatic submission of the current session.

Syntax and flow of executing transactions

SQL uses the following statements to manage transactions.

1) Start a transaction

BEGIN;

or

START TRANSACTION;

This statement explicitly marks the start point of a transaction.

2) Commit the transaction

MySQL uses the following statement to commit a transaction:

COMMIT;

COMMIT means committing a transaction, that is, committing all operations of a transaction. Specifically, all updates to the database in the transaction are written to the physical database on disk, and the transaction ends normally.

Committing a transaction means that all data executed since the start of the transaction is modified to become a permanent part of the database, thus marking the end of a transaction. Once this command is executed, the transaction cannot be rolled back. This operation is performed only when all modifications are ready to be committed to the database.

3) Rollback (undo) a transaction

MySQL rolls back a transaction using the following statement:

ROLLBACK;

ROLLBACK means canceling a transaction. That is, if some failure occurs during the execution of a transaction and the transaction cannot continue, the system will cancel all completed operations on the database in the transaction and roll back to the state at the beginning of the transaction. The operation here refers to the update operation on the database.

When an error is encountered during transaction execution, use the ROLLBACK statement to roll back the transaction to the starting point or a specified hold point. At the same time, the system will clear all data modifications made since the start of the transaction or to a certain save point, and release the resources controlled by the transaction. Therefore, this statement also marks the end of the transaction.

Summarize

The SQL statements following the BEGIN or START TRANSACTION statement that update the database data will be recorded in the transaction log until a ROLLBACK statement or a COMMIT statement is encountered. If an operation in a transaction fails and the ROLLBACK statement is executed, all updated data after the transaction statement is opened can be rolled back to the state before the transaction started. If all operations in the transaction are completed correctly and the COMMIT statement is used to submit the updated data to the database, the data is now in a new consistent state.

Example Demonstration

The following two examples demonstrate the specific usage of MySQL transactions.

example

The following simulation shows that after Zhang San's account is reduced by 500 yuan, but Li Si's account has not yet increased by 500 yuan, there are other sessions accessing the data table. Since the code needs to be executed in two windows, for ease of reading, we call them Window A and Window B.

Open a transaction in window A and update the data in the bank table in the mybank database. The SQL statements and running results are as follows:

In the B window, query the data in the bank data table. The SQL statement and the running results are as follows:

From the results, we can see that although the transaction in window A has changed the data in the bank table, the data is not updated immediately. At this time, other sessions still read the data before the update.

Continue to execute the transaction in window A and commit the transaction. The SQL statement and running results are as follows:

In window B, query the data of the bank table again. The SQL statement and the running result are as follows:

After executing COMMIT in window A to commit the transaction, the updates made to the data will be committed together, and other sessions will read the updated data. From the results, we can see that the total account balance of Zhang San and Li Si is consistent with that before the transfer, so the data is updated from one consistent state to another consistent state.

As mentioned earlier, when a transaction has problems during execution, that is, a complete transaction cannot be executed according to the normal process, you can use the ROLLBACK statement to roll back and restore the data to its initial state.

In Example 1, Zhang San's account balance has been reduced to 500 yuan. If he transfers another 1,000 yuan, the balance will be negative, so he needs to roll back to the original state. As shown in Example 2.

example

Reduce Zhang San's account balance by 1,000 yuan and roll back the transaction. The SQL statement and running results are as follows:

From the results, we can see that after executing the transaction rollback, the account data is restored to the initial state, that is, the state before the transaction was executed.

expand

In database operations, in order to effectively ensure the correctness of concurrent data reading, transaction isolation levels are proposed. In the demonstrations of Examples 1 and 2, the transaction isolation level is the default isolation level. In MySQL, the default transaction isolation level is REPEATABLE-READ, which means that when the transaction is not completed (COMMIT or ROLLBACK is not executed), other sessions can only read uncommitted data.

Precautions

MySQL transaction is a very resource-intensive function. Please pay attention to the following points when using it.

1) Keep transactions as short as possible

From the start to the end of a transaction, a large amount of resources will be reserved in the database management system to ensure the atomicity, consistency, isolation, and durability of the transaction. If in a multi-user system, larger transactions will occupy a large amount of system resources, making the system overwhelmed, affecting the software's operating performance and even causing the system to crash.

2) Minimize the amount of data accessed in a transaction

When executing transactions concurrently, the smaller the amount of data operated by the transactions, the fewer operations on the same data between transactions.

3) Try not to use transactions when querying data

Browsing and querying data will not update the database data, so you should try not to use transactions to query data to avoid occupying excessive system resources.

4) Try not to wait for user input during transaction processing

In the process of handling transactions, if you need to wait for the user to enter data, the transaction will occupy resources for a long time and may cause system blocking.

This article is shared from Huawei Cloud Community "MySQL Transaction Syntax and Process", the original author is: Lucky Boy.

Summarize

This is the end of this article about the syntax and process of MySQL transaction execution. For more relevant MySQL transaction content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!