Append与Direct-Path Insert（二）

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提示是涉及到并行操作的，采用它进行批量数据加载会大大提高加载效率，特别是一些插入多而删除极少的数据表。使用它要注意并发和并行特点。