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

说明

建议变量 ob_trx_idle_timeout 使用默认值。

  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 控制，这个参数值建议使用默认值 120 秒，实际空闲会话断开的时间会是在 [100s, 100s + ob_trx_idle_timeout ] 之间。

示例：事务空闲超时报错

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

  1. obclient> DROP TABLE IF EXISTS t_insert; 
  2. Query OK, 0 rows affected (0.01 sec)
  4. obclient> CREATE TABLE t_insert(
  5.     id bigint NOT NULL PRIMARY KEY auto_increment
  6.     , name varchar(10) NOT NULL
  7.     , value bigint
  8.     ,gmt_create timestamp NOT NULL DEFAULT current_timestamp
  9. );
  10. Query OK, 0 rows affected (0.05 sec)
  12. obclient> INSERT INTO t_insert(name, value) values('CN',NULL),('UK',NULL),('US',NULL);
  13. Query OK, 3 rows affected (0.00 sec)
  14. Records: 3  Duplicates: 0  Warnings: 0
  16. obclient> select now(), * from t_insert t;
  17. +---------------------+----+------+-------+---------------------+
  18. | now()               | id | name | value | gmt_create          |
  19. +---------------------+----+------+-------+---------------------+
  20. | 2020-04-03 16:54:51 |  1 | CN   |  NULL | 2020-04-03 16:54:49 |
  21. | 2020-04-03 16:54:51 |  2 | UK   |  NULL | 2020-04-03 16:54:49 |
  22. | 2020-04-03 16:54:51 |  3 | US   |  NULL | 2020-04-03 16:54:49 |
  23. +---------------------+----+------+-------+---------------------+
  24. 3 rows in set (0.00 sec)
  26. obclient> set session ob_trx_timeout=1000000000;
  27. Query OK, 0 rows affected (0.00 sec)
  29. obclient> set session ob_trx_idle_timeout=120000000;
  30. Query OK, 0 rows affected (0.00 sec)
  32. obclient> begin;
  33. Query OK, 0 rows affected (0.00 sec)
  35. obclient> update t_insert set gmt_create=now() where id=3;
  36. Query OK, 1 row affected (0.00 sec)
  37. Rows matched: 1  Changed: 1  Warnings: 0
  39. obclient> select now(), t.* from t_insert t;
  40. +---------------------+----+------+-------+---------------------+
  41. | now()               | id | name | value | gmt_create          |
  42. +---------------------+----+------+-------+---------------------+
  43. | 2020-04-03 16:55:30 |  1 | CN   |  NULL | 2020-04-03 16:54:49 |
  44. | 2020-04-03 16:55:30 |  2 | UK   |  NULL | 2020-04-03 16:54:49 |
  45. | 2020-04-03 16:55:30 |  3 | US   |  NULL | 2020-04-03 16:55:25 |
  46. +---------------------+----+------+-------+---------------------+
  47. 3 rows in set (0.00 sec)
  49. <<等 100 秒不操作>>
  51. obclient> select now(), t.* from t_insert t;
  52. ERROR-02013: Lost connection to MySQL server during query
  53. obclient> select now(), * from t_insert t;
  54. ERROR-02006: MySQL server has gone away
  55. No connection. Trying to reconnect...
  56. Connection id:    53246
  57. Current database: TPCC
  59. +---------------------+----+------+-------+---------------------+
  60. | now()               | id | name | value | gmt_create          |
  61. +---------------------+----+------+-------+---------------------+
  62. | 2020-04-03 16:57:41 |  1 | CN   |  NULL | 2020-04-03 16:54:49 |
  63. | 2020-04-03 16:57:41 |  2 | UK   |  NULL | 2020-04-03 16:54:49 |
  64. | 2020-04-03 16:57:41 |  3 | US   |  NULL | 2020-04-03 16:54:49 |
  65. +---------------------+----+------+-------+---------------------+
  66. 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> begin;
  8. Query OK, 0 rows affected (0.00 sec)
  10. obclient> update t_insert set gmt_create=sysdate() where id=3;
  11. Query OK, 1 row affected (0.00 sec)
  12. Rows matched: 1  Changed: 1  Warnings: 0
  14. obclient> select now(), t.* from t_insert t ;
  15. +---------------------+----+------+-------+---------------------+
  16. | now()               | id | name | value | gmt_create          |
  17. +---------------------+----+------+-------+---------------------+
  18. | 2020-04-03 16:59:56 |  1 | CN   |  NULL | 2020-04-03 16:54:49 |
  19. | 2020-04-03 16:59:56 |  2 | UK   |  NULL | 2020-04-03 16:54:49 |
  20. | 2020-04-03 16:59:56 |  3 | US   |  NULL | 2020-04-03 16:59:51 |
  21. +---------------------+----+------+-------+---------------------+
  22. 3 rows in set (0.00 sec)
  24. <<等 120 秒不操作>>
  26. obclient> select now(), t.* from t_insert t ;
  27. ERROR-00600: internal error code, arguments: -6210, Transaction is timeout
  28. obclient> commit;
  29. ERROR-00600: internal error code, arguments: -6210, Transaction is timeout
  30. obclient> rollback;
  31. Query OK, 0 rows affected (0.00 sec)
  33. obclient> select now(), t.* from t_insert t ;
  34. +---------------------+----+------+-------+---------------------+
  35. | now()               | id | name | value | gmt_create          |
  36. +---------------------+----+------+-------+---------------------+
  37. | 2020-04-03 17:04:13 |  1 | CN   |  NULL | 2020-04-03 16:54:49 |
  38. | 2020-04-03 17:04:13 |  2 | UK   |  NULL | 2020-04-03 16:54:49 |
  39. | 2020-04-03 17:04:13 |  3 | US   |  NULL | 2020-04-03 16:54:49 |
  40. +---------------------+----+------+-------+---------------------+
  41. 3 rows in set (0.00 sec)

说明

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