崖山数据库支持了YAC如oracle的集群RAC功能，RAC除了高可用外，大家可能比较关注像性能的提升效率比怎么样？因为大家还是希望增加节点为带了性能横向的扩展，那对于要求在cache fusion中的GES, GCS等资源与锁的管理就要求比较高，还有就是跨节点的并发争用，这里先简单测试row级争用的现象，与oracle还是有些不同。
先回顾一下oracle
# node1
SQL> create table test as select rownum id ,'anbob'||rownum name from dual connect by rownum<=10;
Table created.
SQL> update test set name='xxxx' where id=1;
1 row updated.
# node2
oracle@node02:/home/oracle $sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 9 19:51:03 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> update test set name='xxxx' where id=1;
--- waiting...
# session 3
select inst_id,username,sid,SQL_ID,EVENT,SECONDS_IN_WAIT,STATE,BLOCKING_INSTANCE,BLOCKING_SESSION from gv$session where username='SYS'
INST_ID USERNAME SID SQL_ID EVENT SECONDS_IN_WAIT STATE BLOCKING_INSTANCE BLOCKING_SESSION
---------- ---------- ---------- -------------------------- ------------------------------ --------------- -------------------------------------- ----------------- ----------------
1 SYS 460 SQL*Net message from client 68 WAITING
2 SYS 623 2fcq2duttzmxg enq: TX - row lock contention 246 WAITING 1 460
...Note: 提示可以看到是node2的 623# session被node1的460# session堵塞，node2 623 sid显示等待”enq: TX – row lock contention”等待事件，这对于oracle DBA再熟悉不过了。
YashanDB 23.5
#node 1
[yashan@yashan-74 ~]$ sh conn.sh
YashanDB SQL Enterprise Edition Release 23.5.1.100 x86_64
Connected to:
YashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 - Linux
SQL> create table test as select rownum id ,'anbob'||rownum name from dual connect by rownum<=10;
Succeed.
SQL> update test set name='xxxx' where id=1;
1 row affected.
SQL> select * from v$mystat where rownum<=1;
SID STATISTIC# VALUE
-------- ------------ ---------------------
75 0 7
1 row fetched.
# node 2
[yashan@yashan-75 ~]$ yasql sys/yashan_1234@172.20.23.64:1688
YashanDB SQL Enterprise Edition Release 23.5.1.100 x86_64
Connected to:
YashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 - Linux
SQL> update test set name='xxxx' where id=1;
-- waiting
# session 2 on node2
SQL> select sid,LOCKWAIT,WAIT_EVENT,WAIT_CLASS,USERNAME,STATUS,COMMAND,SQL_ID,PROCESS,SQL_STATE_TIME from v$session where username is not null;
SID LOCKWAIT WAIT_EVENT WAIT_CLASS USERNAME STATUS COMMAND SQL_ID PROCESS SQL_STATE_TIME
-------- --------------------- --------------------------------- --------------------------------- ---------- --------- ------------ ----------------- --------- --------------
88 SYS ACTIVE 1 djwap9d7cg05q 5140 0
# session 3 on node2
SQL> select inst_id,GROUP_ID,GLOBAL_SESSION_ID, sid,LOCKWAIT,WAIT_EVENT,WAIT_CLASS,USERNAME,STATUS,COMMAND,SQL_ID,PROCESS from gv$session where username is not null;
INST_ID GROUP_ID GLOBAL_SESSION_ID SID LOCKWAIT WAIT_EVENT WAIT_CLASS USERNAME STATUS COMMAND SQL_ID PROCESS
----------- ----------- ----------------- -------- --------------------- --------------------------------- --------------------------------- ---------- --------- ------------ ----------------- ---------
2 0 88 SYS ACTIVE 26dy9vkjjnp7f 5140
1 0 72 4521328657 row xact wait Application SYS ACTIVE 3 0ysq0ccw5djgr 4857
1 0 75 SQL*Net message from client Idle SYS INACTIVE 3 10928
1 0 76 SQL*Net message from client Idle SYS INACTIVE 1 11740
1 0 77 SYS
5 rows fetched.Note: 这里node2的v$session 并未显示active 被堵塞的会话， 查询gv$session可以看到行堵塞信息，但是inst_id为1. 实际上是sid 75#堵塞了72#, 72# session 显示等待事件为”row xact wait”.
查看节点信息
[yashan@yashan-74 ~]$ yasboot cluster status -c yashandb -d +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | hostid | node_type | nodeid | pid | instance_status | database_status | database_role | listen_address | source_node | data_path | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | host0001 | ce | 1-1:1 | 29139 | open | normal | primary | 172.20.23.74:1688 | - | /data/yashan/yasdb_data/ce-1-1 | +----------+-----------+--------+-------+-----------------+-----------------+---------------+-------------------+-------------+--------------------------------+ | host0002 | ce | 1-2:2 | 27002 | open | normal | primary | 172.20.23.75:1688 | - | /data/yashan/yasdb_data/ce-1-2 | +----------+-----------+--------+-------+-----------------+-----------------+---------------+-------------------+-------------+--------------------------------+ [yashan@yashan-74 ~]$ ycsctl status +-------------------+---------------------------------+---------------------------+-------------------------------------+ | Self Host ID | Cluster Master ID | YasFS Master ID | Active Host Count | +-------------------+---------------------------------+---------------------------+-------------------------------------+ |1 |1 |1 |2 | +-------------------+---------------------------------+---------------------------+-------------------------------------+ +---------+---------+---------+---------+-------------+--------------------+--------------------+-----------+-----------+ | Host ID | Target | YCS | YFS | VIP | DB_NAME | PDB_NAME | DB_STATE | IS_MASTER | +---------+---------+---------+---------+-------------+--------------------+--------------------+-----------+-----------+ |1 |online |online |online |host1.online |yashandb |yashandb |online |TRUE | +---------+---------+---------+---------+-------------+--------------------+--------------------+-----------+-----------+ |2 |online |online |online |host2.online |yashandb |yashandb |online |FALSE | +---------+---------+---------+---------+-------------+--------------------+--------------------+-----------+-----------+ +---------------------------------------+---------------------------------------+---------------------------------------+ |SCAN VIP 1: host1.online | | | +---------------------------------------+---------------------------------------+---------------------------------------+
Note: 虽然是双写的YAC架构，node1 是master节点，锁信息在node1,不确认是否和这节点角色相关。
下面模拟node2 堵塞node1
# node2
[yashan@yashan-75 ~]$ yasql / as sysdba
YashanDB SQL Enterprise Edition Release 23.5.1.100 x86_64
Connected to:
YashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 - Linux
SQL> update test set name='xxxx' where id=2;
1 row affected.
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
-------- ------------ ---------------------
89 0 0
1 row fetched.
# node1
[yashan@yashan-74 ~]$ yasql / as sysdba
YashanDB SQL Enterprise Edition Release 23.5.1.100 x86_64
Connected to:
YashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 - Linux
SQL> update test set name='xxxx' where id=2;
-- waiting
# session 2 on node2
SQL> select sid,LOCKWAIT,WAIT_EVENT,WAIT_CLASS,USERNAME,STATUS,COMMAND,SQL_ID,PROCESS,SQL_STATE_TIME from v$session where username is not null;
SID LOCKWAIT WAIT_EVENT WAIT_CLASS USERNAME STATUS COMMAND SQL_ID PROCESS SQL_STATE_TIME
-------- --------------------- --------------------------------- --------------------------------- ---------- --------- ------------ ----------------- --------- --------------
88 SYS ACTIVE 1 3dbhs8n1xdtzz 5140 0
89 SQL*Net message from client Idle SYS INACTIVE 1 9831 133
2 rows fetched.
SQL> select inst_id,GROUP_ID,GLOBAL_SESSION_ID, sid,LOCKWAIT,WAIT_EVENT,WAIT_CLASS,USERNAME,STATUS,COMMAND,SQL_ID,PROCESS from gv$session where username is not null;
INST_ID GROUP_ID GLOBAL_SESSION_ID SID LOCKWAIT WAIT_EVENT WAIT_CLASS USERNAME STATUS COMMAND SQL_ID PROCESS
----------- ----------- ----------------- -------- --------------------- --------------------------------- --------------------------------- ---------- --------- ------------ ----------------- ---------
2 0 88 SYS ACTIVE 26dy9vkjjnp7f 5140
2 0 89 SQL*Net message from client Idle SYS INACTIVE 1 9831
1 0 72 4521328657 row xact wait Application SYS ACTIVE 3 0ysq0ccw5djgr 4857
1 0 75 SQL*Net message from client Idle SYS INACTIVE 1 10928
1 0 76 4379116555 remote xact wait Cluster SYS ACTIVE 3 g2282jfd5huzj 14900
1 0 77 SYS
6 rows fetched.Note： 现在wait_class变为cluster, 被堵塞的node2 sid 76#的等待事件为”remote xact wait”,
v$lock视图
SQL> select * from gv$lock;
GROUP_ID GROUP_NODE_ID INST_ID SID ID1 ID2 LMODE REQUEST
----------- ------------- ----------- -------- --------------------- --------------------- --------------------------------- ---------------------------------
0 0 2 89 3533 TS
0 0 2 89 4880640 0 ROW
0 0 1 72 3533 TS
0 0 1 75 3533 TS
0 0 1 75 4880640 1 ROW
0 0 1 76 3533 TS
0 0 1 72 4521328657 ROW
0 0 1 76 4379116555 ROW
8 rows fetched.Note: 上面两个事务
72 <= blocked by 75
76 <= blocked by 89
仅记录现象，后面再补充原理。