MySQL timeout 参数可以让事务不完全回滚

AustinDatabases

发布于 2024-11-25 10:58:55

11900

代码可运行

文章被收录于专栏：AustinDatabasesAustinDatabases

运行总次数：0

代码可运行

最近光折腾PostgreSQL,MongoDB,PolarDB,MySQL写的少了，今天咱们写一写，MySQL生疏了的DBA，把两个参数给混淆的故事，故事是这样的，在MySQL中，有两个参数

innodb_lock_wait_timeout lock_wait_timeout

时间一长，竟然把这两个参数给弄混了，innodb_lock_wait_timeout 是innodb 数据库引擎中的管理行锁等待的参数，默认值为50秒，当一个事务中尝试获取另一个事务中已经获取的行锁，且超过innodb_lock_wait_timeout 的时间，则MySQL将这个等待其他事务结束释放行锁的事务，进行终结，且会抛出 lock wait timeout exceeded try restarting transaction 的错误。

问题来了，如果事务要回滚，而等待的事务里面有一堆的语句，是整体回滚还是，部分回滚呢？那就必须请出第二个参数 innodb_rollback_on_timeout，这个参数的默认值是OFF，仅仅回滚最后一条语句，但当设置为ON的情况下，则会进行完全回滚。

这点实际上在一些严谨的事务运行中，非常的重要，我们画一个图。

我们最上面的部分做一个实验，将innodb_rollback_on_timeout 和 innodb_rollback_on_timeout 的ON 和 OFF 不同状态进行展示。

1 innodb_rollback_on_timeout = OFF

事务 1

mysql> show variables like 'innodb_rollback%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
| innodb_rollback_segments   | 128   |
+----------------------------+-------+

