[Oracle] Append hint(insert sql) will lead a TM LOCK

添加了append提示，会对test1表加上6级TM锁，此时该表上的其他DML事物会被全部阻塞
sessioin 1:
SQL> insert /*+append*/ into test1 select * from test;

session 2:
SQL> insert /*+append*/ into test1 select * from test;


SQL> select inst_id,sid,serial#,machine from gv$session where username='SCOTT';

   INST_ID        SID    SERIAL# MACHINE
---------- ---------- ---------- ----------------------------------------
         2        571       4329 xxxxxxxx
         1       3969       5217 xxxxxxxx
         
SQL> select * from gv$lock where sid in(571,3969);

   INST_ID ADDR             KADDR                   SID TYPE          ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- ---------------- ---------------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
         1 C000000222009170 C0000002220091C8       3969 AE            100          0          4          0       8880          2
         1 C000000222009348 C0000002220093A0       3969 FB              5   29515401          0          6          0          0
         1 C000000212E486F0 C000000212E48768       3969 TX         458780       6646          6          0        243          2
         1 9FFFFFFFFD3FB340 9FFFFFFFFD3FB3A0       3969 TM          88530          0          6          0        243          2
         2 C000000222009CE8 C000000222009D40        571 AE            100          0          4          0        306          2
         2 9FFFFFFFFD3EB340 9FFFFFFFFD3EB3A0        571 TM          88530          0          0          6        240          0

6 rows selected.
         
After session 1 commit;

SQL> /

   INST_ID ADDR             KADDR                   SID TYPE          ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- ---------------- ---------------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
         2 C000000222009B28 C000000222009B80        571 XR              4          0          2          0          4          2
         2 C000000222009CE8 C000000222009D40        571 AE            100          0          4          0        595          2
         2 9FFFFFFFFD3FB340 9FFFFFFFFD3FB3A0        571 TM          88530          0          6          0          4          2
         2 C0000002129D3B40 C0000002129D3BB8        571 TX         786463       1923          6          0          4          2
         1 C000000222009170 C0000002220091C8       3969 AE            100          0          4          0       9169          2   

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

转载于:http://blog.itpub.net/24237320/viewspace-2135182/