Oracle中执行DDL后保留排他表锁的实现方案问询

1. 每次DDL后重新获取表级排他锁 #

Oracle执行DDL会自动提交事务并释放所有锁，因此可以在每条DDL执行完成后，立即重新对目标表执行**排他锁（EXCLUSIVE MODE）**锁定，确保后续操作期间其他会话无法对这些表执行DML/DDL。若需要阻止其他会话的SELECT查询（Oracle默认读一致性允许这类操作），可额外对全表执行SELECT * FROM x FOR UPDATE NOWAIT锁定所有行（注意：大表会有明显性能损耗）。

示例代码：

-- 初始锁定目标表
LOCK TABLE x IN EXCLUSIVE MODE NOWAIT;
LOCK TABLE y IN EXCLUSIVE MODE NOWAIT;

-- 第一条DDL操作
ALTER TABLE x ADD COLUMN new_col NUMBER;

-- 重新锁定，避免DDL提交后锁释放
LOCK TABLE x IN EXCLUSIVE MODE NOWAIT;
LOCK TABLE y IN EXCLUSIVE MODE NOWAIT;

-- 第二条DDL操作
ALTER TABLE y MODIFY col1 VARCHAR2(100);

-- 再次重新锁定
LOCK TABLE x IN EXCLUSIVE MODE NOWAIT;
LOCK TABLE y IN EXCLUSIVE MODE NOWAIT;

-- 第三条DDL操作
ALTER TABLE x DROP COLUMN old_col;

-- 显式提交，释放所有锁
COMMIT;

2. 临时设置表为只读（限特定DDL场景） #

将目标表临时设置为只读状态，此时其他会话无法执行DML操作，完成允许的DDL（如创建索引）后再恢复为读写。注意：只读表无法执行修改表结构的DDL（如添加/删除列），因此仅适用于查询类DDL场景。

示例代码：

-- 设置表为只读
ALTER TABLE x READ ONLY;
ALTER TABLE y READ ONLY;

-- 执行允许的DDL（如创建索引）
CREATE INDEX idx_x_col1 ON x(col1);
CREATE INDEX idx_y_col1 ON y(col1);

-- 恢复表为读写状态
ALTER TABLE x READ WRITE;
ALTER TABLE y READ WRITE;

-- 显式提交
COMMIT;

3. 使用可串行化隔离级别增强锁力度 #

将当前会话的事务隔离级别设置为SERIALIZABLE（可串行化），该级别下其他会话对目标表的修改操作会被阻塞，直到当前会话提交事务。但该方案无法阻止其他会话的SELECT查询，仅能保证事务的串行执行。

示例代码：

-- 设置可串行化隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 初始锁定目标表
LOCK TABLE x IN EXCLUSIVE MODE NOWAIT;
LOCK TABLE y IN EXCLUSIVE MODE NOWAIT;

-- 执行DDL后重新锁定
ALTER TABLE x ...;
LOCK TABLE x IN EXCLUSIVE MODE NOWAIT;
LOCK TABLE y IN EXCLUSIVE MODE NOWAIT;

ALTER TABLE y ...;
LOCK TABLE x IN EXCLUSIVE MODE NOWAIT;
LOCK TABLE y IN EXCLUSIVE MODE NOWAIT;

-- 显式提交
COMMIT;

内容的提问来源于stack exchange，提问作者Vladislav Ihost