首页 » MySQL, OceanBase, ORACLE 9i-23c, PostgreSQL/GaussDB, 其它国产库, 达梦 » Oracle、Kingbase、OceanBase、TIDB、达梦数据库比较系列(十八): for update nowait 报错信息可读性

Oracle、Kingbase、OceanBase、TIDB、达梦数据库比较系列(十八): for update nowait 报错信息可读性

前几天我写了一篇《Oceanbase不建议模仿Oracle的错误编号(ORA-NNNNN)！》，出乎意料的是，这篇文章在公众号上的阅读量非常高。一个知名的网红公众号引用了我的文章，并起了一个吸引眼球的标题《真狠！！！OceanBase被骂惨了！！！》，到目前为止，阅读量已经接近3万, 这让我深感惊讶。我撰写技术博客已有十余年，但始终未能获得太多关注。更有些人将我网站上的博客内容利用小拇指与食指舞动2下搬到CSDN，因为百度的排名引流比我高出好几百倍。另外我一直有一个偏见，觉得微信公众号不适合写技术类文章，例如实验代码格式、搜索引擎的收录、手机屏幕与大篇幅阅读性差等，但如果只是写一些观点的小作文除外。然而，不得不感叹的是，技术用户已经转移到了移动互联网终端。

回到这篇文章，我的初衷并不是要抨击OB，而是希望它能变得更好。比如，在排查故障时，是否能够通过错误识别来区分问题。前几天看到有OB用户留言，提到OceanBase很可能是出于对他们需求的考虑增加的设计，因为他们的应用中有以前对ORACLE报错的依赖。这表明现在数据库厂家在满足各种甲方要求时也颇为无奈，在应用的兼容性上做了种种让步。

在Oracle数据库版本迭代中相同的错误代码，除了资源类限制可能报错信息变化外，Oracle在报错信息的可读性上也是下足了功夫，比如：

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
在23c以后阅读性更加友好
ORA-12514: Cannot connect to database. Service ANBOBpdb12 is not registered with the listener at host 172.20.xx.166 port 1521.

Oracle甚至还上线了Error Help网站官方公开一些处理的方法，当然没有MOS中那么具体. 如 https://docs.oracle.com/en/error-help/db/ora-12514/,  这些都是国产数据库可以参考的方向。 当然如果OB可以迭代和ORACLE一样的错误代码并加以特殊标记，分析思路可以复用oracle还能区分报错数据源更合适。

就对于会话1事务中，会话2 select for update nowait相同的报错场景，我简单测试一下在其它国产库上是否还不如Oceanbase. 为了方便横向对比，这里我再简单的附上ORACLE 与OB的报错。

oracle 23c

SQL> select BANNER from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Beta

# session 1
SQL> create table test1(id int);
Table created.

SQL> insert into test1 values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> update test1 set id=2;
1 row updated.

# session 2
SQL> select * from test1 for update nowait;
select * from test1 for update nowait
              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> alter table test1 add name varchar2(20);
-- wait 
-- ctrl +c
^Calter table test1 add name varchar2(20)
*
ERROR at line 1:
ORA-01013: User requested cancel of current operation.

Oceanbase

Server version: OceanBase 3.2.4.1 

# session 1
obclient [ANBOB]> alter session set autocommit=0;     -- OB for oracle default autocommit off
Query OK, 0 rows affected (0.002 sec)

obclient [ANBOB]> create table test101(id int);
obclient [ANBOB]> insert into test101 values(100);

obclient [ANBOB]> update test101 set id=2;
Query OK, 1 row affected (0.018 sec)

# session 2
obclient [ANBOB]>  alter session set autocommit=0;
obclient [ANBOB]> select * from test101 for update nowait;
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired    

obclient [ANBOB]> alter table test101 add name varchar2(10);
Query OK, 0 rows affected (0.064 sec)

obclient [ANBOB]> desc test101;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| ID    | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
| NAME  | VARCHAR2(10) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+

# session 1
obclient [ANBOB]> desc test101;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| ID    | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
| NAME  | VARCHAR2(10) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.005 sec)

