解决金仓数据库KingbaseES游标长时间open导致表无法vacuum问题

最新推荐文章于 2023-12-26 10:59:14 发布

沉舟侧畔千帆过_

最新推荐文章于 2023-12-26 10:59:14 发布

阅读量429

点赞数

分类专栏：数据库文章标签：数据库 java sql 金仓数据库 kingbase

本文链接：https://blog.csdn.net/arthemis_14/article/details/128382828

版权

数据库专栏收录该内容

550 篇文章 32 订阅

订阅专栏

一、问题描述

用户在实际中可能会碰到类似以下 dead rows 无法 vacuum的问题，一个可能的原因是由于游标未结束的原因。

test=# vacuum(verbose) t1;
INFO:  vacuuming "public.t1"
INFO:  "t1": found 0 removable, 985 nonremovable row versions in 66 out of 67 pages
DETAIL:  788 dead row versions cannot be removed yet, oldest xmin: 4996
There were 0 unused item identifiers.
Skipped 1 page due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  vacuuming "sys_toast.sys_toast_29558"
INFO:  index "sys_toast_29558_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "sys_toast_29558": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 4996
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

二、举例说明

1、事务不提交的情况

创建测试数据：

create table t1(id integer,name text);
create table t2(id integer);
insert insert t1 select generate_series(1,1000),repeat('a',500);

创建测试过程：

create or replace procedure proc01_nocommit as
  cursor c01 is select id from t1;
  v_id integer;
begin
  open c01;
  fetch c01 into v_id;
  while (c01%FOUND) loop
    insert into t2 values(v_id);
    perform sys_sleep(1);
    fetch c01 into v_id;
  end loop;
end;
/

session 1:

call proc01_nocommit();

session 2:

test=# select * from sys_locks where relation='t1'::regclass;

 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid   |      mode       | granted | fastpath

----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+-----------------+---------+----------

 relation |    29504 |    29558 |      |       |            |               |         |       |          | 4/2737             | 360612 | AccessShareLock | t       | t

(1 row)



test=# delete from t1 where mod(id,5)<>1;

DELETE 200

无法 vacuum

test=# vacuum(verbose) t1;

INFO:  vacuuming "public.t1"

INFO:  "t1": found 0 removable, 985 nonremovable row versions in 66 out of 67 pages

DETAIL:  788 dead row versions cannot be removed yet, oldest xmin: 4996

There were 0 unused item identifiers.

Skipped 1 page due to buffer pins, 0 frozen pages.

0 pages are entirely empty.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  vacuuming "sys_toast.pg_toast_29558"

INFO:  index "sys_toast_29558_index" now contains 0 row versions in 1 pages

DETAIL:  0 index row versions were removed.

0 index pages have been deleted, 0 are currently reusable.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO:  "sys_toast_29558": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 4996

There were 0 unused item identifiers.

Skipped 0 pages due to buffer pins, 0 frozen pages.

0 pages are entirely empty.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

VACUUM

结论：可以看到对象有 AccessShareLock 锁（实际验证，不管只要 cursor 定义了，不管是否open ，都会有 AccessShareLock 锁）。

2、事务提交的情景

修改过程块如下：

create or replace procedure proc01_commit as
  cursor c01 is select id from t1;
  v_id integer;
begin
  open c01;
  fetch c01 into v_id;
  while (c01%FOUND) loop
    insert into t2 values(v_id);
    perform sys_sleep(1);
    fetch c01 into v_id;
    commit;
  end loop;
end;
/

可以看到在游标内部加了commit;

PS：KingbaseES 支持游标跨事务的场景。在 commit时，会将剩余未完成游标的结果取回到临时文件，这样可以保证MVCC 机制。

Session 1:

call proc01_commit()

Session 2:

test=# select * from sys_locks where relation='t1'::regclass;

 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid   |      mode       | granted | fastpath

----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+--------+-----------------+---------+----------

(0 row)



test=# delete from t1 where mod(id,5)<>0;

DELETE 800

结论：没有锁，可以vacuum。

test=# vacuum (verbose) t1;
INFO:  vacuuming "public.t1"
INFO:  "t1": removed 800 row versions in 67 pages
INFO:  "t1": found 800 removable, 200 nonremovable row versions in 67 out of 67 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 4857
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  vacuuming "sys_toast.sys_toast_29549"
INFO:  index "sys_toast_29549_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "sys_toast_29549": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 4857
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM