【YashanDB 数据库】大事务回滚导致其他操作无法执行，报错 YAS-02016 no free undo blocks

问题现象

客户将一个 100G 的表的数据插入到另一个表中，使用 insert into select 插入数据。从第一天下午 2 点开始执行，到第二天上午 10 点，一直未执行完毕。

由于需要实施下一步操作，客户 kill 重启了数据库，之后数据库一直回滚中，导致后续执行其他操作都报错 YAS-02016 no free undo blocks

问题单：大 sql 的 undo 回滚导致任何操作都无法执行，需要优化

问题的风险及影响

客户环境为准生产环境，影响业务执行。

问题影响的版本

YashanDB 版本：22.2.11.100

问题发生原因

1、UNDO 没有做调整，最大为 64GB，insert 单个表超过 100GB，UNDO 空间不足导致卡死。

2、由于 kill 导致重启对 insert into select 做回退，rollback 过程不能做 truncate 操作，UNDO 空间需要 rollback 完成之后才能释放，由于索引导致 rollback 比较慢，UNDO 一直不能释放，进而导致执行不了其他 SQL。

解决方法及规避方式

1、删除索引，加快 rollback

2、线上操作需要避免出现大事务，使用 imp、yasldr 等工具分批提交，或者在 insert into select 中添加 where 条件，分批提交。

3、执行数据迁移过程，规划好 UNDO 空间。

4、导入数据过程先去掉索引，待数据导入完之后重建索引。

问题分析和处理过程

核查相应参数：

• 机器配置为 16 核 64g

• UNDO_RETENTION 为 300

• STARTUP_ROLLBACK_PARALLELISM 为 2

• V$ROLLBACK 为空

• UNDO 文件为 64G

• user_segments 中目标表的 segment 大小约 100G

• 表一共 493752518 行，数据量大

• CHECKPOINT_INTERVAL=100000、CHECKPOINT_TIMEOUT=300 为默认值

尝试添加 UNDO 数据文件：不成功

返回报错，报错信息 YAS-02042 cannot execute tablespace DDL when the database is rolling back。由于数据库被 kill 重启， 该报错是正常的。

分析是否需要调整回退线程数量：不需要

STARTUP_ROLLBACK_PARALLELISM 可以在数据库启动的时候决定回退线程数量，并启动相应的回退线程。从 CPU 的情况看，消耗很低， 瓶颈不在 rollback 线程， 调整需要重启，决定不调整该参数。

尝试调整 UNDO 保留时间：效果不明显

已提交事务的 UNDO 会变为可回收，为了减少已提交事务占用较多空间，强制所有提交的事务立即写入数据文件，执行了如下操作：

alter system set UNDO_RETENTION = 3;

ALTER SYSTEM CHECKPOINT;

操作后，UNDO 表空间使用没有明显减少

联系客户删除索引，待数据导入完成之后再重建索引，效果明显

查看 IOSTAT，结果： 读 20+M/s， 写 400K/s。写入数据相对较慢， 检查表目标表 DDL， 存在较多索引。

删除后 IO 读 20+M/s， 写 4M/s，写速度明显提升， 20 分钟后客户反馈 rollback 完成。

分析执行其他操作报错原因

检查 UNDO 表空间大小，确认最大值是 64G，这也解释了为什么一个事务 rollback 影响后续其他业务执行都报错 YAS-02016 no free undo blocks， 是因为 UNDO 表空间满了，在 rollback 完成之前不会释放。

UNDO 空间大小有默认安装参数，在没有修改的情况下最大值是 64G， 虽然会自动扩展， 但是在到达最大值之后，不会再扩展。

和客户确认是没有做过修改， 核查 V$datafile 视图， 最大值确认是 64G

https://doc.yashandb.com/yashandb/22.2/zh/%E5%B7%A5%E5%85%B7%E6%89%8B%E5%86%8C/yasboot/%E5%BB%BA%E5%BA%93%E5%8F%82%E6%95%B0.html

重新执行数据插入

客户在 rollback 之后添加多 2 个 UNDO 表空间文件， 扩大 UNDO 的空间扩展上限，同时修改插入语句，分批插入数据，避免大事务。

经验总结

1、数据写入、rollback 过程，需要对索引做相应的修改，为了加快速度，可以先删除或把索引设置为 UNUSABLE，待完成之后再建索引，或 rebuild 索引。

2、线上操作要避免出现大事务，使用 imp、yasldr 等工具分批提交，或者在 insert into select 中添加 where 条件，分批提交。

3、执行数据迁移过程，规划好 UNDO 空间。UNDO 空间大小默认最大值是 64G，虽然会自动扩展，但是在到达最大值之后，不会再扩展，可以修改最大值限制，或添加数据文件。