Idle transaction timed out, with ERROR 6002 reported

This topic describes how to handle ERROR 6002, where an idle transaction timed out in the MySQL mode of OceanBase Database.

Symptom

An error is returned when you query table data long after the transaction is started.

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

/* Do not perform any operation for a long period of time. */

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 codes corresponding to this error message are as follows:

• Error code: ERROR 6002

• Error code in OceanBase Database: 6278

• Error code compatible with MySQL Database: 6002

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

Possible causes

The transaction idle time, namely the execution interval between two consecutive statements, has exceeded the specified threshold. When the transaction idle time exceeds the specified threshold, the system reports an error if you perform a database operation. The idle transaction timeout period is controlled by the ob_trx_idle_timeout tenant variable. The default value is 86400000000 microseconds, namely 24 hours.

Troubleshooting procedure

Take the following steps to view and modify tenant variables, roll back the transaction by using ROLLBACK, and then proceed with your operations.

1. Check whether the transaction remains idle for a period of time longer than the threshold specified by the ob_trx_idle_timeout variable.

   The idle transaction timeout period is controlled by the ob_trx_idle_timeout tenant variable in the unit of microseconds. We recommend that you use the default value of 86400000000 microseconds, namely 24 hours.

   Note

   The following variable setting method takes effect only for the current session. To make the settings permanent, use the SET GLOBAL syntax to configure the variable, and then restart the session for the settings to take effect.

   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. Use 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 succeeds.

   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