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

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共2550人左右 1 + 2 + 3 + 4 +5 + 6 + 7）(1 2 3 4 5 群均已爆满,6群接近500人，新人进7群200人 ，8群)

       最近光折腾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 篇文章

置顶文章：

DBA 失职导致 PostgreSQL 日志疯涨

微软 “爱” 上PostgreSQL, PG  “嫁给” 微软！

撕逼！PostgreSQL 和 MongoDB 开撕，MySQL却躺枪

阿里云 安全扫描 ,说我PostgreSQL 自建主机极度不安全, 谁的问题?

PostgreSQL 13.0-13.15 功能更新和bug fixed列表

撕逼！PostgreSQL 和 MongoDB 开撕，MySQL却躺枪

PostgreSQL 远程管理越来越简单，6个自动化脚本开胃菜

OceanBase 学习记录-- 建立MySQL租户,像用MySQL一样使用OB

OceanBase  学习记录 -- 安装简易环境

病毒攻击PostgreSQL暴力破解系统，防范加固系统方案（内附分析日志脚本）

阿里云数据库产品权限设计缺陷 ，六个场景诠释问题，你可以做的更好？

MySQL还用学吗？这谁问的 “好问题” ！

MySQL 8.0x 到 9.0均可能崩溃--云厂商开发指责 MYSQL不测试就推新版本？

MySQL 让你还用5.7 出事了吧，用着用着5.7崩了

MySQL 8.0 小版本更新要点，那个小版本更稳定（8.0.24-8.0.37）

MySQL 8.0 版本更新 要点 列表 （8.0-8.0.23）

OceanBase  学习记录 --  开始入门

参加 “央企” 项目改造会后的，“数据库瞎想”

PostgreSQL DBA硬扛 垃圾 “开发”，“架构师”，滥用PG 你们滚出 ！（附送定期清理连接脚本）

PolarDB 杀疯了，Everywhere Everytime Everydatabase on Serverless

往期热门文章：

PostgreSQL  哪些版本尽量避免使用，版本更新重点明晰（PG12）

PostgreSQL  15 16 小版本更新信息小结 版本更新是不是挤牙膏

PostgreSQL 14 小版本分析，有那个版本不建议使用

Windows 是MySQL和PostgreSQL高性能数据库的坟墓

道歉贴，为最近写的一篇“垃圾贴”

临时工说：DBA 7*24H 给2万的工作，到底去不去？

PolarDB 最近遇到加字段加不上的问题 与 使用PolarDB 三年感受与恳谈

PostgreSQL 稳定性平台 PG中文社区大会--杭州来去匆匆

MySQL 让你还用5.7 出事了吧，用着用着5.7崩了

MySQL 的SQL引擎很差吗？由一个同学提出问题引出的实验

临时工访谈：从国产数据库 到 普罗大众的产品 ！与在美国创业软件公司老板对话

PostgreSQL 如何通过工具来分析PG 内存泄露