OceanBase 为了避免事务长时间不提交持有锁影响其他会话，设计了两个超时逻辑。一个是事务未提交超时，一个是事务空闲超时。分别由租户变量 ob_trx_idle_timeout 和 ob_trx_timeout 控制，默认值分别是120秒和100秒。通常只会有一个超时机制被触发。

  1. obclient> show variables where variable_name in ('ob_trx_idle_timeout','ob_trx_timeout');
  2. +---------------------+-----------+
  3. | VARIABLE_NAME       | VALUE     |
  4. +---------------------+-----------+
  5. | ob_trx_idle_timeout | 120000000 |
  6. | ob_trx_timeout      | 100000000 |
  7. +---------------------+-----------+
  8. 2 rows in set (0.00 sec)

关于事务空闲超时

OceanBase 的事务空闲时间超过一段时间还没有提交时，会自动断开连接并回滚事务，此时会话需要重新连接。

会话事务空闲超时时间阈值由租户变量 ob_trx_idle_timeout 控制，这个参数值建议不要小于100秒，实际空闲会话断开的时间会是在 [100s, 100s + ob_trx_idle_timeout ] 之间。

示例：事务空闲超时报错

下面示例先设置事务空闲超时时间为120秒，事务未提交超时时间为1000秒。当事务空闲时间超过120秒后，连接会被自动断开，事务也自动被 ROLLBACK 了。

  1. obclient> select sysdate, t.* from t_insert t;
  2. +---------------------+----+------+-------+---------------------+
  3. | SYSDATE             | ID | NAME | VALUE | GMT_CREATE          |
  4. +---------------------+----+------+-------+---------------------+
  5. | 2020-04-02 18:03:14 |  1 | CN   | 10001 | 2020-04-02 17:52:31 |
  6. | 2020-04-02 18:03:14 |  2 | US   | 10002 | 2020-04-02 17:52:38 |
  7. | 2020-04-02 18:03:14 |  3 | EN   | 10003 | 2020-04-02 17:52:38 |
  8. +---------------------+----+------+-------+---------------------+
  9. 3 rows in set (0.00 sec)
  11. obclient> set session ob_trx_timeout=1000000000;
  12. Query OK, 0 rows affected (0.00 sec)
  14. obclient> set session ob_trx_idle_timeout=120000000;
  15. Query OK, 0 rows affected (0.00 sec)
  17. obclient> set session ob_trx_timeout=1000000000;
  18. Query OK, 0 rows affected (0.00 sec)
  20. obclient> update t_insert set gmt_create=sysdate where id=3;
  21. Query OK, 1 row affected (0.00 sec)
  22. Rows matched: 1  Changed: 1  Warnings: 0
  24. obclient> select sysdate, t.* from t_insert t;
  25. ERROR-02013: Lost connection to MySQL server during query
  26. obclient> select sysdate, t.* from t_insert t;
  27. ERROR-02006: MySQL server has gone away
  28. No connection. Trying to reconnect...
  29. Connection id:    53246
  30. Current database: TPCC
  32. +---------------------+----+------+-------+---------------------+
  33. | SYSDATE             | ID | NAME | VALUE | GMT_CREATE          |
  34. +---------------------+----+------+-------+---------------------+
  35. | 2020-04-02 18:07:51 |  1 | CN   | 10001 | 2020-04-02 17:52:31 |
  36. | 2020-04-02 18:07:51 |  2 | US   | 10002 | 2020-04-02 17:52:38 |
  37. | 2020-04-02 18:07:51 |  3 | EN   | 10003 | 2020-04-02 17:52:38 |
  38. +---------------------+----+------+-------+---------------------+
  39. 3 rows in set (0.00 sec)

关于事务未提交超时

OceanBase的事务持续时间超过一段时间还没有提交，会报超时错误。此时会话需要明确发出 ROLLBACK 命令才可以继续在会话里执行SQL。

会话事务的未提交超时时间阈值是由租户变量 ob_trx_timeout 控制。

示例：事务未提交超时报错

下面示例先设置事务空闲超时时间为120秒，事务超时时间为100秒。当一个事务未提交时间持续到100秒时，事务内部状态就变为超时状态，同时锁会释放。此后会话需要显式发出 ROLLBACK 语句。

  1. obclient> set session ob_trx_timeout=100000000;
  2. Query OK, 0 rows affected (0.00 sec)
  4. obclient> set session ob_trx_idle_timeout=120000000;
  5. Query OK, 0 rows affected (0.00 sec)
  7. obclient> update t_insert set gmt_create=sysdate where id=3;
  8. Query OK, 1 row affected (0.00 sec)
  9. Rows matched: 1  Changed: 1  Warnings: 0
  11. obclient> select sysdate, t.* from t_insert t ;
  12. +---------------------+----+------+-------+---------------------+
  13. | SYSDATE             | ID | NAME | VALUE | GMT_CREATE          |
  14. +---------------------+----+------+-------+---------------------+
  15. | 2020-04-02 18:08:54 |  1 | CN   | 10001 | 2020-04-02 17:52:31 |
  16. | 2020-04-02 18:08:54 |  2 | US   | 10002 | 2020-04-02 17:52:38 |
  17. | 2020-04-02 18:08:54 |  3 | EN   | 10003 | 2020-04-02 18:08:47 |
  18. +---------------------+----+------+-------+---------------------+
  19. 3 rows in set (0.01 sec)
  21. obclient> select sysdate, t.* from t_insert t ;
  22. ERROR-00600: internal error code, arguments: -6210, Transaction is timeout
  23. obclient> commit;
  24. ERROR-00600: internal error code, arguments: -6210, Transaction is timeout
  25. obclient> rollback;
  26. Query OK, 0 rows affected (0.00 sec)
  28. obclient> select sysdate, t.* from t_insert t ;
  29. +---------------------+----+------+-------+---------------------+
  30. | SYSDATE             | ID | NAME | VALUE | GMT_CREATE          |
  31. +---------------------+----+------+-------+---------------------+
  32. | 2020-04-02 18:09:21 |  1 | CN   | 10001 | 2020-04-02 17:52:31 |
  33. | 2020-04-02 18:09:21 |  2 | US   | 10002 | 2020-04-02 17:52:38 |
  34. | 2020-04-02 18:09:21 |  3 | EN   | 10003 | 2020-04-02 17:52:38 |
  35. +---------------------+----+------+-------+---------------------+
  36. 3 rows in set (0.00 sec)

说明

建议不要将事务未提交超时参数设置小于1秒。