關于事務超時 · k8s原理與實踐

[TOC] # 關于事務超時 OceanBase 為了避免事務長時間不提交持有鎖影響其他會話，設計了兩個超時邏輯。一個是事務空閑超時，一個是事務未提交超時。分別由租戶變量 ob\_trx\_idle\_timeout 和 ob\_trx\_timeout 控制，默認值分別是 120 秒和 100 秒。通常只會有一個超時機制被觸發。 **說明** 建議變量 ob\_trx\_idle\_timeout 使用默認值。 ~~~ 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 (0.00 sec) ~~~ ## 關于事務空閑超時 OceanBase 的事務空閑時間超過一段時間還沒有提交時，會自動斷開連接并回滾事務，此時會話需要重新連接。會話事務空閑超時時間閾值由租戶變量 ob\_trx\_idle\_timeout 控制，這個參數值建議使用默認值 120 秒，實際空閑會話斷開的時間會是在 \[100s, 100s + ob\_trx\_idle\_timeout \] 之間。 #### 示例：事務空閑超時報錯下面示例先設置事務空閑超時時間為 120 秒，事務未提交超時時間為 1000 秒。當事務空閑時間超過 120 秒后，連接會被自動斷開，事務也自動被 ROLLBACK 了。 ~~~ obclient> DROP TABLE IF EXISTS t_insert; Query OK, 0 rows affected (0.01 sec) 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 (0.05 sec) obclient> INSERT INTO t_insert(name, value) values('CN',NULL),('UK',NULL),('US',NULL); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 obclient> select now(), * from t_insert t; +---------------------+----+------+-------+---------------------+ | now() | id | name | value | gmt_create | +---------------------+----+------+-------+---------------------+ | 2020-04-03 16:54:51 | 1 | CN | NULL | 2020-04-03 16:54:49 | | 2020-04-03 16:54:51 | 2 | UK | NULL | 2020-04-03 16:54:49 | | 2020-04-03 16:54:51 | 3 | US | NULL | 2020-04-03 16:54:49 | +---------------------+----+------+-------+---------------------+ 3 rows in set (0.00 sec) obclient> set session ob_trx_timeout=1000000000; Query OK, 0 rows affected (0.00 sec) obclient> set session ob_trx_idle_timeout=120000000; Query OK, 0 rows affected (0.00 sec) obclient> begin; Query OK, 0 rows affected (0.00 sec) obclient> update t_insert set gmt_create=now() where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 obclient> select now(), t.* from t_insert t; +---------------------+----+------+-------+---------------------+ | now() | id | name | value | gmt_create | +---------------------+----+------+-------+---------------------+ | 2020-04-03 16:55:30 | 1 | CN | NULL | 2020-04-03 16:54:49 | | 2020-04-03 16:55:30 | 2 | UK | NULL | 2020-04-03 16:54:49 | | 2020-04-03 16:55:30 | 3 | US | NULL | 2020-04-03 16:55:25 | +---------------------+----+------+-------+---------------------+ 3 rows in set (0.00 sec) <<等 100 秒不操作>> obclient> select now(), t.* from t_insert t; ERROR-02013: Lost connection to MySQL server during query obclient> select now(), * from t_insert t; ERROR-02006: MySQL server has gone away No connection. Trying to reconnect... Connection id: 53246 Current database: TPCC +---------------------+----+------+-------+---------------------+ | now() | id | name | value | gmt_create | +---------------------+----+------+-------+---------------------+ | 2020-04-03 16:57:41 | 1 | CN | NULL | 2020-04-03 16:54:49 | | 2020-04-03 16:57:41 | 2 | UK | NULL | 2020-04-03 16:54:49 | | 2020-04-03 16:57:41 | 3 | US | NULL | 2020-04-03 16:54:49 | +---------------------+----+------+-------+---------------------+ 3 rows in set (0.00 sec) ~~~ ## 關于事務未提交超時 OceanBase 的事務持續時間超過一段時間還沒有提交，會報超時錯誤。此時會話需要明確發出 ROLLBACK 命令才可以繼續在會話里執行 SQL。會話事務的未提交超時時間閾值是由租戶變量 ob\_trx\_timeout 控制。 #### 示例：事務未提交超時報錯下面示例先設置事務空閑超時時間為 120 秒，事務超時時間為 100 秒。當一個事務未提交時間持續到 100 秒時，事務內部狀態就變為超時狀態，同時鎖會釋放。此后會話需要顯式發出 ROLLBACK 語句。 ~~~ obclient> set session ob_trx_timeout=100000000; Query OK, 0 rows affected (0.00 sec) obclient> set session ob_trx_idle_timeout=120000000; Query OK, 0 rows affected (0.00 sec) obclient> begin; Query OK, 0 rows affected (0.00 sec) obclient> update t_insert set gmt_create=sysdate() where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 obclient> select now(), t.* from t_insert t ; +---------------------+----+------+-------+---------------------+ | now() | id | name | value | gmt_create | +---------------------+----+------+-------+---------------------+ | 2020-04-03 16:59:56 | 1 | CN | NULL | 2020-04-03 16:54:49 | | 2020-04-03 16:59:56 | 2 | UK | NULL | 2020-04-03 16:54:49 | | 2020-04-03 16:59:56 | 3 | US | NULL | 2020-04-03 16:59:51 | +---------------------+----+------+-------+---------------------+ 3 rows in set (0.00 sec) <<等 120 秒不操作>> obclient> select now(), t.* from t_insert t ; ERROR-00600: internal error code, arguments: -6210, Transaction is timeout obclient> commit; ERROR-00600: internal error code, arguments: -6210, Transaction is timeout obclient> rollback; Query OK, 0 rows affected (0.00 sec) obclient> select now(), t.* from t_insert t ; +---------------------+----+------+-------+---------------------+ | now() | id | name | value | gmt_create | +---------------------+----+------+-------+---------------------+ | 2020-04-03 17:04:13 | 1 | CN | NULL | 2020-04-03 16:54:49 | | 2020-04-03 17:04:13 | 2 | UK | NULL | 2020-04-03 16:54:49 | | 2020-04-03 17:04:13 | 3 | US | NULL | 2020-04-03 16:54:49 | +---------------------+----+------+-------+---------------------+ 3 rows in set (0.00 sec) ~~~ **說明** 建議不要將事務未提交超時參數設置小于 1 秒。