KingbaseES运维案例之--- 和 Oracle修改rename表名称对比

案例说明：
KingbaseES 和Oracle数据库同样都支持'alter table xxx rename'的命令来修改表名称，但是当表被select查询时，同时rename表名称的表现却不同。

适用版本：
KingbaseES V8R6

一、KingbaseES数据库测试

1、session A 执行表查询

1）查询表数据（本表数据量较大，查询时间较长）

prod=# select count(*) from t11;
  count
----------
 80000000
(1 row)

2）查看select时锁信息

prod=# SELECT
prod-# a.datname,
prod-# locktype,
prod-# virtualtransaction,
prod-# transactionid,
prod-# nspname,
prod-# relname,
prod-# mode,
prod-# granted,
prod-# cast(date_trunc('second',query_start) AS timestamp) AS query_start
prod-# FROM
prod-# pg_locks
prod-# LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
prod-# LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace),
prod-# pg_stat_activity a
prod-# WHERE NOT pg_locks.pid = pg_backend_pid()
prod-# AND pg_locks.pid=a.pid
prod-# and pg_class.relname='t11';

 datname | locktype | virtualtransaction | transactionid | nspname | relname |      mode       | granted |     qu
ery_start
---------+----------+--------------------+---------------+---------+---------+-----------------+---------+-------
--------------
 prod    | relation | 10/25              |               | public  | t11     | AccessShareLock | t       | 2023-0
9-06 10:27:51
 prod    | relation | 11/1376            |               | public  | t11     | AccessShareLock | t       | 2023-0
9-06 10:27:51
 prod    | relation | 13/109             |               | public  | t11     | AccessShareLock | t       | 2023-0
9-06 10:27:51
(3 rows)

---如上所示，在执行select查询时，会持有‘AccessShareLock’锁。

2、Session B 执行rename table
1）执行rename table（此时表查询仍在继续）

prod=# alter table t11 rename to t1;

---此时rename操作被hang住，需要等待select完成。

2）执行raname table获得的锁信息

prod=# SELECT
prod-# a.datname,
prod-# locktype,
prod-# virtualtransaction,
prod-# transactionid,
prod-# nspname,
prod-# relname,
prod-# mode,
prod-# granted,
prod-# cast(date_trunc('second',query_start) AS timestamp) AS query_start
prod-# FROM
prod-# pg_locks
prod-# LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
prod-# LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace),
prod-# pg_stat_activity a
prod-# WHERE NOT pg_locks.pid = pg_backend_pid()
prod-# AND pg_locks.pid=a.pid
prod-# and pg_class.relname='t11';

 datname | locktype | virtualtransaction | transactionid | nspname | relname |        mode         | granted |
  query_start
---------+----------+--------------------+---------------+---------+---------+---------------------+---------+---
------------------
 prod    | relation | 8/282              |               | public  | t11     | AccessExclusiveLock | f       | 20
23-09-06 10:34:40
 prod    | relation | 10/30              |               | public  | t11     | AccessShareLock     | t       | 20
23-09-06 10:34:18
 prod    | relation | 11/2364            |               | public  | t11     | AccessShareLock     | t       | 20
23-09-06 10:34:18
 prod    | relation | 13/163             |               | public  | t11     | AccessShareLock     | t       | 20
23-09-06 10:34:18
(4 rows)

如下图所示，在执行rename table时，需持有‘AccessExclusiveLock’锁，将和‘AccessShareLock’产生冲突，需要等待‘AccessShareLock ‘锁释放。

二、Oracle 数据库测试

1、session A 执行表查询

1）查询表数据（本表数据量较大，查询时间较长）

15:18:16 SCOTT@prod>select count(*) from t2;

  COUNT(*)
----------
 117440512

2）查看select时锁信息

15:18:10 SYS@prod>r
  1  SELECT S.USERNAME,
  2         L.SID,
  3         TRUNC(l.ID1 / power(2, 16)) rbs,--power(x,y) :计算x^y次方 截断获取高4字节
  4         bitand(l.ID1, to_number('ffff', 'xxxx')) + 0 slot,---bitand 按位与 按位与获取低4字节
  5         l.ID2 seq,
  6         l.LMODE,
  7         l.REQUEST
  8    FROM v$session s, v$lock l
  9   WHERE l.TYPE = 'TX'
 10     AND l.SID = s.SID
 11*    AND s.USERNAME = 'SCOTT'

no rows selected

---如上所示，Oracle在执行select时，不需要持有锁。

2、Session B 执行rename table

1）执行rename table（此时表查询仍在继续）

15:17:20 SCOTT@prod>alter table t2 rename to t1;
Table altered.

---虽然，仍在执行select查询，但是rename table仍然瞬间执行完成，不受select的影响。

三、Kingbase和Oracle锁机制

1、KingbaseES锁机制

如下所示，当用户访问表时，需要对表持有对应的表级锁：

• ACCESS SHARE（访问共享锁）
  只与ACCESS EXCLUSIVE锁冲突。
  SELECT命令会在当前查询的表上获取一个ACCESS SHARE锁。总的来说，任何只读操作都会获取该锁。
• ACCESS EXCLUSIVE（访问排他锁）
  和所有的锁都冲突，该锁保证只有持有锁的事务能够访问当前表。
  被DROP TABLE，TRUNCATE，REINDEX，CLUSTER，VACUUM FULL和REFRESH MATERIALIZED VIEW命令自动获取。有很多种形式的ALTER TABLE命令可以获取该锁，它同样也是LOCK TABLE命令默认的锁级别。
  只有ACCESS EXCLUSIVE锁可以防止一个SELECT语句。

2、Oracle 锁机制
如下所示，当Oracle执行不同的操作时，会对访问对象持有对应的锁：

• DML锁
  DML锁可能是针对某一特定行数据的行级锁，也可能是针对表中所有行的表级锁。当用户拥有DML锁，其他用户不能修改持有锁的数据，也不能修改数据表的结构定义。DML锁主要分为两类：TX锁（事务锁或行级锁）和TM锁（表级锁）。
• DDL锁
  　 DDL锁用于保护在执行DDL操作时，对象不会被其他会话修改。在DDL语句执行期间，对象会一直持有DDL锁，执行完成后立即释放。需要注意的是，为了防止阻塞，Oracle中DDL语句一定会（隐式）提交，即使提交失败也是如此，在此之前执行的所有操作都会随之提交。

四、总结
在对表执行查询时，同时执行rename table操作，KingbaseES和Oracle数据库，由于锁机制不同，获取到的结果也不同；对于KingbaseES数据库，在业务繁忙期间，应该避免执行rename table操作，以免产生锁冲突，导致正常的业务访问被阻塞。

五、附件（锁查询语句）

1、kingbase锁查询语句

SELECT
a.datname,
locktype,
virtualtransaction,
transactionid,
nspname,
relname,
mode,
granted,
FROM
pg_locks
LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace),
pg_stat_activity a
WHERE NOT pg_locks.pid = pg_backend_pid()
AND pg_locks.pid=a.pid
and pg_class.relname='t11';

2、Oracle锁查询语句

 SELECT S.USERNAME,
       L.SID,
       TRUNC(l.ID1 / power(2, 16)) rbs,--power(x,y) :计算x^y次方 截断获取高4字节
        bitand(l.ID1, to_number('ffff', 'xxxx')) + 0 slot,---bitand 按位与 按位与获取低4字节
        l.ID2 seq,
       l.LMODE,
       l.REQUEST
       FROM v$session s, v$lock l
       WHERE l.TYPE = 'TX'
        AND l.SID = s.SID
        AND s.USERNAME = 'SCOTT' ;