Parallel DML和append將在表上產生exclusive lock

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/，如需轉載，請註明出處，否則將追究法律責任。

Parallel DML和append將在表上產生exclusive lock

相關文章