ORA-12838: cannot read/modify an object after modifying it in parallel

2312123www

  今天涛神说程序报ORA-12838错误，调出程序sql发现是一个大批量的insert，里面使用了   /*+ append */的hint，于是下面做了段模拟这个错误实验  

session 1   

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> create table tb_test as select * from emp where 1=2; Table created. SQL> insert /*+ append */ into tb_test select * from emp;    14 rows created.  -- 注意/*+ append*/ 没有commit SQL> select * from tb_test; select * from tb_test               * ERROR at line 1: ORA-12838: cannot read/modify an object after modifying it in parallel

session 2

1 2 3

SQL> select * from tb_test; no rows selected

session 1 报了ORA-12838的错误，session正常，说明了在同一个事物中如果使用/*+ append */ insert如果没有commit去做dml操作会这个错误，

进一步查询发现表上加了类型为6的锁

1 2 3 4 5 6

SQL> col object_name for a20; SQL> select t2.object_name,t1.LOCKED_MODE from  v$locked_object t1 join dba_objects t2 on t1.OBJECT_ID = t2.object_id; OBJECT_NAME      LOCKED_MODE -------------------- ----------- TB_TEST               6

我们知道 locked mode 6： Lock table t in exclusive mode， 排他锁限制最高的TM锁，会禁止执行任何类型的DML语句