OceanBase 为了避免事务长时间不提交持有锁影响其他会话,设计了两个超时逻辑。一个是事务未提交超时,一个是事务空闲超时。分别由租户变量 ob_trx_idle_timeout 和 ob_trx_timeout 控制,默认值分别是120秒和100秒。通常只会有一个超时机制被触发。
obclient> show variables where variable_name in ('ob_trx_idle_timeout','ob_trx_timeout');
+---------------------+-----------+
| VARIABLE_NAME | VALUE |
+---------------------+-----------+
| ob_trx_idle_timeout | 120000000 |
| ob_trx_timeout | 100000000 |
+---------------------+-----------+
2 rows in set (0.00 sec)
关于事务空闲超时
OceanBase 的事务空闲时间超过一段时间还没有提交时,会自动断开连接并回滚事务,此时会话需要重新连接。
会话事务空闲超时时间阈值由租户变量 ob_trx_idle_timeout 控制,这个参数值建议不要小于100秒,实际空闲会话断开的时间会是在 [100s, 100s + ob_trx_idle_timeout ] 之间。
示例:事务空闲超时报错
下面示例先设置事务空闲超时时间为120秒,事务未提交超时时间为1000秒。当事务空闲时间超过120秒后,连接会被自动断开,事务也自动被 ROLLBACK 了。
obclient> select sysdate, t.* from t_insert t;
+---------------------+----+------+-------+---------------------+
| SYSDATE | ID | NAME | VALUE | GMT_CREATE |
+---------------------+----+------+-------+---------------------+
| 2020-04-02 18:03:14 | 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2020-04-02 18:03:14 | 2 | US | 10002 | 2020-04-02 17:52:38 |
| 2020-04-02 18:03:14 | 3 | EN | 10003 | 2020-04-02 17:52:38 |
+---------------------+----+------+-------+---------------------+
3 rows in set (0.00 sec)
obclient> set session ob_trx_timeout=1000000000;
Query OK, 0 rows affected (0.00 sec)
obclient> set session ob_trx_idle_timeout=120000000;
Query OK, 0 rows affected (0.00 sec)
obclient> set session ob_trx_timeout=1000000000;
Query OK, 0 rows affected (0.00 sec)
obclient> update t_insert set gmt_create=sysdate where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
obclient> select sysdate, t.* from t_insert t;
ERROR-02013: Lost connection to MySQL server during query
obclient> select sysdate, t.* from t_insert t;
ERROR-02006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 53246
Current database: TPCC
+---------------------+----+------+-------+---------------------+
| SYSDATE | ID | NAME | VALUE | GMT_CREATE |
+---------------------+----+------+-------+---------------------+
| 2020-04-02 18:07:51 | 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2020-04-02 18:07:51 | 2 | US | 10002 | 2020-04-02 17:52:38 |
| 2020-04-02 18:07:51 | 3 | EN | 10003 | 2020-04-02 17:52:38 |
+---------------------+----+------+-------+---------------------+
3 rows in set (0.00 sec)
关于事务未提交超时
OceanBase的事务持续时间超过一段时间还没有提交,会报超时错误。此时会话需要明确发出 ROLLBACK 命令才可以继续在会话里执行SQL。
会话事务的未提交超时时间阈值是由租户变量 ob_trx_timeout 控制。
示例:事务未提交超时报错
下面示例先设置事务空闲超时时间为120秒,事务超时时间为100秒。当一个事务未提交时间持续到100秒时,事务内部状态就变为超时状态,同时锁会释放。此后会话需要显式发出 ROLLBACK 语句。
obclient> set session ob_trx_timeout=100000000;
Query OK, 0 rows affected (0.00 sec)
obclient> set session ob_trx_idle_timeout=120000000;
Query OK, 0 rows affected (0.00 sec)
obclient> update t_insert set gmt_create=sysdate where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
obclient> select sysdate, t.* from t_insert t ;
+---------------------+----+------+-------+---------------------+
| SYSDATE | ID | NAME | VALUE | GMT_CREATE |
+---------------------+----+------+-------+---------------------+
| 2020-04-02 18:08:54 | 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2020-04-02 18:08:54 | 2 | US | 10002 | 2020-04-02 17:52:38 |
| 2020-04-02 18:08:54 | 3 | EN | 10003 | 2020-04-02 18:08:47 |
+---------------------+----+------+-------+---------------------+
3 rows in set (0.01 sec)
obclient> select sysdate, t.* from t_insert t ;
ERROR-00600: internal error code, arguments: -6210, Transaction is timeout
obclient> commit;
ERROR-00600: internal error code, arguments: -6210, Transaction is timeout
obclient> rollback;
Query OK, 0 rows affected (0.00 sec)
obclient> select sysdate, t.* from t_insert t ;
+---------------------+----+------+-------+---------------------+
| SYSDATE | ID | NAME | VALUE | GMT_CREATE |
+---------------------+----+------+-------+---------------------+
| 2020-04-02 18:09:21 | 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2020-04-02 18:09:21 | 2 | US | 10002 | 2020-04-02 17:52:38 |
| 2020-04-02 18:09:21 | 3 | EN | 10003 | 2020-04-02 17:52:38 |
+---------------------+----+------+-------+---------------------+
3 rows in set (0.00 sec)
注意:
建议不要将事务未提交超时参数设置小于1秒。