11.2 change in direct insert behavior
Date: Sat, 25 Feb 2012 22:08:11 +0000 (UTC)
Message-ID: <pan.2012.02.25.22.08.11_at_gmail.com>
This is about a surprising conclusion of a debate from Oracle Support Community forums. Somebody has complained about "being locked on the table". I checked the attached diagnostic output and found a lock with lock type TM and locking mode 6 and looked no further as it is an exclusive table lock. I dismissed the case as a work of an application designer who should be, in my own words, "hanged, drawn and quartered as an example to the others". Boy, was I wrong! It turned out that there was a slight behavior change in the Oracle 11:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/ tables004.htm#i1009887
Let me quote:
Locking Considerations with Direct-Path INSERT
During direct-path INSERT, the database obtains exclusive locks on the table (or on all partitions of a partitioned table). As a result, users cannot perform any concurrent insert, update, or delete operations on the table, and concurrent index creation and build operations are not permitted. Concurrent queries, however, are supported, but the query will return only the information before the insert operation.
In other words, using "insert /*+ APPEND */" will acquire an exclusive table lock on the underlying table. That might have some minor effects on the concurrency of the applications using the table. That behavior is also documented in the MOS document ID 1317447.1. This may be of some significance to the people still planning to upgrade to the 11G.
This was discovered by Ravikiran K. who works for the Oracle Support. This guy is good. Mark Powell from this forum also took part in the debate.
-- http://mgogala.byethost5.comReceived on Sat Feb 25 2012 - 16:08:11 CST