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> 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 operation>>
obclient> SELECT now(), t.* FROM t_insert t;
ERROR 6002 (25000): transaction needs rollback
obclient> RALLBACK;
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