【布道师经验分享】显式和隐式事务在 ob_query_timeout 和 ob_trx_lock_timeout 超时表现

经验分享 知识分享
#1

测试版本： 4.3.1.0

会话1：

1）表和插入测试数据

CREATE TABLE `t4` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL
);
insert into `t4` values(1,1),(2,2);

2）设置参数

set autocommit=off;

set global ob_query_timeout=20000000;

set global ob_trx_lock_timeout=10000000;

# 断开连接 重连集群生效
MySQL [test]> show variables like 'ob_query_timeout';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| ob_query_timeout | 20000000 |
+------------------+----------+
1 row in set (0.00 sec)

MySQL [test]> show variables like 'ob_trx_lock_timeout';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| ob_trx_lock_timeout | 10000000 |
+---------------------+----------+
1 row in set (0.00 sec)

3） update 更新 但不提交，观察会话2，会话3的表现

MySQL [test]> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> update t4 set b=1 where a=2;
Query OK, 11 rows affected (0.00 sec)
Rows matched: 11  Changed: 11  Warnings: 0

MySQL [test]>

会话2：

1）直接执行更新sql语句，20s后超时，为ob_query_timeout参数生效。

MySQL [test]>  select now();update t4 set b=1 where a=2; select now();
+---------------------+
| now()               |
+---------------------+
| 2024-07-04 10:54:22 |
+---------------------+
1 row in set (0.00 sec)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[11.161.204.105:2882] [2024-07-04 10:54:42.033437] [YB420BA1CC69-00061BDA2B28E33A-0-0]
+---------------------+
| now()               |
+---------------------+
| 2024-07-04 10:54:42 |
+---------------------+
1 row in set (0.00 sec)

2）ob_query_timeout设置10s，ob_trx_lock_timeout设置20s，10s后超时，还是ob_query_timeout参数生效。

set ob_query_timeout=10000000;
set ob_trx_lock_timeout=20000000;

MySQL [test]> show variables like 'ob_query_timeout';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| ob_query_timeout | 10000000 |
+------------------+----------+

MySQL [test]> show variables like 'ob_trx_lock_timeout';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| ob_trx_lock_timeout | 20000000 |
+---------------------+----------+

MySQL [test]> select now();update t4 set b=1 where a=2; select now();
+---------------------+
| now()               |
+---------------------+
| 2024-07-04 10:57:39 |
+---------------------+
1 row in set (0.00 sec)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[11.161.204.105:2882] [2024-07-04 10:57:48.951717] [YB420BA1CC69-00061BDA391843E7-0-0]
+---------------------+
| now()               |
+---------------------+
| 2024-07-04 10:57:49 |
+---------------------+
1 row in set (0.01 sec)

会话3：

1）使用begin事务执行更新，ob_query_timeout 20s,ob_trx_lock_timeout 10s，10s后超时，是ob_trx_lock_timeout参数生效。

MySQL [test]> show variables like 'ob_query_timeout';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| ob_query_timeout | 20000000 |
+------------------+----------+


MySQL [test]> show variables like 'ob_trx_lock_timeout';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| ob_trx_lock_timeout | 10000000 |
+---------------------+----------+


MySQL [test]> begin;
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> select now();update t4 set b=1 where a=2; select now();
+---------------------+
| now()               |
+---------------------+
| 2024-07-04 11:06:20 |
+---------------------+
1 row in set (0.00 sec)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[11.161.204.105:2882] [2024-07-04 11:06:30.324786] [YB420BA1CC69-00061BDA2B28E376-0-0]
+---------------------+
| now()               |
+---------------------+
| 2024-07-04 11:06:30 |
+---------------------+
1 row in set (0.00 sec)

2）ob_query_timeout设置10s，ob_trx_lock_timeout设置20s，10s后超时，是ob_query_timeout参数生效。

set ob_query_timeout=10000000;
set ob_trx_lock_timeout=20000000;

MySQL [test]> show variables like 'ob_query_timeout';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| ob_query_timeout | 10000000 |
+------------------+----------+

MySQL [test]> show variables like 'ob_trx_lock_timeout';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| ob_trx_lock_timeout | 20000000 |
+---------------------+----------+

MySQL [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select now();update t4 set b=1 where a=2; select now();
+---------------------+
| now()               |
+---------------------+
| 2024-07-04 11:07:49 |
+---------------------+
1 row in set (0.00 sec)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[11.161.204.105:2882] [2024-07-04 11:07:59.861926] [YB420BA1CC69-00061BDA2B28E380-0-0]
+---------------------+
| now()               |
+---------------------+
| 2024-07-04 11:07:59 |
+---------------------+
1 row in set (0.01 sec)

测试总结：

隐式事务，在等待锁释放时，超时受ob_query_timeout参数影响。

显式事务，在等待锁释放使，受min(ob_query_timeout,ob_trx_lock_timeout)影响。

2 个赞
DDL 异常耗时报错超时
#2

这个结果不符合预期吧，隐式事务 也是事务，不应该区分对待。@秃蛙

2 个赞
#5

事务没有显式和隐式的区别，楼主 @秃蛙 的意思是介绍下显式提交事务（通过执行 commit 命令）和隐式提交事务（通过 set autocommit = 1），这两种不同的事务提交方式，受到的超时时间控制有哪些不同。

测试得出的结论看着有点儿晕。印象中还有一个 ob_trx_timeout，会影响测试结果吗？@秃蛙

1 个赞
#7

这个是 bug 而非 feature。

最新版本（434）已经修复，都受 min(ob_query_timeout,ob_trx_lock_timeout) 的影响了。

1 个赞
#8

@秃蛙 帖子已经取消公开。

#9

@obpilot 使用ob434重新测试了下， 显式和隐式 都是受min(ob_query_timeout,ob_trx_lock_timeout)影响了，新版本做了一些优化调整，后续帖子会撤掉重新调整。
@兹拉坦 不受ob_trx_timeout参数影响。

1 个赞
#10

那 ob_trx_timeout 这个超时时间是干啥的呀？

#11

刚重新测试了下，也会受到ob_trx_timeout影响。

隐式和显示: ： min( ob_trx_timeout,ob_query_timeout,ob_trx_lock_timeout)