添加了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
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/