Note：
Oceanbase 在线DDL 并不会被DML事务堵塞，并且事务中的会话在结束当前的事务前，就可以查到变化后的表结构， 这点与TIDB不同.

TIDB

Your MySQL connection id is 473
Server version: 5.7.25-TiDB-v5.4.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

mysql>  SET autocommit=0;
Query OK, 0 rows affected (0.04 sec)

mysql> create table test1(id int);
Query OK, 0 rows affected (0.18 sec)

mysql> insert into test1 values(1);
Query OK, 1 row affected (0.12 sec)

mysql> update test1 set id=3;
Query OK, 0 rows affected (0.04 sec)

# session 2
mysql> select * from test1 for update nowait;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
mysql>

mysql> select * from test1 for update;
-- waiting 
-- timeout
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# session 3
mysql> show  processlist;
+------+------+--------------------+------+---------+------+----------------+--------------------------------+
| Id   | User | Host               | db   | Command | Time | State          | Info                           |
+------+------+--------------------+------+---------+------+----------------+--------------------------------+
|  471 | root | 172.20.xx.253:4810 | NULL | Query   |    0 | autocommit     | show  processlist              |
|  473 | root | 172.20.xx.253:4832 | test | Query   |   12 | in transaction | select * from test1 for update |
|  475 | root | 172.20.xx.253:4836 | test | Sleep   |   71 | in transaction | NULL                           |
+------+------+--------------------+------+---------+------+----------------+--------------------------------+
3 rows in set (0.04 sec)

# session 2
mysql> alter table test1 add name varchar(20);
Query OK, 0 rows affected (0.32 sec)
--- online DDL
mysql> desc test1;
+-------+-------------+------+------+---------+-------+
| Field | Type        | Null | Key  | Default | Extra |
+-------+-------------+------+------+---------+-------+
| id    | int(11)     | YES  |      | NULL    |       |
| name  | varchar(20) | YES  |      | NULL    |       |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.12 sec)

#  session 1
mysql> desc test1;
+-------+---------+------+------+---------+-------+
| Field | Type    | Null | Key  | Default | Extra |
+-------+---------+------+------+---------+-------+
| id    | int(11) | YES  |      | NULL    |       |
+-------+---------+------+------+---------+-------+
1 row in set (0.04 sec)

Note:
TIDB同样DDL不会被DML事务堵塞，但是事务中的SESSION 1 在SESSION 2做完DDL后，看到的表结构还是事务开始时的样子，而OB是直接可以读取到。 哪家更合理？自行判断。
TIDB是MySQL解析不再演示MySQL报错。

达梦dameng

SQL> select * from v$version;

行号     BANNER
---------- ---------------------------------
1          DM Database Server 64 V8
2          DB Version: 0x7000c
3          03134283914-20221207-176225-20009

# session 1
SQL> create table test1(id int);
操作已执行
已用时间: 17.681(毫秒). 执行号:143129801.
SQL> insert into test1 values(1);
影响行数 1

已用时间: 1.414(毫秒). 执行号:143129802.
SQL> commit;
操作已执行
已用时间: 1.128(毫秒). 执行号:143129803.
SQL> update test1 set id=2;
影响行数 1
已用时间: 0.735(毫秒). 执行号:143129804.

# session 2
[dmdba@template ~]$ disql sysdba/root123
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 2.983(ms)
disql V8
SQL> select * from test1;
行号     id
---------- -----------
1          1

已用时间: 2.753(毫秒). 执行号:143131100.
SQL> select * from test1 for update nowait;
select * from test1 for update nowait;
[-6409]:锁等待.
已用时间: 3.940(毫秒). 执行号:0.
SQL> select * from test1 for update;
^C^C
^C
-- 挂死了

# session 3
SQL> select sess_id,substr(sql_text,1,40),STATE,RUN_STATUS from v$sessions;
行号     sess_id              substr(sql_text,1,40)                    STATE  RUN_STATUS
---------- -------------------- ---------------------------------------- ------ ----------
..
2          209692816            update test1 set id=2;                   IDLE   IDLE
3          140595797803360      select * from test1 for update;          WAIT   RUNNING
..
6          196059984            select sess_id,substr(sql_text,1,40),STA ACTIVE RUNNING

