Idle transaction timed out, with ERROR 6002 reported

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

Symptoms

After a transaction is started, an error occurs when querying 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 preceding error information corresponds to the following error codes:

• Error code: ERROR 6002

• OceanBase error code: 6278

• MySQL-compatible error code: 6002

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

Possible causes

The transaction idle timeout occurred because the interval between two database statements in the transaction exceeded the specified threshold. When the transaction idle time exceeded the specified threshold, an error occurred if you continued to 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 transactions 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 only take effect for 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 in 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