本文主要介绍 OBClient 客户端工具正常退出 OceanBase 数据库 Oracle 模式的活跃会话时会自动提交当前会话中尚未提交的事务的特性，以及相关的控制开关。
详细说明
原生 Oracle 的 SQLPLUS 客户端命令在正常退出会话时会自动提交该会话中尚未提交的事务，而 OceanBase 数据库 Oracle 模式的 OBClient 客户端工具正常退出会话时也会自动提交事物。不过在小于 OBClient V2.2.6 的低版本上无法关闭该行为。而 Oracle 的 SQLPLUS 有退出自动提交功能，行为默认开，可以通过设置 SET EXITCOMMIT OFF; 来关闭。因此从 OBClient V2.2.6 版本开始，提供了一个兼容 Oracle 的 SQLPLUS 的开关：
SET EXITC[OMMIT] {ON | OFF}
指定默认 EXIT 行为是 COMMIT 还是 ROLLBACK。 默认设置为 ON，这意味着工作将在退出时提交，无论您是否希望提交它。将 EXITCOMMIT 设置为 OFF 以在退出时自动回滚工作。
备注：OBClient V2.2.6 版本引入。
测试验证
正常退出 OceanBase 数据库 Oracle 模式的会话时，OBClient 工具会自动提交尚未提交的事务
[admin@localhost /home/admin]$ obclient -hxx.xxx.80.111 -P2881 -usys@oracle -pxxx -A -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221513182
Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(SYS@oracle)[SYS]> select @@autocommit from dual;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 0 |
+--------------+
1 row in set (0.001 sec)
obclient(SYS@oracle)[SYS]> desc t1;
+-------+---------------+------+------+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+---------------+------+------+---------+-------+
| NAME | VARCHAR2(100) | YES | NULL | NULL | NULL |
+-------+---------------+------+------+---------+-------+
1 row in set (0.003 sec)
obclient(SYS@oracle)[SYS]> select * from t1;
Empty set (0.000 sec)
obclient(SYS@oracle)[SYS]> insert into t1 values ('abc');
Query OK, 1 row affected (0.004 sec)
obclient(SYS@oracle)[SYS]> Bye
[admin@localhost /home/admin]$ obclient -hxx.xxx.80.111 -P2881 -usys@oracle -pxxx -A -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221511822
Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(SYS@oracle)[SYS]> select * from t1;
+------+
| NAME |
+------+
| abc |
+------+
1 row in set (0.001 sec)
注意：OceanBase 数据库 MySQL 模式无此特性。示例如下。
[admin@localhost /home/admin]$ obclient -hxx.xxx.80.111 -P2881 -uroot@mysqlt -pxxx -A -c test
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221553279
Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(root@mysqlt)[test]> set session autocommit=off;
Query OK, 0 rows affected (0.001 sec)
obclient(root@mysqlt)[test]> desc t1;
+-------+--------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+------+---------+-------+
| name | varchar(100) | YES | | NULL | |
+-------+--------------+------+------+---------+-------+
1 row in set (0.003 sec)
obclient(root@mysqlt)[test]> select * from t1;
Empty set (0.000 sec)
obclient(root@mysqlt)[test]> insert into t1 values ('abc');
Query OK, 1 row affected (0.000 sec)
obclient(root@mysqlt)[test]> Bye
[admin@localhost /home/admin]$ obclient -hxx.xxx.80.111 -P2881 -uroot@mysqlt -pxxx -A -c test
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221548755
Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(root@mysqlt)[test]> select * from t1;
Empty set (0.000 sec)
OBClient V2.2.6 及更高版本提供了一个开关 SET EXITC[OMMIT] {ON | OFF} 来控制此行为
[admin@localhost /home/admin]$ obclient --version
obclient Ver 2.2.10 Distrib 10.4.18-MariaDB, for Linux (x86_64) using readline 5.1
[admin@localhost /home/admin]$ obclient -hxx.xxx.80.111 -P2881 -usys@oracle -pxxx -A -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221605710
Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(SYS@oracle)[SYS]> set exitcommit off;
obclient(SYS@oracle)[SYS]> select @@autocommit from dual;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 0 |
+--------------+
1 row in set (0.000 sec)
obclient(SYS@oracle)[SYS]> desc t1;
+-------+---------------+------+------+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+---------------+------+------+---------+-------+
| NAME | VARCHAR2(100) | YES | NULL | NULL | NULL |
+-------+---------------+------+------+---------+-------+
1 row in set (0.003 sec)
obclient(SYS@oracle)[SYS]> select * from t1;
+------+
| NAME |
+------+
| abc |
+------+
1 row in set (0.000 sec)
obclient(SYS@oracle)[SYS]> insert into t1 values ('def');
Query OK, 1 row affected (0.000 sec)
obclient(SYS@oracle)[SYS]> Bye
[admin@localhost /home/admin]$ obclient -hxx.xxx.80.111 -P2881 -usys@oracle -pxxx -A -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 3221599787
Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient(SYS@oracle)[SYS]> select * from t1;
+------+
| NAME |
+------+
| abc |
+------+
1 row in set (0.000 sec)
如果 OBClient 客户端进程被 Kill 掉，未提交事务不会自动提交
通过 Terminal 1 连接 OBClient 执行 SQL。
[admin@localhost /home/admin]$ obclient -hxx.xxx.80.111 -P2881 -usys@oracle -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221627056 Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient(SYS@oracle)[SYS]> set exitcommit on; obclient(SYS@oracle)[SYS]> select @@autocommit from dual; +--------------+ | @@AUTOCOMMIT | +--------------+ | 0 | +--------------+ 1 row in set (0.000 sec) obclient(SYS@oracle)[SYS]> desc t1; +-------+---------------+------+------+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +-------+---------------+------+------+---------+-------+ | NAME | VARCHAR2(100) | YES | NULL | NULL | NULL | +-------+---------------+------+------+---------+-------+ 1 row in set (0.003 sec) obclient(SYS@oracle)[SYS]> select * from t1; +------+ | NAME | +------+ | abc | +------+ 1 row in set (0.000 sec) obclient(SYS@oracle)[SYS]> set ob_trx_timeout=60000000; Query OK, 0 rows affected (0.000 sec) obclient(SYS@oracle)[SYS]> insert into t1 values ('def'); Query OK, 1 row affected (0.000 sec) -- 这边在 Terminal 2 将 obclient客户端进程 kill 掉 obclient(SYS@oracle)[SYS]> Killed在 INSERT 语句执行结束后，通过 Terminal 2 Kill 掉 OBClient 客户端进程。
[admin@localhost /home/admin]$ ps -ef | grep obclient admin 121137 31943 0 17:41 pts/0 00:00:00 obclient -hxx.xxx.80.111 -P2881 -usys@oracle -px xxxxxxxxx -A -c admin 123774 128686 0 17:42 pts/2 00:00:00 grep --color=auto obclient [admin@localhost /home/admin]$ kill -9 121137再次连接 OBClient，检查发现未提交事务被自动回滚了。
[admin@localhost /home/admin]$ obclient -hxx.xxx.80.111 -P2881 -usys@oracle -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221543723 Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient(SYS@oracle)[SYS]> select * from t1; +------+ | NAME | +------+ | abc | +------+ 1 row in set (0.000 sec)备注：通过
kill -15SIGTERM 信号杀掉 OBClient 客户端进程后，效果跟kill -9SIGKILL 信号一样，未提交事务均会被自动回滚。
如果 OBClient 客户端网络连接被断开了，未提交事务不会自动提交
通过 Terminal 1 连接 OBClient 执行SQL。
[admin@localhost /home/admin]$ echo $$ 31943 [admin@localhost /home/admin]$ obclient -hxx.xxx.80.111 -P2881 -usys@oracle -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221498244 Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient(SYS@oracle)[SYS]> set exitcommit on; obclient(SYS@oracle)[SYS]> select @@autocommit from dual; +--------------+ | @@AUTOCOMMIT | +--------------+ | 0 | +--------------+ 1 row in set (0.000 sec) obclient(SYS@oracle)[SYS]> desc t1; +-------+---------------+------+------+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +-------+---------------+------+------+---------+-------+ | NAME | VARCHAR2(100) | YES | NULL | NULL | NULL | +-------+---------------+------+------+---------+-------+ 1 row in set (0.003 sec) obclient(SYS@oracle)[SYS]> select * from t1; +------+ | NAME | +------+ | abc | +------+ 1 row in set (0.000 sec) obclient(SYS@oracle)[SYS]> set ob_trx_timeout=60000000; Query OK, 0 rows affected (0.000 sec) obclient(SYS@oracle)[SYS]> insert into t1 values ('def'); Query OK, 1 row affected (0.000 sec) -- 这边在 Terminal 2 将 obclient客户端网络连接断开 obclient(SYS@oracle)[SYS]> ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── Session stopped - Press <Return> to exit tab - Press R to restart session - Press S to save terminal output to file在 INSERT 语句执行结束后，通过 Terminal 2 Kill 掉 OBClient 客户端的网络连接。
[admin@localhost /home/admin]$ ps -ef | grep 31943 admin 31943 31924 0 10:39 pts/0 00:00:00 -bash admin 33961 31943 0 17:59 pts/0 00:00:00 obclient -hxx.xxx.80.111 -P2881 -usys@oracle -px xxxxxxxxx -A -c admin 36607 128686 0 18:00 pts/2 00:00:00 grep --color=auto 31943 [admin@localhost /home/admin]$ kill -9 31943 [admin@localhost /home/admin]$ ps -ef | grep 31943 admin 47529 128686 0 18:04 pts/2 00:00:00 grep --color=auto 31943 [admin@localhost /home/admin]$ ps -ef | grep obclient admin 47634 128686 0 18:04 pts/2 00:00:00 grep --color=auto obclient重新连接 OBClient，检查发现未提交事务被自动回滚了。
[admin@localhost /home/admin]$ obclient -hxx.xxx.80.111 -P2881 -usys@oracle -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221548944 Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient(SYS@oracle)[SYS]> select * from t1; +------+ | NAME | +------+ | abc | +------+ 1 row in set (0.000 sec)
适用版本
OBClient 客户端工具所有版本。
OceanBase 数据库所有版本。
