Transaction timed out, with Error ORA-00600 reported

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

Symptom

If a transaction remains uncommitted for a long period of time after it is started, an error is reported when you query table data.

obclient [SYS]> CREATE TABLE ordr(
    id number NOT NULL PRIMARY KEY
    , name varchar(10) NOT NULL
    , value number   
    ,gmt_create timestamp NOT NULL DEFAULT current_timestamp);   // Prepare data.                                                   
Query OK, 0 rows affected

obclient [SYS]> INSERT INTO ordr(id, name, value, gmt_create) VALUES (1,'CN',10001, current_timestamp);
Query OK, 1 row affected

obclient [SYS]> INSERT INTO ordr(id, name, value) VALUES (2,'US', 10002);
Query OK, 1 rows affected

obclient [SYS]> INSERT INTO ordr(id, name, value) VALUES (3,'EN', 10003);
Query OK, 1 rows affected

obclient [SYS]> SELECT sysdate, t.* FROM ordr t;
+-----------+----+------+-------+------------------------------+
| SYSDATE   | ID | NAME | VALUE | GMT_CREATE                   |
+-----------+----+------+-------+------------------------------+
| 05-SEP-22 |  1 | CN   | 10001 | 05-SEP-22 04.34.37.145469 PM |
| 05-SEP-22 |  2 | US   | 10002 | 05-SEP-22 04.34.46.776463 PM |
| 05-SEP-22 |  3 | EN   | 10003 | 05-SEP-22 04.34.46.776463 PM |
+-----------+----+------+-------+------------------------------+
3 rows in set

obclient [SYS]> COMMIT;

obclient [SYS]> INSERT INTO ordr(id, name, value) VALUES (4,'JP', 10004);
Query OK, 1 rows affected

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

obclient [SYS]> SELECT sysdate, t.* FROM ordr t;
ORA-00600: internal error code, arguments: -6210, Transaction timeout occurred, please rollback the transaction, set the variable ob_trx_timeout to a larger value and then restart the transaction

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

• Error code: ORA-00600

• Error code in OceanBase Database: 6210

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

Possible causes

The uncommitted transaction timeout indicates that the transaction remains uncommitted after the specified threshold has elapsed. If the transaction is not committed when the specified time has elapsed, the system reports an error. The uncommitted transaction timeout period is controlled by the ob_trx_timeout tenant variable. The default value is 86400000000 microseconds, namely 24 hours.

Troubleshooting procedure

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

1. Check whether the transaction is not committed within the period of time specified by the ob_trx_timeout tenant variable.

   The uncommitted transaction timeout period is controlled by the ob_trx_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_timeout';
   +----------------+-----------+
   | Variable_name  | Value     |
   +----------------+-----------+
   | ob_trx_timeout | 100000000 |
   +----------------+-----------+
   1 row in set
   
   obclient [SYS]> SET SESSION ob_trx_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. Execute the COMMIT statement to commit the transaction.

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

4. Query the data in the ordr table again. The query succeeds.

   obclient [SYS]> SELECT sysdate, t.* FROM ordr t;
   +-----------+----+------+-------+------------------------------+
   | SYSDATE   | ID | NAME | VALUE | GMT_CREATE                   |
   +-----------+----+------+-------+------------------------------+
   | 05-SEP-22 |  1 | CN   | 10001 | 05-SEP-22 04.34.37.145469 PM |
   | 05-SEP-22 |  2 | US   | 10002 | 05-SEP-22 04.34.46.776463 PM |
   | 05-SEP-22 |  3 | EN   | 10003 | 05-SEP-22 04.34.46.776463 PM |
   +-----------+----+------+-------+------------------------------+
   3 rows in set