系统全局锁等候者-SYS_GWAITERS
📄字数 679
👁️阅读量 加载中...
功能描述
SYS_GWAITERS系统表用于管理数据库当前节点全局锁等候信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | NODEID | INTEGER | 节点ID | × | √ |
| 1 | LOCK_TYPE | INTEGER | 全局锁类型 | √ | √ |
| 2 | LOCK_ID | BIGINT | 锁ID | √ | √ |
| 3 | WAIT_NID | INTEGER | 等候者节点ID | √ | √ |
| 4 | WAIT_LKS | VARCHAR | 等候者锁描述 | √ | √ |
应用举例
- 查询系统全局锁等候者节点ID
- 查询系统全局锁等候者锁类型
sql
-- 1号节点执行
-- 创建表
SQL> CREATE TABLE tab_gwaiters(id int);
-- 设置非自动提交
SQL> SET auto_commit OFF;
-- 为t1表增加排他锁
SQL> LOCK tab_gwaiters IN EXCLUSIVE MODE;
-- 此时1号节点持有全局X锁
SQL> SELECT * FROM SYS_LOCKS WHERE node_own = 'x';
+--------+-----------+--------------------+------------------------------+----------+----------+-------------+
| NODEID | LOCK_TYPE | LOCK_ID | REF | NODE_OWN | NODE_REQ | WAIT_REVOKE |
+--------+-----------+--------------------+------------------------------+----------+----------+-------------+
| 1 | 2 | 576460756599439395 | [S]0,[X]1,[IS]0,[IX]0,[SIX]0 | X | | |
+--------+-----------+--------------------+------------------------------+----------+----------+-------------+
-- 2号节点执行查询
SQL> SELECT * FROM tab_gwaiters;
-- 等候
-- 1号节点查询
SQL> SELECT * FROM SYS_GWAITERS;
+--------+-----------+--------------------+----------+----------+
| NODEID | LOCK_TYPE | LOCK_ID | WAIT_NID | WAIT_LKS |
+--------+-----------+--------------------+----------+----------+
| 1 | 2 | 576460756599439395 | 2 | IS |
+--------+-----------+--------------------+----------+----------+
-- 1号节点执行提交
SQL> commit
-- 查询等候者为空
SQL> SELECT * FROM SYS_GWAITERS;
+--------+-----------+---------+----------+----------+
| NODEID | LOCK_TYPE | LOCK_ID | WAIT_NID | WAIT_LKS |
+--------+-----------+---------+----------+----------+
+--------+-----------+---------+----------+----------+