SQL> alter table test1 add name varchar2(10);
^C
alter table test1 add name varchar2(10);
[-6407]:锁超时.

Note:
达梦的报错就过于简洁，像他的执行计划显示一样，吐槽一下，可读性不够友好。

人大金仓KINGBASE
–PostgreSQL的其中代表

$ ksql -h 172.xx.xxx.49 -U system -W
Password:
ksql: error: could not connect to server: FATAL:  sorry, too many clients already
[kingbase@kingbase1 ~]$ ps -ef|grep kingbase|grep -i idle
kingbase  7270 29215  0 11:49 ?        00:00:02 kingbase: system test 172.xx.xxx.4(24355) idle
kingbase 16027 29215  0 12:34 ?        00:00:00 kingbase: system kingbase 172.xx.xxx.4(44540) idle
kingbase 16819 29215  0 12:35 ?        00:00:01 kingbase: system kingbase 172.xx.xxx.4(45010) idle
kingbase 17618 29215  0 12:35 ?        00:00:00 kingbase: system test 172.xx.xxx.4(46298) idle
kingbase 18127 29215  0  2023 ?        00:44:12 kingbase: esrep esrep 172.xx.xxx.49(23808) idle
kingbase 18135 29215  0  2023 ?        00:22:45 kingbase: esrep esrep 172.xx.xxx.50(39580) idle
kingbase 19257 29215  0 12:36 ?        00:00:00 kingbase: system test 172.xx.xxx.4(48146) idle
kingbase 22622 29215  0 12:39 ?        00:00:00 kingbase: system esrep 172.xx.xxx.4(52032) idle
kingbase 30033 29215  0  2022 ?        00:00:00 kingbase: system test ::1(30695) idle
kingbase 30044 29215  0  2022 ?        01:44:43 kingbase: esrep esrep 172.xx.xxx.49(9785) idle
kingbase 30510 23987  0 12:43 pts/0    00:00:00 grep --color=auto -i idle
[kingbase@kingbase1 ~]$ kill -9 30033
[kingbase@kingbase1 ~]$ kill -9 17618 7270


$ ksql -h 172.xx.xxx.49 -U system -W -d test
Password:
ksql (V8.0)
Type "help" for help.
test=#

# session 1
test=# create table test1(id int);
CREATE TABLE
test=# insert into test1 values(1);
INSERT 0 1

test=# begin;
BEGIN
test=# update test1 set id =2;
UPDATE 1
test=# select * from test1;
 id
----
  2

# session 2
test=# select * from test1 for update nowait;
ERROR:  could not obtain lock on row in relation "test1"

test=# alter table test1 add name varchar(10);
^CCancel request sent
ERROR:  canceling statement due to user request
test=

Note:
kingbase(postgresql)的报错感觉能把对象名报出来，个人感觉这更好。

Mogdb
— opengauss的代表

[omm@mogdb1 ~]$ gsql -r
gsql ((MogDB 5.0.1 build ae6d2ada) compiled at 2023-08-16 09:07:43 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

# session 1
db1=# create table test1(id int);
CREATE TABLE
db1=# insert into test1 values(1);
INSERT 0 1

db1=# begin;
BEGIN
db1=# update test1 set id=2;
UPDATE 1
db1=#

# session 2
db1=# select * from test1 for update nowait;
ERROR:  could not obtain lock on row in relation "test1"

mogdb(opengauss)的报错同postgresql，毕竟同根， 报错中带对象名。

Summary：
对比了oracle,ob,tidb,dameng,kingbase,mogdb的报错基本是上分4类: Oracle式、MySQL式、PostgreSQL式、达梦式。 ob报错是“兼容”了oracle, 其它国产是mysql与pg自带，达梦的报错个人感觉过于简洁。 注意在线DDL上OB与TIDB这两家原生分布式对事务中的表结构生效阶段不同。

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：