OceanBase Database 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.
Background
The idle transaction timeout and uncommitted transaction timeout are respectively controlled by the ob_trx_idle_timeout and ob_trx_timeout tenant variables, whose default values are 120 seconds and 100 seconds, respectively. Typically, only one timeout mechanism is triggered.
You can run the following command to view the current variable configurations:
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
Idle transaction timeout
After a statement is executed in a transaction, if the next statement is not executed within the specified period of time, the transaction is considered idle. Then OceanBase Database automatically disconnects the session and rolls back the transaction. In this case, you need to reconnect to the session.
The idle transaction timeout period is controlled by the ob_trx_idle_timeout tenant variable. We recommend that you use the default value 120 seconds.
Example: Set the idle transaction timeout period to 120 seconds and the uncommitted transaction timeout period to 1,000 seconds. After the transaction remains idle for over 120 seconds, the session is locked. In this case, you need to use ROLLBACK to roll back the transaction before proceeding to subsequent operations.
obclient> CREATE TABLE t_insert(
id number NOT NULL PRIMARY KEY,
name varchar(10) NOT NULL,
value number,
gmt_create timestamp NOT NULL DEFAULT current_timestamp
);
Query OK, 0 rows affected
obclient> INSERT INTO t_insert(id, name, value) values(1,'CN',NULL),(2,'UK',NULL),(3,'US',NULL);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT sysdate, t.* FROM t_insert t;
+-----------+----+------+-------+------------------------------+
| SYSDATE | ID | NAME | VALUE | GMT_CREATE |
+-----------+----+------+-------+------------------------------+
| 01-JAN-70 | 1 | CN | NULL | 01-JAN-70 04.45.31.513542 PM |
| 01-JAN-70 | 2 | UK | NULL | 01-JAN-70 04.45.31.513542 PM |
| 01-JAN-70 | 3 | US | NULL | 01-JAN-70 04.45.31.513542 PM |
+-----------+----+------+-------+------------------------------+
3 rows in set
obclient> SET session ob_trx_timeout=1000000000;
Query OK, 0 rows affected
obclient> SET session ob_trx_idle_timeout=120000000;
Query OK, 0 rows affected
obclient> BEGIN;
Query OK, 0 rows affected
obclient> UPDATE t_insert SET gmt_create=sysdate WHERE id=3;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
obclient> SELECT sysdate, t.* FROM t_insert t;
+-----------+----+------+-------+------------------------------+
| SYSDATE | ID | NAME | VALUE | GMT_CREATE |
+-----------+----+------+-------+------------------------------+
| 01-JAN-70 | 1 | CN | NULL | 01-JAN-70 04.45.31.513542 PM |
| 01-JAN-70 | 2 | UK | NULL | 01-JAN-70 04.45.31.513542 PM |
| 01-JAN-70 | 3 | US | NULL | 01-JAN-70 04.54.09.000000 PM |
+-----------+----+------+-------+------------------------------+
3 rows in set
<<Wait for 120 seconds without performing any operation>>
obclient> SELECT sysdate, t.* FROM t_insert t;
ORA-24761: transaction rolled back: transaction needs rollback
obclient> ROLLBACK;
Query OK, 0 rows affected
obclient> SELECT sysdate, t.* FROM t_insert t;
+-----------+----+------+-------+------------------------------+
| SYSDATE | ID | NAME | VALUE | GMT_CREATE |
+-----------+----+------+-------+------------------------------+
| 01-JAN-70 | 1 | CN | NULL | 01-JAN-70 04.45.31.513542 PM |
| 01-JAN-70 | 2 | UK | NULL | 01-JAN-70 04.45.31.513542 PM |
| 01-JAN-70 | 3 | US | NULL | 01-JAN-70 04.45.31.513542 PM |
+-----------+----+------+-------+------------------------------+
3 rows in set
Uncommitted transaction timeout
If a transaction is not committed after a specified period of time, OceanBase Database returns a timeout error. To continue executing the SQL statement, the session needs to explicitly issue a ROLLBACK statement.
The uncommitted transaction timeout period is controlled by the ob_trx_timeout tenant variable.
Example: Set the idle transaction timeout period to 120 seconds and the uncommitted transaction timeout period to 100 seconds. When a transaction remains uncommitted for 100 seconds, the transaction enters the timeout state and the lock is released. To proceed to subsequent operations, the session needs to explicitly issue a ROLLBACK statement.
Note
We recommend that you do not set the uncommitted transaction timeout period to less than 1 second.
obclient> SET session ob_trx_timeout=100000000;
Query OK, 0 rows affected
obclient> SET session ob_trx_idle_timeout=120000000;
Query OK, 0 rows affected
obclient> BEGIN;
Query OK, 0 rows affected
obclient> UPDATE t_insert SET gmt_create=sysdate WHERE id=3;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
obclient> SELECT sysdate, t.* FROM t_insert t;
+-----------+----+------+-------+------------------------------+
| SYSDATE | ID | NAME | VALUE | GMT_CREATE |
+-----------+----+------+-------+------------------------------+
| 01-JAN-70 | 1 | CN | NULL | 01-JAN-70 04.45.31.513542 PM |
| 01-JAN-70 | 2 | UK | NULL | 01-JAN-70 04.45.31.513542 PM |
| 01-JAN-70 | 3 | US | NULL | 01-JAN-70 04.59.48.000000 PM |
+-----------+----+------+-------+------------------------------+
3 rows in set
<<Wait for 100 seconds without operation>>
obclient> SELECT sysdate, t.* FROM t_insert t;
ORA-00600: internal error code, arguments: -6210, Transaction is timeout
obclient> COMMIT;
ORA-00600: internal error code, arguments: -6210, Transaction is timeout
obclient> ROLLBACK;
Query OK, 0 rows affected
obclient> SELECT sysdate, t.* FROM t_insert t;
+-----------+----+------+-------+------------------------------+
| SYSDATE | ID | NAME | VALUE | GMT_CREATE |
+-----------+----+------+-------+------------------------------+
| 01-JAN-70 | 1 | CN | NULL | 01-JAN-70 04.45.31.513542 PM |
| 01-JAN-70 | 2 | UK | NULL | 01-JAN-70 04.45.31.513542 PM |
| 01-JAN-70 | 3 | US | NULL | 01-JAN-70 04.45.31.513542 PM |
+-----------+----+------+-------+------------------------------+
3 rows in set
