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

【 使用环境 】测试环境
【 使用版本 】 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 个赞

又多等了一会,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 个赞

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

1 个赞

是显示的问题:我设置的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 个赞

我在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 ,超过10s,没报回滚
[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 ,超过了30s才报错
[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]> 

应该是这个原因:

这个参数默认是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 个赞

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

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