Re: 11.2 change in direct insert behavior

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 25 Feb 2012 23:17:34 +0000 (UTC)
Message-ID: <pan.2012.02.25.23.17.33_at_gmail.com>



On Sat, 25 Feb 2012 22:48:10 +0000, Jonathan Lewis wrote:

> Are you sure this is a change - as far as I can recall there has always
> been a TM mode 6 on "insert /*+ append */"

Jonathan, sorry for being unclear. The change is with the "values" form, as in the mentioned MOS document:

INSERT .. VALUES and APPEND HINT
Version: 10.2.0.4

--SID=159:
SQL> create table test_insert (id number, name varchar2(25)); SQL> insert /*+ APPEND */ into test_insert values (1,'Test1');

1 row created.

select sid,type,id1,id2,lmode from v$lock where sid=159;

SQL>
       SID TY ID1 ID2 LMODE ---------- -- ---------- ---------- ----------

       159 TM      61245          0          3
       159 TX     262153       3571          6

APPEND hint ignored and lock mode=3 Row Exclusive

Version: 11.1.0.7

--SID=131:
SQL> create table test_insert (id number, name varchar2(25)); SQL> insert /*+ APPEND */ into test_insert values (1,'Test1');

1 row created.

select sid,type,id1,id2,lmode from v$lock where sid=131;

SQL>
        SID TY ID1 ID2 LMODE ---------- -- ---------- ---------- ----------

       131 TM      84239          0          6
       131 TX      65548       4981          6

APPEND hint honored and lock mode=6 Exclusive

Essentially, the problem is that /*+ APPEND */ hint in the form that was ignored and didn't bother anyone, while providing a placebo to the developer, can now suddenly create an exclusive table lock.

-- 
http://mgogala.byethost5.com
Received on Sat Feb 25 2012 - 17:17:34 CST

Original text of this message