Lock TABLE IN EXCLUSIVE MODE 控制DDL 并发

原创于 2025-03-04 02:05:11 发布 · 619 阅读

19 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql

LOCK table 后，未commit之前不会释放lock，不允许其他session进行DDL，这样避免alter table （parallel /noparallel enable constraints）后出现的

ORA-12842: Cursor invalidated during parallel excution

Changes

Adding partitions while querying the table

Cause

ORA-12842 can be reported to a user session when executing a parallel query.
This is normal and usually can be ignored but may cause problems for some applications.

ORA-12842: Cursor invalidated during parallel execution -- the object changed

Solution

Ignore error during maintenance operations: you will need to re-execute the query

Optionally you might try the following to suppress this expected error message

set event Bug 7170213 - Put ORA-12842 from parallel query under _fix_control

To disable reporting of the error set

"_fix_control"='7170213:OFF'; 出错了 DML 都继续不下去了，怎么可以忽略

For usage see

Error: ORA-12842 (ORA-12842)
Text: Cursor invalidated during parallel execution
---------------------------------------------------------------------------
Cause: The cursor was invalidated during the parse phase of deferred
parallel processing, e.g. when set operands are parallelized.
Action: Depends on why the cursor was invalidated. Possible causes
include DDL on a schema object and shared pool being flushed.

排他锁：可通过 lock table in exclusive mode 命令添加X锁。在该锁定模式下，其他用户不能对表进行任何的DML和DDL操作，该表上只能进行查询。排它锁是在锁机制中限制最多的一种锁类型，允许加排它锁的事务独自控制对表的写权限。
加锁语法：Lock Table TableName In Exclusive Mode;
允许的操作：在一个表中只能有一个事务对该表实行排它锁，排它锁仅允许其它的事务查询该表。
禁止的操作：拥有排外锁的事务禁止其它事务执行其它任何DML类型的语句或在该表上加任何其它类型的锁。
定义排它锁的语法：LOCK TABLE TableName IN EXCLUSIVE MODE;

共享锁：可通过lock table in share mode命令添加该S锁。在该锁定模式下，不允许任何用户更新表。但是允许其他用户发出 select…from for update 命令对表添加RS锁。
加锁语法：Lock Table TableName In Share Mode;
允许的操作：一个共享锁由一个事务控制，仅允许其它事务查询被锁定的表。一个有效的共享锁明确地用Select … For update形式锁定行，或执行Lock Table TableName In Share Mode语法锁定整个表，不允许被其它事务更新。
禁止的操作：一个共享锁由一个事务来控制，防止其它事务更新该表或执行下面的语句：
LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE