关于事务超时

OceanBase 为了避免事务长时间不提交持有锁影响其他会话，设计了两个超时逻辑。一个是事务空闲超时，一个是事务未提交超时。

背景信息

OceanBase 设计的事务空闲超时和事务未提交超时，分别由租户变量 ob_trx_idle_timeout 和 ob_trx_timeout 控制，默认值分别是 120 秒和 100 秒。通常只会有一个超时机制会先被触发。

可以通过以下命令查看当前参数配置：

obclient> SHOW variables WHERE variable_name IN ('ob_trx_idle_timeout','ob_trx_timeout');
+---------------------+-----------+
| VARIABLE_NAME       | VALUE     |
+---------------------+-----------+
| ob_trx_idle_timeout | 120000000 |
| ob_trx_timeout      | 100000000 |
+---------------------+-----------+
2 rows in set
复制

事务空闲超时

OceanBase 的事务空闲时间表示超过一定时间没有执行语句或者没有执行提交，会自动断开连接并回滚事务，此时会话需要重新连接。

会话事务空闲超时时间阈值由租户变量 ob_trx_idle_timeout 控制，这个参数值建议使用默认值 120 秒。

下面示例先设置事务空闲超时时间为 120 秒，事务未提交超时时间为 1000 秒。当事务空闲时间超过 120 秒后，会话锁定，需要先通过 ROLLBACK 回滚事务，才能继续操作。

obclient> DROP TABLE IF EXISTS t_insert;
Query OK, 0 rows affected 

obclient> CREATE TABLE t_insert(
id bigint NOT NULL PRIMARY KEY auto_increment
, name varchar(10) NOT NULL
, value bigint
,gmt_create timestamp NOT NULL DEFAULT current_timestamp
);
Query OK, 0 rows affected 

obclient> INSERT INTO t_insert(name, value) VALUES('CN',NULL),('UK',NULL),('US',NULL);
Query OK, 3 rows affected 
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT now(), t.* FROM t_insert t;
+---------------------+----+------+-------+---------------------+
| now()               | id | name | value | gmt_create          |
+---------------------+----+------+-------+---------------------+
| 1970-01-01 16:54:51 |  1 | CN   |  NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:54:51 |  2 | UK   |  NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:54:51 |  3 | US   |  NULL | 1970-01-01 16:54:49 |
+---------------------+----+------+-------+---------------------+
3 rows in set 

obclient> SET session ob_trx_timeout=1000000000;
Query OK, 0 rows affected 

obclient> SET session ob_trx_idle_timeout=120000000;
Query OK, 0 rows affected 

obclient> BEGIN;
Query OK, 0 rows affected 

obclient> UPDATE t_insert SET gmt_create=now() WHERE id=3;
Query OK, 1 row affected 
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT now(), t.* FROM t_insert t;
+---------------------+----+------+-------+---------------------+
| now()               | id | name | value | gmt_create          |
+---------------------+----+------+-------+---------------------+
| 1970-01-01 16:55:30 |  1 | CN   |  NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:55:30 |  2 | UK   |  NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:55:30 |  3 | US   |  NULL | 1970-01-01 16:55:25 |
+---------------------+----+------+-------+---------------------+
3 rows in set 

<<等 120 秒不操作>>

obclient> SELECT now(), t.* FROM t_insert t;
ERROR 6002 (25000): transaction needs rollback

obclient> ROLLBACK;
Query OK, 0 rows affected 

obclient> SELECT now(), * FROM t_insert t;
+---------------------+----+------+-------+---------------------+
| now()               | id | name | value | gmt_create          |
+---------------------+----+------+-------+---------------------+
| 1970-01-01 16:57:43 |  1 | CN   |  NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:57:43 |  2 | UK   |  NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:57:43 |  3 | US   |  NULL | 1970-01-01 16:54:49 |
+---------------------+----+------+-------+---------------------+
3 rows in set
复制

事务未提交超时

OceanBase 的事务持续时间超过一段时间还没有提交，会报超时错误。此时会话需要明确发出 ROLLBACK 命令才可以继续在会话里执行 SQL。

会话事务的未提交超时时间阈值是由租户变量 ob_trx_timeout 控制。

下面示例先设置事务空闲超时时间为 120 秒，事务超时时间为 100 秒。当一个事务未提交时间持续到 100 秒时，事务内部状态就变为超时状态，同时锁会释放。此后会话需要显式发出 ROLLBACK 语句才能继续进行操作。

说明

建议不要将事务未提交超时参数设置小于 1 秒。

obclient> SET session ob_trx_timeout=100000000;
Query OK, 0 rows affected 

obclient> SET session ob_trx_idle_timeout=120000000;
Query OK, 0 rows affected 

obclient> BEGIN;
Query OK, 0 rows affected 

obclient> UPDATE t_insert SEF gmt_create=sysdate() WHERE id=3;
Query OK, 1 row affected 
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT now(), t.* FROM t_insert t ;
+---------------------+----+------+-------+---------------------+
| now()               | id | name | value | gmt_create          |
+---------------------+----+------+-------+---------------------+
| 1970-01-01 16:59:56 |  1 | CN   |  NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:59:56 |  2 | UK   |  NULL | 1970-01-01 16:54:49 |
| 1970-01-01 16:59:56 |  3 | US   |  NULL | 1970-01-01 16:59:51 |
+---------------------+----+------+-------+---------------------+
3 rows in set 

<<等 100 秒不操作>>

obclient> SELECT now(), t.* FROM t_insert t ;
ERROR 4012 (25000): Transaction is timeout
obclient> COMMIT;
ERROR 4012 (25000): Transaction is timeout
obclient> ROLLBACK;
Query OK, 0 rows affected 

obclient> SELECT now(), t.* FROM t_insert t ;
+---------------------+----+------+-------+---------------------+
| now()               | id | name | value | gmt_create          |
+---------------------+----+------+-------+---------------------+
| 1970-01-01 17:04:13 |  1 | CN   |  NULL | 1970-01-01 16:54:49 |
| 1970-01-01 17:04:13 |  2 | UK   |  NULL | 1970-01-01 16:54:49 |
| 1970-01-01 17:04:13 |  3 | US   |  NULL | 1970-01-01 16:54:49 |
+---------------------+----+------+-------+---------------------+
3 rows in set
复制