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, 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 of 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> DROP TABLE IF EXISTS t_insert;
Query OK, 0 rows affected
obclient> CREATE TABLE t_insert(
id bigint NOT NULL PRIMARY KEY auto_increment
, name varchar(10) NOT NULL
, value bigint
,gmt_create timestamp NOT NULL DEFAULT current_timestamp
);
Query OK, 0 rows affected
obclient> INSERT INTO t_insert(name, value) VALUES('CN',NULL),('UK',NULL),('US',NULL);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT now(), t.* FROM t_insert t;
+---------------------+----+------+-------+---------------------+
| now() | id | name | value | gmt_create |
+---------------------+----+------+-------+---------------------+
| 1970-01-01 16:54:51 | 1 | CN | NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:54:51 | 2 | UK | NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:54:51 | 3 | US | NULL | 1970-01-01 16:54:49 |
+---------------------+----+------+-------+---------------------+
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=now() WHERE id=3;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
obclient> SELECT now(), t.* FROM t_insert t;
+---------------------+----+------+-------+---------------------+
| now() | id | name | value | gmt_create |
+---------------------+----+------+-------+---------------------+
| 1970-01-01 16:55:30 | 1 | CN | NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:55:30 | 2 | UK | NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:55:30 | 3 | US | NULL | 1970-01-01 16:55:25 |
+---------------------+----+------+-------+---------------------+
3 rows in set
<<Wait for 120 seconds without performing any operation>>
obclient> SELECT now(), t.* FROM t_insert t;
ERROR 6002 (25000): transaction needs rollback
obclient> ROLLBACK;
Query OK, 0 rows affected
obclient> SELECT now(), * FROM t_insert t;
+---------------------+----+------+-------+---------------------+
| now() | id | name | value | gmt_create |
+---------------------+----+------+-------+---------------------+
| 1970-01-01 16:57:43 | 1 | CN | NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:57:43 | 2 | UK | NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:57:43 | 3 | US | NULL | 1970-01-01 16:54:49 |
+---------------------+----+------+-------+---------------------+
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 SEF gmt_create=sysdate() WHERE id=3;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
obclient> SELECT now(), t.* FROM t_insert t ;
+---------------------+----+------+-------+---------------------+
| now() | id | name | value | gmt_create |
+---------------------+----+------+-------+---------------------+
| 1970-01-01 16:59:56 | 1 | CN | NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:59:56 | 2 | UK | NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:59:56 | 3 | US | NULL | 1970-01-01 16:59:51 |
+---------------------+----+------+-------+---------------------+
3 rows in set
<<Wait for 100 seconds without operation>>
obclient> SELECT now(), t.* FROM t_insert t ;
ERROR 4012 (25000): Transaction is timeout
obclient> COMMIT;
ERROR 4012 (25000): Transaction is timeout
obclient> ROLLBACK;
Query OK, 0 rows affected
obclient> SELECT now(), t.* FROM t_insert t ;
+---------------------+----+------+-------+---------------------+
| now() | id | name | value | gmt_create |
+---------------------+----+------+-------+---------------------+
| 1970-01-01 17:04:13 | 1 | CN | NULL | 1970-01-01 16:54:49 |
| 1970-01-01 17:04:13 | 2 | UK | NULL | 1970-01-01 16:54:49 |
| 1970-01-01 17:04:13 | 3 | US | NULL | 1970-01-01 16:54:49 |
+---------------------+----+------+-------+---------------------+
3 rows in set