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

说明

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