The following sample code describes the execution of a transaction:
create table test (id1 int);
set @@ob_query_timeout=200 * 1000 * 1000;
set @@ob_trx_timeout = 200 * 1000 * 1000;
begin;
insert into test values(1);
// Wait for an idle period of 150s.
insert into test values(2); // The execution of this SQL statement returns this message: Lost connection.
select * from test; // The result of this query is empty, which means that the transaction is rolled back.
rollback;
The client reports a lost connection error when the second INSERT statement in the sample code is executed. This is because, if the interval between two SQL statements in a transaction exceeds the specified threshold, OceanBase Database forcibly kills the session, disconnects from the client, and rolls back the transaction.
This mechanism resolves the issues of long-running transactions and suspended transactions. The idle period threshold is specified by the tenant-level system variable ob_trx_idle_timeout. The default threshold is 86400000000 ms, namely 24 hours.