Parallel DML和append将在表上产生exclusive lock

http://space.itpub.net/4227/viewspace-678083

说到append使用时会出现OR A-12838:无法在并行模式下修改之后读/修改对象

 

其实这是由于append会启用parallel操作造成的。

使用insert /*+ append */ .. values(...)是不会发生ORA-12838的错误的，原因是insert一条记录时，Oracle不会启用parallel.

 

现在我来看看paralle DML的LOCK情况:

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as dlt
 
SQL> drop table test_lock;
 
Table dropped
 
SQL> create table test_lock (id number(8));
 
Table created
 
SQL> select object_name,object_id from user_objects where object_name='TEST_LOCK';
 
OBJECT_NAME                                                                       OBJECT_ID
-------------------------------------------------------------------------------- ----------
TEST_LOCK                                                                             57672
 
SQL> insert /*+ append */ into test_lock values(1);
 
1 row inserted
 
SQL> select * from v$lock where id1 = 57672;
 
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
6E1C4224 6E1C423C        139 TM        57672          0          3          0         42          0
 
SQL> commit;
 
Commit complete
 
SQL> insert /*+ append */ into test_lock select object_id from user_objects;
 
37 rows inserted
 
SQL> select * from v$lock where id1 = 57672;
 
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
6E1C4224 6E1C423C        139 TM        57672          0          6          0          7          0
 
SQL> select count(1) from v$px_session;
 
  COUNT(1)
----------
         0
 
SQL> commit;
 
Commit complete

 

SQL> alter session enable parallel dml;
 
Session altered
 
SQL> insert /*+ parallel(test_lock,4) */ into test_lock select object_id from user_objects;
 
37 rows inserted
 
SQL> select count(1) from v$px_session;
 
  COUNT(1)
----------
         5    --产生了平行session
 
SQL> select * from v$lock where id1 = 57672;
 
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
6E1C4224 6E1C423C        139 TM        57672          0          6          0         14          0
 

SQL> select count(1) from test_lock;
 
select count(1) from test_lock
 
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> commit;
 
Commit complete

 

SQL> select count(1) from test_lock;
 
  COUNT(1)
----------
       112

 

update /*+ parallel(test_lock,4) test_lock set id =1;

delete  /*+ parallel(test_lock,4) test_lock ;

都会产生与insert /*+ parallel(test_lock,4) */ into test_lock select object_id from user_objects;
同样的效果.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195110/viewspace-692694/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195110/viewspace-692694/