Re: 11.2 change in direct insert behavior
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.comReceived on Sat Feb 25 2012 - 17:17:34 CST