oceanbase ob_query_timeout/ob_trx_timeout参数测试

今天测试ob数据库，无意中发现两个ob_query_timeout/ob_trx_timeout参数影响事务。

一、测试数据

准备测试数据，测试过程中发现ora-600,OB错误码为4012

obclient [ZZX]> insert into t1 select * from t1;
Query OK, 516096 rows affected (7.006 sec)
Records: 516096  Duplicates: 0  Warnings: 0

obclient [ZZX]> insert into t1 select * from t1;
ORA-00600: internal error code, arguments: -4012, Timeout
obclient [ZZX]> insert into t1 select * from t1;
ORA-24761: transaction rolled back: transaction needs rollback
obclient [ZZX]> commit;
ORA-24761: transaction rolled back
obclient [ZZX]> select count(*) from t1;
+----------+
| COUNT(*) |
+----------+
|      504 |
+----------+
1 row in set (1.009 sec)

二、ob_query_timeou参数

根据Timeout关键字猜测可能与某个超时机制有关，检查会话参数
查看全局参数

obclient [oceanbase]> show variables like '%timeout%';
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| connect_timeout     | 10               |
| interactive_timeout | 28800            |
| lock_wait_timeout   | 31536000         |
| net_read_timeout    | 30               |
| net_write_timeout   | 60               |
| ob_pl_block_timeout | 3216672000000000 |
| ob_query_timeout    | 10000000         |
| ob_trx_idle_timeout | 120000000        |
| ob_trx_lock_timeout | -1               |
| ob_trx_timeout      | 100000000        |
| wait_timeout        | 28800            |
+---------------------+------------------+

查看会话参数

obclient [oceanbase]> show proxysession  variables all 18001 like '%timeout%';
+---------------------+------------------+---------+--------------------+-----------------------------------+
| variable_name       | value            | info    | modified_type      | sys_variable_flag                 |
+---------------------+------------------+---------+--------------------+-----------------------------------+
| interactive_timeout | 28800            | sys var | cold modified vars |  && global_scope && session_scope |
| wait_timeout        | 28800            | sys var | cold modified vars |  && global_scope && session_scope |
| connect_timeout     | 10               | sys var | cold modified vars |  && global_scope                  |
| net_read_timeout    | 30               | sys var | cold modified vars |  && global_scope && session_scope |
| net_write_timeout   | 60               | sys var | cold modified vars |  && global_scope && session_scope |
| lock_wait_timeout   | 31536000         | sys var | cold modified vars |  && global_scope && session_scope |
| ob_query_timeout    | 10000000         | sys var | hot modified vars  |  && global_scope && session_scope |
| ob_trx_timeout      | 100000000        | sys var | cold modified vars |  && global_scope && session_scope |
| ob_trx_idle_timeout | 120000000        | sys var | cold modified vars |  && global_scope && session_scope |
| ob_trx_lock_timeout | -1               | sys var | cold modified vars |  && global_scope && session_scope |
| ob_pl_block_timeout | 3216672000000000 | sys var | cold modified vars |  && global_scope && session_scope |
+---------------------+------------------+---------+--------------------+-----------------------------------+

上面sql前一个执行为7秒，那么下一个应该再15秒左右，检查发现 ob_query_timeout 默认参数为10000000=10s。应该sql中的select超时。调整该参数

obclient [ZZX]> set  ob_query_timeout=100000000;
Query OK, 0 rows affected (0.001 sec)

obclient [oceanbase]> show variables like '%timeout%';
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| connect_timeout     | 10               |
| interactive_timeout | 28800            |
| lock_wait_timeout   | 31536000         |
| net_read_timeout    | 30               |
| net_write_timeout   | 60               |
| ob_pl_block_timeout | 3216672000000000 |
| ob_query_timeout    | 10000000         |
| ob_trx_idle_timeout | 120000000        |
| ob_trx_lock_timeout | -1               |
| ob_trx_timeout      | 100000000        |
| wait_timeout        | 28800            |
+---------------------+------------------+
11 rows in set (0.001 sec)

obclient [oceanbase]> show proxysession  variables all 18001 like '%timeout%';
+---------------------+------------------+---------+--------------------+-----------------------------------+
| variable_name       | value            | info    | modified_type      | sys_variable_flag                 |
+---------------------+------------------+---------+--------------------+-----------------------------------+
| interactive_timeout | 28800            | sys var | cold modified vars |  && global_scope && session_scope |
| wait_timeout        | 28800            | sys var | cold modified vars |  && global_scope && session_scope |
| connect_timeout     | 10               | sys var | cold modified vars |  && global_scope                  |
| net_read_timeout    | 30               | sys var | cold modified vars |  && global_scope && session_scope |
| net_write_timeout   | 60               | sys var | cold modified vars |  && global_scope && session_scope |
| lock_wait_timeout   | 31536000         | sys var | cold modified vars |  && global_scope && session_scope |
| ob_query_timeout    | 10000000         | sys var | hot modified vars  |  && global_scope && session_scope |
| ob_trx_timeout      | 100000000        | sys var | cold modified vars |  && global_scope && session_scope |
| ob_trx_idle_timeout | 120000000        | sys var | cold modified vars |  && global_scope && session_scope |
| ob_trx_lock_timeout | -1               | sys var | cold modified vars |  && global_scope && session_scope |
| ob_pl_block_timeout | 3216672000000000 | sys var | cold modified vars |  && global_scope && session_scope |
+---------------------+------------------+---------+--------------------+-----------------------------------+
11 rows in set (0.001 sec)

只调整的会话级别ob_query_timeout参数为100s，全局参数未调整,继续测试

obclient [ZZX]> insert into t1 select * from t1;
ORA-00600: internal error code, arguments: -6210, Transaction is timeout

obclient [ZZX]> commit;
ORA-00600: internal error code, arguments: -6210, Transaction is timeout
obclient [ZZX]> rollback;
Query OK, 0 rows affected (0.000 sec)

事务超时，没法提交，只能回滚。继续测试

obclient [ZZX]> insert into t1 select * from t1;
Query OK, 4128768 rows affected (56.675 sec)
Records: 4128768  Duplicates: 0  Warnings: 0

obclient [ZZX]> commit;
Query OK, 0 rows affected (7.259 sec)

可以了。

三、ob_trx_timeout参数

obclient [ZZX]> set ob_trx_timeout=8000000;
Query OK, 0 rows affected (0.001 sec
obclient [ZZX]> set  ob_query_timeout=200000000;
Query OK, 0 rows affected (0.000 sec)

obclient [ZZX]> insert into t1 select * from t1;
ORA-00600: internal error code, arguments: -6210, Transaction is timeout

调整ob_query_timeout为200秒，ob_trx_timeout为8秒，执行一条预计200秒完成的sql，执行8秒左右报错ORA-600,OB错误码6210。

四、总结

1、ob_query_timeout控制事务中查询超时时间，3.2.4版本默认10s，超时后会导致事务超时。官方文档提示从V4.0.0 版本开始默认值由 100000000 调整为 86400000000，既24小时。3系列版本注意调整。
2、事务超时后无法commit，需要会话手工执行rollback。
3、ob_trx_timeout默认100秒，超时后依然只能rollback。官方文档提示从V4.0.0 版本开始默认值由 100000000 调整为 86400000000，既24小时。3系列版本注意调整。
4、对于月结场景注意检查该参数。