Transaction timed out, with ERROR 4012 reported

This topic describes how to handle ERROR 4012, where an uncommitted transaction timed out in OceanBase Database in MySQL mode.

Symptom

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

obclient [test]> BEGIN;
Query OK, 0 rows affected

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

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

obclient [test] SELECT * FROM ordr;
ERROR 4012 (25000): 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: ERROR 4012

• Error code in OceanBase Database: 6210

• MySQL error code: 4012

For more information about the error codes, see Error code overview.

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 tenant variables, 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 these variables, and then restart the session for the settings to take effect.

   obclient> SHOW VARIABLES LIKE 'ob_trx_timeout';
   +----------------+-----------+
   | Variable_name  | Value     |
   +----------------+-----------+
   | ob_trx_timeout | 100000000 |
   +----------------+-----------+
   1 row in set
   
   obclient [test]> SET SESSION ob_trx_timeout=86400000000;
   Query OK, 0 rows affected
   

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

   obclient> ROLLBACK;
   Query OK, 0 rows affected
   

3. Execute the COMMIT statement to commit the transaction.

   obclient> COMMIT;
   Query OK, 0 rows affected
   

4. Query the data in the ordr 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