/+append/

最新推荐文章于 2021-07-05 19:10:15 发布

cuishipin9918

最新推荐文章于 2021-07-05 19:10:15 发布

阅读量270

点赞数 1

今天使用/* +APPEND */时遇到了错误

A>insert /*+append*/ into t1 select * from dba_users;

15 rows created.

A>insert /*+append*/ into t1 select * from dba_users;
insert /*+append*/ into t1 select * from dba_users
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

可以看出报错是在第二次使用/* +APPEND */

A>!oerr ora 12838
12838, 00000, "cannot read/modify an object after modifying it in parallel"
// *Cause: Within the same transaction, an attempt was made to add read or
// modification statements on a table after it had been modified in parallel
// or with direct load. This is not permitted.
// *Action: Rewrite the transaction, or break it up into two transactions:
// one containing the initial modification and the second containing the
// parallel modification operation.

查了下错误代码，说同一个事务中，对同一个表进行过并行操作，或者直接加载后，就不能再进行读写操作了

分析一下
1.从直觉判断，错误与同一个事务没有多大关系
2.APPEND是直接加载没错，但没听说过直接加载后不能再进行读写操作了

对报错行为概括一下就是：相同操作，第一次可以，第二次不行
网上google一下，发现与锁有关，简单地说：第一次以APPEND方式插入后，在表T1上加了6号排它锁，所以第二次插入时失败

具体实验一下

A>insert into t1 select * from dba_users;

15 rows created.

A>select sid,type,id1,id2,lmode,request,block from v$lock where sid=1;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
         1 AE        100          0          4          0          0
         1 TM      59051          0          3          0          0            //常规插入时加的是3号锁
         1 TX     262175        501          6          0          0

A>insert /*+append*/ into t1 select * from dba_users;

15 rows created.

A>select sid,type,id1,id2,lmode,request,block from v$lock where sid=1;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
         1 AE        100          0          4          0          0
         1 TM      59051          0          6          0          0            //改成APPEND插入后变成了6号锁
         1 TX     262175        501          6          0          0

此后无论是常规插入，APPEND插入，还是更新删除操作，一律失败，原因都是6号排它锁

A>insert /*+append*/ into t1 select * from dba_users;
insert /*+append*/ into t1 select * from dba_users
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

A>insert into t1 select * from dba_users;
insert into t1 select * from dba_users
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

A>update t1 set user_id=1;
update t1 set user_id=1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

A>delete from t1;
delete from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

最后要注意的就是hint的写法，其中多一个空格也不行，尤其是在+前面

A>insert into t1 select * from dba_users;

15 rows created.

A>insert /* +append */ into t1 select * from dba_users; //+append前后都有空格，没问题

15 rows created.

A>insert /* +append*/ into t1 select * from dba_users; //+append前面有空格，后边没空格，没问题

15 rows created.

A>insert /*+append */ into t1 select * from dba_users; //+append前面没空格，后面有，不行了，锁变成6号了，换句话说，这种写法可能才是正确

15 rows created.

A>insert /*+append*/ into t1 select * from dba_users;                //当然了，+append前后都没有，也是加的6号锁
insert /*+append*/ into t1 select * from dba_users
                                         *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

总结
关于APPEND有两点要注意：1.使用/*+APPEND*/后会在表上加6号排它锁
                                        2.注意/*+APPEND*/中，/*和+号之间不能有空格，否则就不是APPEND方式了