1、唱唱反調——Append中的非並行

在實驗中，偶然發現了這樣的現象。

//單值情況

//自身session1（sid=140）允許多次新增

SQL> insert /*+ append */into t values('ABC',34,32);

1 row inserted

SQL> insert /*+ append */into t values('ABC',34,33);

1 row inserted

注意：這裡同一個session的連續append竟然實現了。

//檢視此時鎖狀態

SQL> select * from v$lock where sid=140;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 140 TM 53083 0 3 0

6BE3BE7C 6BE3BF98 140 TX 196625 652 6 0

在這裡，我們發現一些和上面的討論不同的例外。就是當我們append一條資料（注意：是格式上的一條資料）時候，oracle對TM表鎖不會嘗試獲取一個LMODE=6的排他鎖，而是同一般序列化insert一樣，只去獲取了一個共享鎖lmode=3。

那麼，此時我們在另一個session中，進行一般的insert，也就沒有問題了。

//session2中插入資料

SQL> insert into t values('ABC',34,34); //一般模式

1 row inserted

SQL> select * from v$lock where sid in (140,142);

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 140 TM 53083 0 3 0

6BDC4138 6BDC4150 142 TM 53083 0 3 0

6BE127B8 6BE128D4 142 TX 65576 638 6 0

6BE3BE7C 6BE3BF98 140 TX 196625 652 6 0

說明：兩個session都在物件上加了共享鎖LMODE=3，是可以相容的。

//如果session2中使用append的方法，單條模式

SQL> insert /*+ append */into t values('ABC',34,35);

1 row inserted

SQL> select * from v$lock where sid in (140,142);

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 140 TM 53083 0 3 0

6BDC4138 6BDC4150 142 TM 53083 0 3 0

6BE127B8 6BE128D4 142 TX 65576 638 6 0

6BE3BE7C 6BE3BF98 140 TX 196625 652 6 0

說明：當使用單條插入模式之後，即使是使用了append，多會話併發實現。

但是，當我們在另一個session中使用多條append之後，是什麼現象？

//如果session2中使用append方法，多條

SQL> insert /*+ append */ into t select owner, object_id, rownum from dba_objects where rownum<10;

立即被hange住

SQL> select * from v$lock where sid in (140,142);

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 140 TM 53083 0 3 0

6BDC4138 6BDC4150 142 TM 53083 0 3 6

6BE127B8 6BE128D4 142 TX 65576 638 6 0

6BE3BE7C 6BE3BF98 140 TX 196625 652 6 0

說明：本段程式碼是在上面的實驗基礎上進行的，可以看出，sid=142的會話在擁有共享鎖的基礎上，試圖升級為排他鎖。但是其他一個session（sid=140）的擁有一個共享鎖，共享鎖與排他鎖互斥，所以session2被hange住。

剛才的現象似乎與我們之前的結論相悖，原因可能有兩個：其一是插入資料是一條；其二是插入資料的sql語句格式。

那麼是不是說，只要插入的資料是一條就不會有問題了？

SQL> select sid from v$mystat where rownum<2;

SID

----------

145

SQL> insert /*+ append */ into t select * from dba_objects where object_id=100;

1 row inserted

SQL> select * from v$lock where sid=145;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 145 TM 53084 0 6 0

6BE19000 6BE1911C 145 TX 131075 655 6 0

說明：同樣是加排他鎖！！

SQL> commit;

Commit complete

SQL> select count(*) from t;

COUNT(*)

----------

1

那麼，我們的第一個假設被否決了。只有可能是透過插入的格式確定了，因為使用values的方式，一次只能有一條資料被插入！

結論：當insert使用append，但是後面用values(…)的時候，oracle不會啟動並行插入的模式，使用direct-path方式也不會影響到其他session的併發操作。

2、單session的鎖升級

當我們在一個session的時候，獨佔鎖和共享鎖之間關係是什麼樣子？

//在一個session內

SQL> insert into t values('ABC',34,36);

1 row inserted

SQL> select * from v$lock where sid=140;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 140 TM 53083 0 3 0

6BE3BE7C 6BE3BF98 140 TX 589834 679 6 0

//append操作

SQL> insert /*+ append */ into t select owner, object_id, rownum from dba_objects where rownum<10;

9 rows inserted

SQL> select * from v$lock where sid=140;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6BDC4074 6BDC408C 140 TM 53083 0 6 0

6BE3BE7C 6BE3BF98 140 TX 589834 679 6 0

說明：此時一個session內部，鎖的型別從共享鎖升級到排他鎖。當然，這是在沒有其他session擁有表T共享鎖的基礎上的。

SQL> insert into t values('ABC',34,37);

insert into t values('ABC',34,37)

ORA-12838: 無法在並行模式下修改之後讀/修改物件

這部分的結論：在一個session內部，當沒有其他會話擁有物件共享鎖或其級別以上的鎖時，可以從共享鎖升級為排他鎖。

3、臨時表與append

臨時表是一種以session為中心的儲存結構，各個session互不影響。在append一個臨時表的時候，會不會出現併發問題呢？

//建立一個臨時表，型別是會話級別的

SQL> create global temporary table t_temp

2 ( owner varchar2(100),

3 object_id number,

4 num number)

5 on commit preserve rows;

Table created

//執行append批次插入

SQL> insert /*+ append */ into t_temp select owner,object_id,rownum from dba_objects where rownum<10;

9 rows inserted

SQL> select sid from v$mystat where rownum<2;

SID

----------

156

SQL> select * from v$lock where sid=156;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6C8342DC 6C8342F0 156 TO 53086 1 3 0

6BDC4074 6BDC408C 156 TM 53086 0 3 0

6BE18084 6BE181A0 156 TX 196644 653 6 0

注意：臨時表的append操作與普通表的差異顯示出來了。首先，在表鎖TM上，沒有使用排他鎖（LMODE=6），而是使用了共享鎖（LMODE=3）。其次，在物件上，加入了TO型別的鎖（用於臨時表物件），級別為共享鎖。

這是，如果另一個session也進行插入操作。

//session2中

SQL> select sid from v$mystat where rownum<2;

SID

----------

145

SQL> insert /*+ append */ into t_temp select owner,object_id,rownum from dba_objects where rownum<10;

9 rows inserted

SQL> select * from v$lock where sid in (156,145);

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST

-------- -------- ---------- ---- ---------- ---------- ---------- ----------

6C8342DC 6C8342F0 156 TO 53086 1 3 0

6C83444C 6C834460 145 TO 53086 1 3 0

6BDC4074 6BDC408C 156 TM 53086 0 3 0

6BDC4138 6BDC4150 145 TM 53086 0 3 0

6BE18084 6BE181A0 156 TX 196644 653 6 0

6BE23C64 6BE23D80 145 TX 131075 653 6 0

6 rows selected

結果顯而易見，session2（sid=145）沒有因為鎖而被hange住。同樣加了TM共享鎖和TO共享鎖，鎖之間沒有出現不相容的情況。

從原理上，也是比較好理解的。臨時表是session之間隔離的，不同session進行操作不相影響。

Append提示是涉及到並行操作的，採用它進行批次資料載入會大大提高載入效率，特別是一些插入多而刪除極少的資料表。使用它要注意併發和並行特點。

Append與Direct-Path Insert（二）

相關文章