Transactions

This topic describes the requirements and suggestions on transactions in OceanBase Database.

• OceanBase Database V3.x does not limit the size of large transactions. However, you must control the concurrency to prevent the drastic increase in memory usage.

  • The number of parameters in the IN list of a WHERE clause is no longer limited.

  • During the initialization of an OBServer node, write throttling is triggered when the memory usage of the MemStore reaches 80%:

    alter system set writing_throttling_trigger_percentage = 80 TENANT = ALL;

• To operate data in batches, the program must be able to suspend the processing and retry failed transactions.

• OceanBase Database commits distributed transactions in two phases. Therefore, failed transactions must be retried and the transaction execution status must be checked.

• Frontend programs cannot use SET statements other than the SET NAMES statement, such as SET TIMEZONE, SET SQL_MODE, and SET ISOLATION_LEVEL.

• A sequence is useful when you need a unique primary key. A sequence can also provide globally unique and consecutive integers.

• The number of concurrent sessions is limited.

  If a tenant has n CPUs, it usually has up to 10 x n user sessions that are active at the same time. In this case, each additional concurrent session needs to wait for an available worker thread before being active.

• We recommend that you enable automatic transaction committing by using the Connection.setAutoCommit(xx) API instead of executing the set @@autocommit=xx statement.

• We recommend that you start transactions by using the Connection.setAutoCommit(xx) API instead of executing the start transaction/begin statement.

• We recommend that you close transactions by using the Connection.commit()／Connection.rollback() API instead of executing the commit/rollback statement.