Re: Strange thing
Date: Tue, 12 Jun 2012 21:55:35 +0100
Message-ID: <t76dnc2BX6cZMUrSnZ2dnUVZ7v6dnZ2d_at_bt.com>
"Mladen Gogala" <gogala.REMOVETHISmladen_at_google.com> wrote in message
news:jr813u$6oo$1_at_solani.org...
|I have recently re-read the following Tom Kyte's article and something has
| struck me as very odd:
|
| http://www.oracle.com/technetwork/issue-archive/2010/10-nov/
| o60asktom-176254.html
|
|
| if you update the parent table or delete from it, the child table will
| still be locked, but just for the duration of the UPDATE or DELETE. The
| lock is released after the statement is processed-not when you commit.
| This is better than in 1993, but the lock still exists.
|
|
| I didn't notice this the first time around, but now it struck me: how can
| Oracle locks be released before the commit is issued? All manuals say
that
| Oracle locks have the same lifespan as the transaction that issued them.
| If I assume that Tom Kyte's description is factually correct and that it
| wasn't a case of WUI (Writing Under Influence), then there are two
| possible ways of doing it:
|
I think you're focusing too closely on transaction locks. The locks in Tom's discussion are table locks (TM). ALL lock types (of enqueue types if you want to fuss about naming) have 6 lock modes in principle and there are calls to convert locks up or down as necessary. TX locks tend to come in modes 4 and 6; TM locks commonly appear in mode 3, used to appear in mode 2 for various reasons, and convert to mode 4 or 5 during the "FK with missing index" problem. (Actually I've got a reference to lock modes here: http://jonathanlewis.wordpress.com/2010/06/21/locks/ )
| 1) Making process have 2 contexts, just like on the OS level. A kernel
| context would have its own "commit" which doesn't affect the user
| context. That would be a bit hard to do, because the whole philosophy
| of Oracle RDBMS is that "commit" is a global event which initiates many
| things (LGWR write, flushing log buffer, releasing locks). An inteirnal
| "lightweight commit" could really be considered an anomaly, not unlike
| "the dark matter" in astronomy. It would also be the first step toward
| fully multi-threaded oracle session.
| 2) The internal quick and dirty "unlock" mechanism which doesn't
| necessarily have anything to do with transactions. If that is the
case,
| where else is this mechanism used? Can it be used by the users? Is
| there any way that user could initiate "unlock" of a resource? This
| "unlock" call is obviously being called as a part of the commit
| processing.
|
If you want to play around with controlling user resources with Oracle's locking mechanism you need to look at package dbms_lock which, from memory has key procedures called: request, release and convert (plus a couple of supporting bits).
Regards
Jonathan Lewis
Received on Tue Jun 12 2012 - 15:55:35 CDT