mysql> CREATE TABLE `rental_1` (
    ->   `rental_id` int NOT NULL AUTO_INCREMENT,
    ->   `inventory_id` int DEFAULT NULL,
    ->   `customer_id` int DEFAULT NULL,
    ->   `rental_date` datetime DEFAULT NULL,
    ->   `return_date` datetime DEFAULT NULL,
    ->   `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`rental_id`),
    ->   KEY `idx_customer_id` (`customer_id`),
    ->   KEY `idx_rental_date` (`rental_date`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.38 sec)

mysql> 
mysql> 
mysql> insert into rental_1 (rental_date,return_date) values (now(),now());
Query OK, 1 row affected (0.02 sec)

mysql> insert into rental_1 (rental_date,return_date) values (now(),now());
Query OK, 1 row affected (0.01 sec)

mysql> insert into rental_1 (rental_date,return_date) values (now(),now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from rental_1;
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
| rental_id | inventory_id | customer_id | rental_date         | return_date         | last_update         |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
|         1 |         NULL |        NULL | 2024-09-19 13:01:31 | 2024-09-19 13:01:31 | 2024-09-19 13:01:31 |
|         2 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         3 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into rental_1 (rental_date,return_date) values (now(),now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into rental_1 (rental_date,return_date) values (now(),now());
Query OK, 1 row affected (0.00 sec)

mysql> update rental_1 set customer_id = 1 where rental_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from rental_1;
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
| rental_id | inventory_id | customer_id | rental_date         | return_date         | last_update         |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
|         1 |         NULL |           1 | 2024-09-19 13:01:31 | 2024-09-19 13:01:31 | 2024-09-19 13:02:38 |
|         2 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         3 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         4 |         NULL |        NULL | 2024-09-19 13:01:46 | 2024-09-19 13:01:46 | 2024-09-19 13:01:46 |
|         5 |         NULL |        NULL | 2024-09-19 13:01:47 | 2024-09-19 13:01:47 | 2024-09-19 13:01:47 |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

事务 2

mysql> show variables like 'innodb_rollback_%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
| innodb_rollback_segments   | 128   |
+----------------------------+-------+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update rental set customer_id = '2222' where rental_id = 5000002;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update rental_1 set customer_id = 1 where rental_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from rental_1;
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
| rental_id | inventory_id | customer_id | rental_date         | return_date         | last_update         |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
|         1 |         NULL |           1 | 2024-09-19 13:01:31 | 2024-09-19 13:01:31 | 2024-09-19 13:02:38 |
|         2 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         3 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from rental_1;
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
| rental_id | inventory_id | customer_id | rental_date         | return_date         | last_update         |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
|         1 |         NULL |           1 | 2024-09-19 13:01:31 | 2024-09-19 13:01:31 | 2024-09-19 13:02:38 |
|         2 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         3 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         4 |         NULL |        NULL | 2024-09-19 13:01:46 | 2024-09-19 13:01:46 | 2024-09-19 13:01:46 |
|         5 |         NULL |        NULL | 2024-09-19 13:01:47 | 2024-09-19 13:01:47 | 2024-09-19 13:01:47 |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+

实验证明当innodb_rollback_on_timeout=OFF; 则事务1在回滚后，将最后产生问题的update 语句进行回滚，而上面insert 的两条数据，成功的进行了插入。

实验2 innodb_rollback_on_timeout=ON;

事务 1

mysql> begin;
mysql> 
mysql> insert into rental_1 (rental_date,return_date) values (now(),now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into rental_1 (rental_date,return_date) values (now(),now());
Query OK, 1 row affected (0.00 sec)

mysql> update rental_1 set customer_id = 1 where rental_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from rental_1;
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
| rental_id | inventory_id | customer_id | rental_date         | return_date         | last_update         |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
|         1 |         NULL |           2 | 2024-09-19 13:01:31 | 2024-09-19 13:01:31 | 2024-09-19 13:15:13 |
|         2 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         3 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         4 |         NULL |        NULL | 2024-09-19 13:01:46 | 2024-09-19 13:01:46 | 2024-09-19 13:01:46 |
|         5 |         NULL |        NULL | 2024-09-19 13:01:47 | 2024-09-19 13:01:47 | 2024-09-19 13:01:47 |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from rental_1;
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
| rental_id | inventory_id | customer_id | rental_date         | return_date         | last_update         |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
|         1 |         NULL |           2 | 2024-09-19 13:01:31 | 2024-09-19 13:01:31 | 2024-09-19 13:15:13 |
|         2 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         3 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         4 |         NULL |        NULL | 2024-09-19 13:01:46 | 2024-09-19 13:01:46 | 2024-09-19 13:01:46 |
|         5 |         NULL |        NULL | 2024-09-19 13:01:47 | 2024-09-19 13:01:47 | 2024-09-19 13:01:47 |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

事务2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update rental_1 set customer_id = 2 where rental_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from rental_1;
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
| rental_id | inventory_id | customer_id | rental_date         | return_date         | last_update         |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
|         1 |         NULL |           2 | 2024-09-19 13:01:31 | 2024-09-19 13:01:31 | 2024-09-19 13:15:13 |
|         2 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         3 |         NULL |        NULL | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 | 2024-09-19 13:01:32 |
|         4 |         NULL |        NULL | 2024-09-19 13:01:46 | 2024-09-19 13:01:46 | 2024-09-19 13:01:46 |
|         5 |         NULL |        NULL | 2024-09-19 13:01:47 | 2024-09-19 13:01:47 | 2024-09-19 13:01:47 |
+-----------+--------------+-------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

通过第二个实验，我们可以看到，事务1已经插入的2条记录，已经整体回滚，完成了数据库的事务的特性。基于这个问题，我们从新梳理一下关于timeout们以及周边参数的关系。

以innodb_lock_wait_timeout作为核心参数，与他有关的第一个参数就是innodb_rollback_on_timeout,上面的实验也说明了问题，如果要完全遵循数据库原理的情况下，必须在一个事务被终止后进行完全回滚。

一般情况下innodb_lock_wait_timeout 设置的时间为1-3秒，而innodb_rollback_on_ttimeout = ON,在这个配置确认后，第二个问题 innodb_deadlock_detect 到底是开不开，一些老的DBA会对innodb_deadlock_detect建议不开启，而是采用innodb_lock_wait_timeout来进行问题的解决，通过设置innodb_lock_wait_timeout 等于一个比较小的数字来解决死锁，而新的DBA会建议开启innodb_deadlock_detect，来积极解决在发生死锁的第一时间来进行死锁的解除。

随着MySQL的版本的推进，对于8.0后的MySQL且硬件资源比较丰富的情况下，建议开启innodb_deadlock_detect参数，通过innodb_deadlock_detect和innodb_lock_wait_timeout参数来分辨出来，那些是死锁，那些是blocked的语句，且给应用更多和宽容的空间。

这里举一个例子，当应用程序中，就是有一些较大的事务，在1-2秒钟才能完成，而在他操作的过程中，有一些短的事务和他争抢资源，而这些事务可以等待一定的时间，在这样的情况下，为了减少业务中提交事务的失败率，是可以将innodb_lock_wait_timeout 的数值进行加大，降低因为产生blocked后超时，将大事务进行回滚的可能性。

mysql> show variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set (0.83 sec)

mysql> show variables like 'innodb_table_locks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_table_locks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql>

而剩下的两个Lock,lock_wait_timeout,和innodb_table_locks。innodb_table_locks 是控制innodb引擎下的数据库锁中，是否存在表锁，在正常的系统中，这个锁是要开启的，对这个参数的调整基本上是没有太多的不同的认知，而lock_wait_timeout这个参数，从官方的说明中是控制事务等待行锁最长的时间，但在有了innodb_lock_wait_timeout这个参数的情况下，这个lock_wait_timeout参数更多的意义就与DDL 有关了。

因为DDL 操作也需要获取行锁，尤其在DDL ONLINE 的情况下，那么等待多长时间行锁，就是决定DDL能否成功的一个关键，所以这个lock_wait_timeout 实际上的意义与你的DDL 等待的时长有关，比如当一个select * from table for update 的语句在处理的时候，那么你的alter table 的操作必须等待这个操作的结束，所以lock_wait_timeout 都设置的比较大。

mysql> show variables like 'lock%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| lock_wait_timeout | 86400 |
| locked_in_memory  | OFF   |
+-------------------+-------+
2 rows in set (0.00 sec)

mysql>

到此为止，timeout的参数，梳理的一遍，有的时候是容易健忘，需要一段时间重新梳理好似熟悉的知识，但实际上已经不清晰了的知识。

截止今天共发布 1242 篇文章

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-10-31，如有侵权请联系 cloudcommunity@tencent.com 删除

mysql