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: ERROR 6002

Error code in OceanBase Database: 6002

Example

No operation is performed within 120 seconds after the BEGIN statement is executed.

obclient> delete from t_insert where id=2;
ERROR 6002 (25000): transaction needs rollback

Troubleshooting procedure

1. Check whether the transaction remains idle for a period longer than the threshold 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 roll back the transaction.

   obclient> rollback;
   Query OK, 0 rows affected
   

3. Delete data that meets the condition id=2 from the t_insert table.

   obclient> delete from t_insert where id=2;
   Query OK, 1 row affected
   

4. Check the result.

   obclient> select * from t_insert;
   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  3 | US   |  NULL | 2022-03-31 17:04:55 |
   +----+------+-------+---------------------+
   1 row 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: ERROR 4012

• Error code in OceanBase Database: 6210

• MySQL error code: 4012

Example

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

obclient> select now(), t.* from t_insert t;
ERROR 4012 (25000): Transaction is timeout
obclient> commit;
ERROR 4012 (25000): Transaction is timeout

Troubleshooting procedure

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

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

2. Execute the rollback statement to roll back the transaction.

   obclient> rollback;
   Query OK, 0 rows affected
   

3. Query the data in the t_insert table.

   obclient> select now(), t.* from t_insert t;
   +---------------------+----+------+-------+---------------------+
   | now()               | id | name | value | gmt_create          |
   +---------------------+----+------+-------+---------------------+
   | 2022-04-02 14:14:09 |  1 | CN   |  NULL | 2022-04-02 14:03:31 |
   | 2022-04-02 14:14:09 |  2 | UK   |  NULL | 2022-04-02 14:03:31 |
   | 2022-04-02 14:14:09 |  3 | US   |  NULL | 2022-03-31 17:04:55 |
   +---------------------+----+------+-------+---------------------+
   3 rows in set