Idle transaction timed out, with Error ORA-24761 reported

This topic describes how to handle Error ORA-24761, where an idle transaction timed out in the Oracle mode of OceanBase Database.

Symptom

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

obclient [SYS]> SELECT * FROM ordr;
+----+------+-------+------------------------------+
| ID | NAME | VALUE | GMT_CREATE                   |
+----+------+-------+------------------------------+
|  1 | CN   |  NULL | 04-NOV-22 06.06.16.843024 PM |
|  2 | UK   |  NULL | 04-NOV-22 06.06.16.843024 PM |
|  3 | US   |  NULL | 04-NOV-22 06.06.16.843024 PM |
+----+------+-------+------------------------------+
3 rows in set

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

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

obclient [SYS]> SELECT * FROM ordr;
ORA-24761: transaction rolled back: transaction idle timeout

The error codes corresponding to this error message are as follows:

• Error code: ORA-24761

• Error code in OceanBase Database: 6211

• SQLSTATE: 25000

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

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

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

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

3. Query the data of the table again.

   obclient [SYS]> SELECT * FROM ordr;
   +----+------+-------+------------------------------+
   | ID | NAME | VALUE | GMT_CREATE                   |
   +----+------+-------+------------------------------+
   |  1 | CN   |  NULL | 04-NOV-22 06.06.16.843024 PM |
   |  2 | UK   |  NULL | 04-NOV-22 06.06.16.843024 PM |
   |  3 | US   |  NULL | 04-NOV-22 06.06.16.843024 PM |
   +----+------+-------+------------------------------+
   3 rows in set