OceanBase designs two timeout mechanisms to prevent transactions from holding locks for a long time and affecting other sessions: idle transaction timeout and uncommitted transaction timeout. You use the ob_trx_idle_timeout and ob_trx_timeout variables to respectively control the timeout values in these two mechanisms. Default values: 120 seconds for idle transaction timeout and 100 seconds for uncommitted transaction timeout. Typically, only one timeout mechanism is triggered. For more information about the ob_trx_idle_timeout and ob_trx_timeout variables, see System Variables in Reference Guide (Oracle Mode). Note
We recommend that you use the default value for the ob_trx_idle_timeout variable.
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)
About idle transaction timeout
When a transaction is not committed after remaining idle for a specified period, OceanBase Database automatically disconnects the session and rolls back the transaction. In this case, you need to reconnect to the session.
The threshold value for idle transaction timeout is determined by the ob_trx_idle_timeout variable. We recommend that you use the default value of 120 seconds for this variable. A session is disconnected if it remains idle for a period between 100s and 100s + ob_trx_idle_timeout.
Example: An error occurred when the idle transaction timeout is reached
The following example sets the idle transaction timeout to 120 seconds and the uncommitted transaction timeout to 1,000 seconds. When a transaction remains idle for 120 seconds, OceanBase Database automatically disconnects the session and rolls back the transaction.
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> 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
<<Wait for 120s without operation>>
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)
About uncommitted transaction timeout
When a transaction is not committed after a specified period, OceanBase Database returns a timeout error. To continue executing the SQL statement, the session needs to issue an explicit ROLLBACK command.
The threshold value for uncommitted transaction timeout is determined by the ob_trx_timeout variable.
Example: An error occurred when the uncommitted transaction timeout is reached
The following example sets the idle transaction timeout to 120 seconds and the transaction timeout to 100 seconds. When a transaction remains uncommitted for 100 seconds, the transaction enters the timeout state and the lock is released. The session needs to explicitly issue a ROLLBACK statement.
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)
<<Wait for 100s without operation>>
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)
Note
We recommend that you do not set the uncommitted transaction timeout to less than 1 second.