MySQL rollback (summary sharing)

This article brings you relevant knowledge about mysql. It mainly organizes rollback-related issues and mainly introduces bookstore rollback and rollback mechanism. Let’s take a look at it together. ,I hope everyone has to help.

Recommended learning: mysql video tutorial

We often encounter operating a large table and find that the operation time is too long or affects the online Business needs to roll back large table operations. After stopping large table operations, waiting for rollback is a very long process. Although you may know some methods to shorten the time and are in awe of the integrity of the data in the production environment, you may choose not to intervene.

Transaction rollback

A transaction is an execution unit in a relational database and can be submitted or rolled back through the final stage control. Perform rollback operations in various scenarios where integrity cannot be guaranteed. Rollback in MySQL is accomplished through the Undo log, which contains information about how to undo the latest changes related to the transaction. Undo logs exist in the Undo log segment, and the Undo log segment is included in the rollback segment. The rollback segment is located in the undo table space and the global Temporary table space.
The relationship is as follows:

• undo file

  

mysql > show variables like '%undo%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| innodb_max_undo_log_size | 1073741824         |
| innodb_undo_directory    | /opt/data8.0/mysql |
| innodb_undo_log_encrypt  | OFF                |
| innodb_undo_log_truncate | ON                 |
| innodb_undo_tablespaces  | 2                  |
+--------------------------+--------------------+
5 rows in set (0.00 sec)

refers to the global Temporary A temporary tablespace (ibtmp1) that stores rollback segments for changes to user-created temporary tables.

mysql > SELECT @@innodb_temp_data_file_path;
+-------------------------------+
| @@innodb_temp_data_file_path  |
+-------------------------------+
| ibtmp1:128M:autoextend:max:30G |
+-------------------------------+

After understanding the files included in the rollback, continue reading.

Rollback mechanism:

MySQL rollback control is coordinated by the internal innodb engine and does not provide a human-controlled mechanism. The MySQL rollback parameters currently provided are as follows:

mysql> SHOW VARIABLES LIKE  '%ROLL%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
| innodb_rollback_segments   | 128   |
+----------------------------+-------+

innodb_rollback_on_timeout:

By default, InnoDB only rolls back the last statement when the transaction times out. If --InnoDB -rollback-on-timeout is specified, a transaction timeout will cause InnoDB to abort and roll back the entire transaction. It is turned off by default, once the specified time, such as rollback fails. It is conceivable that there will be inconsistencies in the data. This method is not advisable.

Innodb_rollback_segments (1~128):

Defines the number of rollback segments allocated to each undo table space, and the number of global temporary table spaces allocated for transactions that generate undo records .
The number of transactions supported by the rollback segment: depends on the number of undo slots in the rollback segment and the number of undo logs required for each transaction

The number of undo slots in the officially provided rollback segment is based on InnoDB Page size related:

From the latest MySQL8.0.27 source code implementation storage\innobase\include\trx0rseg.h:

/* Number of undo log slots in a rollback segment file copy 
这里 UNIV_PAGE_SIZE正常页面的大小  即 1024*/
#define TRX_RSEG_N_SLOTS (UNIV_PAGE_SIZE / 16)

/* Maximum number of transactions supported by a single rollback segment 
单个回滚段支持的最大事务数1024/2=512
*/
#define TRX_RSEG_MAX_N_TRXS (TRX_RSEG_N_SLOTS / 2)

By default page 1024 slots (TRX_RSEG_N_SLOTS) are divided into it, and each slot corresponds to an undo log object. Therefore, theoretically InnoDB can support 128 * 512 = 65536 ordinary transactions.
For the principle part, please refer to MySQL · Engine Features · InnoDB undo log roaming
Officially provides undbo rollback concurrent read and write scenarios:

Return from the principle of appeal In actual application scenarios:
The ability to support rollback segments is still considerable, but it is often very slow when executing large batches of rollbacks. Especially during online processing, it may take 10 minutes to roll back 100,000 rows. Or even longer.
The following uses sysbench to prepare 50 million single table data. Under no load, delete it for about 1 minute, and then use kill -9 to force stop the transaction to roll back the transaction:

Obviously the effect of restarting is better.
However, the kill -9 method can easily damage the data page, and there is a great risk. The database is also under load in daily life. It can be imagined that the cost of rolling back large transactions is very high.

Summary

Large rollback operations should be avoided as much as possible, which consumes database resources and performance, and can lead to major production accidents in a production environment. If large transaction rollback cannot be avoided, you can take the following methods:

• For batch operations, you can submit them in batches. For example, the
• undo space and global temporary table space of 1,000 to 5,000 rows can be adjusted appropriately. It is recommended to use 4 undo files and initialize 1G global ibtmp1. In a high-availability environment and ensure data consistency, you can promote the slave to a new master, provide services, and wait for large transactions to be rolled back.
• In extreme cases, you can use kill -9 to restart the operation. Because the amount of data is very large, mysql recovery will be slow. At this time, you need to wait for mysql to perform crash recovery. The waiting time will be different depending on the amount of data.
• If the data page is damaged or the rollback is skipped during the restart process, you can pass innodb_force_recovery=3 (no transaction rollback operation is performed.)
• Recommended learning:

mysql video tutorial

The above is the detailed content of MySQL rollback (summary sharing). For more information, please follow other related articles on the PHP Chinese website!