The lifecycle of a transaction usually consists of the start of the transaction, the execution of queries, the execution of DML statements, and the end of the transaction. To explicitly start a transaction, you can use a BEGIN or START TRANSACTION statement. You can also use a DML statement to implicitly start the transaction. To end a transaction, you can use a COMMIT statement to commit the transaction. You can also use a ROLLBACK statement to end the transaction through rollback. In addition, the execution of a DDL statement in an active transaction implicitly commits the transaction.
You can create a savepoint in a transaction to mark a point in the transaction, and then roll back the transaction to this point using the ROLLBACK TO SAVEPOINT statement as needed during the transaction execution process. For more information about savepoints, see Savepoints.
Transaction size
In OceanBase Database V2.x, the maximum size of a transaction is usually limited to 100 MB. The size of a transaction is based on the _tenant_max_trx_size parameter in the tenant configuration and the _max_trx_size parameter in the cluster configuration. If you set both parameters, the _tenant_max_trx_size parameter prevails.
OceanBase Database V3.x supports large transactions and does not specify limits on the size of the transaction.
Statement timeout and transaction timeout
The ob_query_timeout system variable specifies the timeout period for statement execution. If a statement fails to be executed within this period, the timeout error code -6212 is returned to the application and the statement must be rolled back. In most cases, the default timeout period for statement execution is 10s.
The ob_trx_timeout system variable specifies the timeout period for transaction execution. If a transaction fails to be executed within this period, the timeout error code -6210 is returned to the application. In this case, the application must execute a ROLLBACK statement to roll back the transaction.
The ob_trx_idle_timeout system variable specifies the maximum period for a transaction in a session to stay in the IDLE state. If you do not execute a DML statement or you do not end the transaction during this period, the transaction is automatically rolled back. After this, if you execute a DML statement, the error code -6224 is returned to the application. In this case, the application must execute a ROLLBACK statement to clear the session state.
Transaction query
You can use the __all_virtual_trans_stat virtual table to query all active transactions in the system. For more information about active transactions, see Active transactions.