我设置的变量ob_trx_idle_timeout为啥不起作用？

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 使用版本 】 OB社区版4.3.5
【问题描述】我在尝试体验 ob_trx_idle_timeout 的功能，按照OBCP的V4版教材第150页的描述，
将该参数修改为10秒后，开启事务，在事务中间等等超过10秒，再尝试执行sql时事务会报错，并提示执行rollback。
我做了如下测试，两次select now(); 之间idle等待了40秒，没有任何报错；中间idle了10分钟，也可以继续执行sql不报错，是为什么？

谢谢！

obclient(root@ob_school)[school_db]> set ob_trx_idle_timeout=10 * 1000 * 1000;
Query OK, 0 rows affected (0.014 sec)

obclient(root@ob_school)[school_db]> set ob_trx_timeout=200 * 1000 * 1000;
Query OK, 0 rows affected (0.001 sec)

obclient(root@ob_school)[school_db]> set ob_query_timeout=60 * 1000 * 1000;
Query OK, 0 rows affected (0.001 sec)

obclient(root@ob_school)[school_db]> show variables like ‘ob_trx_idle_timeout’;
±--------------------±---------+
| Variable_name | Value |
±--------------------±---------+
| ob_trx_idle_timeout | 10000000 |
±--------------------±---------+
1 row in set (0.005 sec)

obclient(root@ob_school)[school_db]> SET autocommit=0;
Query OK, 0 rows affected (0.002 sec)

obclient(root@ob_school)[school_db]> begin;
Query OK, 0 rows affected (0.000 sec)

obclient(root@ob_school)[school_db]> update classes set CS_Name=‘obcpv3’ where CS_ID=2;
Query OK, 1 row affected (0.054 sec)
Rows matched: 1 Changed: 1 Warnings: 0

obclient(root@ob_school)[school_db]> select now();
±--------------------+
| now() |
±--------------------+
| 2025-06-14 20:54:05 |
±--------------------+
1 row in set (0.001 sec)

obclient(root@ob_school)[school_db]> select now();
±--------------------+
| now() |
±--------------------+
| 2025-06-14 20:54:44 |
±--------------------+
1 row in set (0.001 sec)

obclient(root@ob_school)[school_db]> select * from classes;
±------±--------+
| CS_ID | CS_Name |
±------±--------+
| 1 | obca |
| 2 | obcpv3 |
| 3 | obce |
±------±--------+
3 rows in set (0.001 sec)

obclient(root@ob_school)[school_db]> select now();
±--------------------+
| now() |
±--------------------+
| 2025-06-14 21:04:37 |
±--------------------+
1 row in set (0.006 sec)

obclient(root@ob_school)[school_db]>

1 个赞
#3

又多等了一会，ob_trx_timeout 这个参数起作用了。这个参数的设置是200秒的，在10多分钟后起作用了。 ob_trx_idle_timeout 应该只需要10秒就起作用，为啥不起作用呢？该怎么诊断？
obclient(root@ob_school)[school_db]> select now();
±--------------------+
| now() |
±--------------------+
| 2025-06-14 21:06:58 |
±--------------------+
1 row in set (0.001 sec)

obclient(root@ob_school)[school_db]> select * from classes;
ERROR 4012 (25000): Transaction timeout occurred, please rollback the transaction, set the variable ob_trx_timeout to a larger value and then restart the transaction
obclient(root@ob_school)[school_db]>

2 个赞
#4

你设置时间大概为17分钟，符合预期哈！

1 个赞
#5

是显示的问题：我设置的ob_trx_idle_timeout是10秒钟：
obclient(root@ob_school)[school_db]> show variables like ‘ob_trx_idle_timeout’;
±--------------------±---------+
| Variable_name | Value |
±--------------------±---------+
| ob_trx_idle_timeout | 10000000 |

所以我理解，在一个transaction里面的两个sql 语句中间，idle 10秒钟以上就该报错。但实际这个参数没有起作用，直到ob_trx_timeout起作用了。

1 个赞
#6

我在v4.2.1.10测试也是这样。

select version();
+----------------------------+
| version()                  |
+----------------------------+
| 5.7.25-OceanBase-v4.2.1.10 |
+----------------------------+
1 row in set (0.002 sec)


# 09:05:14开始事务
[root@localhost][test][09:05:12]> begin;
Query OK, 0 rows affected (0.000 sec)

[root@localhost][test][09:05:14]> insert into t values(1);
Query OK, 1 row affected (0.002 sec)

[root@localhost][test][09:05:17]> insert into t values(2);
Query OK, 1 row affected (0.001 sec)

[root@localhost][test][09:05:21]> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.001 sec)

[root@localhost][test][09:05:25]> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.001 sec)

# 09:05:25－－09:05:39 ，超过１０ｓ，没报回滚
[root@localhost][test][09:05:39]> insert into t values(3);
ERROR 4012 (25000): Transaction timeout occurred, please rollback the transaction, set the variable ob_trx_timeout to a larger value and then restart the transaction

＃09:05:14－－09:05:47 ，超过了３０ｓ才报错
[root@localhost][test][09:05:47]> select * from t;
ERROR 4012 (25000): Transaction timeout occurred, please rollback the transaction, set the variable ob_trx_timeout to a larger value and then restart the transaction
[root@localhost][test][09:06:01]>
#7

应该是这个原因：

image
image692×112 3.81 KB

这个参数默认是true：

show proxyconfig like 'enable_transaction_internal_routing'\G
*************************** 1. row ***************************
         name: enable_transaction_internal_routing
        value: True
         info: if enabled, proxy will route the dml statement in a transaction to different servers
  need_reboot: false
visible_level: USER
        range: 
 config_level: LEVEL_GLOBAL
1 row in set (0.002 sec)
2 个赞
#8

学习了，两位大佬学的好细节，平时都没注意到

#9

的确是的，我修改连接方式，直连节点，不连obproxy，就很快就timeout了：
obclient -h10.3.x.x -P2881 -uroot@ob_school