Transaction idle timeout, error code ERROR 6002

This topic describes how to handle the ERROR 6002 error after a transaction idle timeout in OceanBase Database in MySQL mode.

Symptoms

After you start a transaction, an error occurs when you query data from a table after a long wait.

obclient [test]> SELECT * FROM ordr;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   |  NULL | 2022-04-02 14:03:31 |
|  2 | UK   |  NULL | 2022-04-02 14:03:31 |
|  3 | US   |  NULL | 2022-03-31 17:04:55 |
+----+------+-------+---------------------+
3 rows in set

obclient [test]> BEGIN;

obclient [test]> UPDATE ordr SET value=1003 WHERE id=3;
Query OK, 1 rows affected
Rows matched: 1  Changed: 1  Warnings: 0

obclient [test]> SELECT * FROM ordr;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   |  NULL | 2022-04-02 14:03:31 |
|  2 | UK   |  NULL | 2022-04-02 14:03:31 |
|  3 | US   |  1003 | 2022-03-31 17:04:55 |
+----+------+-------+---------------------+
3 rows in set

/* Wait for a long time without performing any operation*/

obclient [test]> SELECT * FROM ordr;
ERROR 6002 (25000): Transaction idle timeout occurred, please rollback the transaction, set the variable ob_trx_idle_timeout to a larger value and then restart the transaction

The error code of this error message is as follows:

• Error code: ERROR 6002

• OceanBase error code: 6278

• MySQL-compatible error code: 6002

For more information about error codes, see Overview of error codes.

Causes

The interval between two statements in a transaction exceeds the specified threshold. When the idle time of a transaction exceeds the specified threshold, an error occurs if you perform database operations. The transaction idle timeout is controlled by the tenant variable ob_trx_idle_timeout, with a default value of 86400000000 microseconds, that is, 24 hours.

Solution

Check and modify the tenant variables. Then, execute the ROLLBACK statement to roll back the transaction. After that, you can continue with your operations.

1. Query the value of the ob_trx_idle_timeout variable and check whether the timeout period of the operation exceeds the threshold.

   The idle timeout period for a transaction is specified by the tenant variable ob_trx_idle_timeout, in microseconds. The default value is 24 hours (86400000000 microseconds).

   Note

   The following methods of modifying variables are effective only in the current session. If you want the modification to take effect permanently, use the SET GLOBAL statement to modify the same variable and then restart the session.

   obclient [test]> SHOW VARIABLES LIKE 'ob_trx_idle_timeout';
   +---------------------+-----------+
   | VARIABLE_NAME       | VALUE     |
   +---------------------+-----------+
   | ob_trx_idle_timeout | 120000000 |
   +---------------------+-----------+
   1 row in set
   
   obclient [test]> SET SESSION ob_trx_idle_timeout=86400000000;
   Query OK, 0 rows affected 
   

2. Execute the ROLLBACK statement to roll back the transaction.

   obclient [test]> ROLLBACK;
   Query OK, 0 rows affected
   

3. Query the data of the table again. The query is successful.

   obclient [test]> SELECT * FROM ordr;
   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  1 | CN   |  NULL | 2022-04-02 14:03:31 |
   |  2 | UK   |  NULL | 2022-04-02 14:03:31 |
   |  3 | US   |  NULL | 2022-03-31 17:04:55 |
   +----+------+-------+---------------------+
   3 rows in set