ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Parallel DML和append将在表上产生exclusive lock

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

原创 Linux操作系统 作者:denglt 时间:2011-04-17 15:00:12 0 删除 编辑
http://space.itpub.net/4227/viewspace-678083
说到append使用时会出现ORA-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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-04

  • 博文量
    118
  • 访问量
    708468