Transaction timeout errors

This topic describes errors related to transaction timeout and the troubleshooting procedures.

Idle transaction timeout

When the execution interval of two statements of a transaction exceeds the specified threshold, this error is returned.

Error: ORA-24761

Error code

• Error code in OceanBase Database: 6211

• SQLSTATE: 25000

Error messages

transaction rolled back

Example

No operation is performed within 120 seconds after a transaction is started.

obclient> CREATE TABLE t_insert(
    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> INSERT INTO t_insert(id, name, value, gmt_create) VALUES (1,'CN',10001, current_timestamp);
Query OK, 1 row affected

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

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

obclient> BEGIN;      // No operation is performed within 120 seconds after the transaction is started.
Query OK, 0 rows affected

obclient> UPDATE t_insert SET t_insert.value = 10004  WHERE t_insert.id = 3;
ORA-24761: transaction rolled back: transaction needs rollback

Solutions

1. Check whether the transaction remains idle for a period longer than the threshold in microseconds specified by the ob_trx_idle_timeout parameter.

   obclient> SHOW variables like 'ob_trx_idle_timeout';
   +---------------------+-----------+
   | VARIABLE_NAME       | VALUE     |
   +---------------------+-----------+
   | ob_trx_idle_timeout | 120000000 |
   +---------------------+-----------+
   1 row in set
   

2. Execute the ROLLBACK statement to recover the transaction.

   obclient> ROLLBACK;
   Query OK, 0 rows affected
   

3. Query the data in the t_insert table.

   obclient> SELECT sysdate, t.* FROM t_insert 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
   

Uncommitted transaction timeout

When a transaction is not committed or rolled back after the specified time threshold is reached, this error is returned.

Error: ORA-00600

Error messages

internal error code, arguments: -6210, Transaction is timeout

Example

The transaction is not committed or rolled back within 200 seconds.

obclient> CREATE TABLE t_insert(
    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> INSERT INTO t_insert(id, name, value, gmt_create) VALUES (1,'CN',10001, current_timestamp);
Query OK, 1 row affected

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

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

/* Wait for 200 seconds without performing any operations.*/

obclient> SELECT sysdate, t.* FROM t_insert t;
ORA-00600: internal error code, arguments: -6210, Transaction is timeout
obclient> COMMIT;
ORA-00600: internal error code, arguments: -6210, Transaction is timeout

Solutions

1. Check whether the transaction is not committed within the period of time specified by the ob_trx_timeout parameter (unit: us).

   obclient> SHOW variables like 'ob_trx_timeout';
   +----------------+------------+
   | Variable_name  | Value      |
   +----------------+------------+
   | ob_trx_timeout | 200000000 |
   +----------------+------------+
   1 row in set
   

2. Execute the ROLLBACK statement to recover the transaction.

   obclient> ROLLBACK;
   Query OK, 0 rows affected
   

3. Execute the COMMIT statement again.

   obclient> COMMIT;
   Query OK, 0 rows affected
   

4. Query the data in the t_insert table.

   obclient> SELECT sysdate, t.* FROM t_insert 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